Skip to content

Comments

Get-DbaDbTable: Fix performance regression with URN filter and config option#10140

Merged
potatoqualitee merged 3 commits intodataplat:developmentfrom
MGGM-Software-GmbH:fix/get-dbadbtable-performance-regression
Feb 12, 2026
Merged

Get-DbaDbTable: Fix performance regression with URN filter and config option#10140
potatoqualitee merged 3 commits intodataplat:developmentfrom
MGGM-Software-GmbH:fix/get-dbadbtable-performance-regression

Conversation

@PeterSawatzki-MGGM
Copy link
Contributor

@PeterSawatzki-MGGM PeterSawatzki-MGGM commented Feb 5, 2026 •
edited
Loading

Summary

Fixes #10139 - Get-DbaDbTable performance regression introduced in 2.7.7.

  • URN server-side filtering: When -Schema or -Table parameters are specified, builds a proper URN filter (e.g., [@Schema='dbo' and @Name='MyTable']) and passes it to ClearAndInitialize() instead of an empty string. This ensures only matching tables are loaded from the server, restoring sub-second performance for targeted queries.
  • Configuration item commands.get-dbadbtable.clearandinitialize: Controls whether ClearAndInitialize is called at all. Defaults to $false to prevent breaking pipeline commands like Copy-DbaDbTableData that reuse SMO objects (where ClearAndInitialize clears already loaded properties from the collection).

Enabling the performance optimization

Users who call Get-DbaDbTable standalone (not piped from reused SMO connections) can opt-in to bulk property loading:

# Enable for current session
Set-DbatoolsConfig -FullName 'commands.get-dbadbtable.clearandinitialize' -Value $true

# Enable permanently (persists across sessions)
Set-DbatoolsConfig -FullName 'commands.get-dbadbtable.clearandinitialize' -Value $true
Register-DbatoolsConfig -FullName 'commands.get-dbadbtable.clearandinitialize'

When enabled, ClearAndInitialize loads all requested table properties (Schema, Name, RowCount, IndexSpaceUsed, etc.) in a single optimized SQL query, with server-side filtering via URN when -Schema or -Table is specified.

Test plan

  • Verify Get-DbaDbTable -SqlInstance $srv -Database $db -Table "specific_table" returns quickly (sub-second) regardless of config setting, thanks to URN filter
  • Verify Get-DbaDbTable -SqlInstance $srv -Database $db returns all tables correctly
  • Verify Copy-DbaDbTableData pipeline scenarios work without losing SMO properties (config default $false)
  • Verify enabling the config ($true) activates ClearAndInitialize for bulk loading optimization

(do Get-DbaDbTable)

🤖 Generated with Claude Code

@potatoqualitee
Copy link
Member

potatoqualitee commented Feb 6, 2026

Thanks so much for your PR! Tests are so busted from yesterday's outage. gonna close and reopen to try to push it forward.

@potatoqualitee potatoqualitee reopened this Feb 6, 2026
PeterSawatzki-MGGM and others added 3 commits February 12, 2026 18:57
@PeterSawatzki-MGGM
Fix Get-DbaDbTable performance regression when using -Schema or -Tabl…
8326585 
…e parameters

The ClearAndInitialize optimization introduced in 2.7.7 loads ALL tables in the
database before filtering client-side. This causes severe performance issues
(30+ seconds vs <1 second) when only specific tables are requested.

This fix adds server-side filtering by passing an XPath-style URN filter to
ClearAndInitialize when -Schema or -Table parameters are specified.

Example filter: [@Schema='dispo' and @name='t_auftraege']

Performance improvement:
- Before: ~30 seconds (loads 1000+ tables)
- After: <1 second (loads only requested table)
@PeterSawatzki-MGGM
MD ignored
3fb3e7f
@PeterSawatzki-MGGM @claude
Add configuration item to control ClearAndInitialize behavior
842d1e7 
Add 'commands.get-dbadbtable.clearandinitialize' config option (default: false)
to let users opt-in to the ClearAndInitialize optimization. This prevents
pipeline commands like Copy-DbaDbTableData from having their SMO cache cleared.

Co-Authored-By: Claude Opus 4.6 <[email protected]>
@PeterSawatzki-MGGM PeterSawatzki-MGGM force-pushed the fix/get-dbadbtable-performance-regression branch from 0549886 to 842d1e7 Compare February 12, 2026 18:15
@PeterSawatzki-MGGM PeterSawatzki-MGGM changed the title Fix Get-DbaDbTable performance regression when using -Schema or -Tabl… Get-DbaDbTable: Fix performance regression with URN filter and config option Feb 12, 2026
@PeterSawatzki-MGGM PeterSawatzki-MGGM mentioned this pull request Feb 12, 2026
Closed
@potatoqualitee potatoqualitee merged commit c5203ab into dataplat:development Feb 12, 2026
3 checks passed
@potatoqualitee
Copy link
Member

potatoqualitee commented Feb 12, 2026

Thank you so much! Will release shortly 🪄

@andreasjordan
Copy link
Collaborator

andreasjordan commented Feb 13, 2026 •
edited
Loading

Sorry, I added two comments to the code, one with text and one with mentions only.

Here is everything together at the correct place:

Maybe this was merged too fast...

In the old version we used $db.Tables.ClearAndInitialize('', [string[]]$properties) to load our default properties for all tables. Now this is gone and we might face performance problems because the properties will be fetched one by one.

And we now calculate a $urnFilter every time that we most often do not use.

I think the test for Get-DbatoolsConfigValue -FullName 'commands.get-dbadbtable.clearandinitialize' should be added to the test $fqTns -or $Schema in line 215.
And then line 281 $db.Tables.ClearAndInitialize($urnFilter, [string[]]$properties) must be moved out of the if block, so that it is executed in every case.

Should I open a follow up pull request?

@PeterSawatzki-MGGM
Copy link
Contributor Author

PeterSawatzki-MGGM commented Feb 13, 2026

yes please.

@andreasjordan andreasjordan mentioned this pull request Feb 13, 2026
Merged
@andreasjordan
Copy link
Collaborator

andreasjordan commented Feb 13, 2026

Just opened #10157 for that.

@andreasjordan andreasjordan mentioned this pull request Feb 20, 2026
Open
claude bot pushed a commit that referenced this pull request Feb 20, 2026
@github-actions @andreasjordan
Get-DbaDbTable: Fix ClearAndInitialize failing on Azure SQL
72fb376 
Azure SQL's SMO enumerator does not support the IndexSpaceUsed and
DataSpaceUsed properties. When ClearAndInitialize is called with an
explicit property list that includes these, it throws:
  "unknown property IndexSpaceUsed"

The fix removes these two properties from the list before calling
ClearAndInitialize when the server is Azure SQL. The call itself remains
unconditional (matching the pre-#10140 behavior), and the URN filter
logic from #10157 is unchanged.

Fixes #10181

(do Get-DbaDbTable, Copy-DbaDbTableData)

Co-authored-by: Andreas Jordan <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Get-DbaDbTable with -Schema and -Table parameters queries ALL tables instead of filtering server-side (severe performance regression since 2.7.7)

3 participants

@PeterSawatzki-MGGM @potatoqualitee @andreasjordan