-
-
Notifications
You must be signed in to change notification settings - Fork 785
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Get-DbaDatabase significantly slower on SQL2022 vs. SQL2016 when executing user is sysadmin #9282
Comments
hi @maxim-vde, great analysis! Isn't this a problem on sql2022's side rather than dbatools ? Did you try on a SQL2022 which isn't RTM, i.e. maybe a CU fixed the performance ? |
Thanks for the quick response, @niphlod! I have only tested on RTM builds of SQL2022. |
Upgrading to the latest CU fixed our performance issue. Many thanks to @niphlod for bringing this to my attention! |
lol, @maxim-vde , thanks a lot nonetheless, it was a reaaaally good description of the issue. Glad that a CU fixed it, thanks to Microsoft in this case |
Unfortunately it seems that after a while, the issue has returned and it's back to taking around 4 minutes... General performance of the SQL server is good, CPU utilization is hovering around 60%. |
point is, IMHO, it's still a performance problem on SQL's side that dbatools can do nothing about ?! |
@niphlod That's a tricky one imo, according to this MS article, the system table view appears to be deprecated and MS recommends using "the current SQL Server system views" instead. Oddly enough, even the latest version of SqlServer.SMO.dll seems to rely on this deprecated view rather than directly utilizing the new system views. Dbatools in its turn seems to be fetching all databases on the server, and then applying a filter, rather than only fetching the requested one. The performance impact of a single db sysprocesses lookup is negligible on its own, I could live with that. |
uhm, not convinced by this, 'cause 2016 (and every other except 2022) is "speedy". So, we MAY say "if this is 2022, don't fetch activeconnections (till MS fixes it)" but it's such a niche problem that I don't know if the pros outweight the cons. Failing that, dbatools should revert something we introduced a bit ago... there was a time where we then did This may only fix the slowness using
there's really nothing to do here. So, calling in the usual suspects ... @wsmelton , @potatoqualitee , @ClaudioESSilva , @andreasjordan ... any ideas here ? |
Just an FYI, in our troubleshooting we also tested I agree that removing activeconnections just for 2022 instances is probably a bad idea, unless it's unused in the rest of the dbatools codebase? Edit: I also want to add, we only tested SQL2016 & 2022, I don't know if this issue occurs on 2017 or 2019. |
yep, it tracks. Dunno if you tried with plain SMO browsing a server, but it's slow as hell because of it's parent/child structure, where till you browse the "child" object, some (a lot of) properties are not fetched. Think listing 30 dbs in an instance, where at EACH and EVERY db, SMO "stops and waits" for a query to retrieve specifically the "Collation" property. So, a "balanced" choice is the way: adding fields to the SMO initialization incurs in less queries: the pros are huge, the cons are:
In this case it seems that the "yet another property" doesn't add up a BIT of time: it adds a TON.
So, if we go down the route of de-init(ting) ActiveConnections just on 2022, assuming it takes 5 minutes at this moment to walk 100 databases on 2022 and 30 seconds on any other version, what should happen is:
Pros ? |
@niphlod Thank you for this very detailed explanation! It makes perfect sense! We ended up deploying a "custom build" of dbatools in which ActiveConnections aren't fetched in advance for all dbs but rather on a per-db basis. As this does seem to be a very niche thing, I'm not sure if I should keep this issue open? |
@maxim-vde : glad that the "diagnosis" was right. Just for the sake of discussion, with the "custom build" not init(ting) ActiveConnections are your times worse than before when doing |
When I tried it a few days ago, I recall it being slower when piped to a 'Where-Object' (top of my head, 16 seconds instead of 8). Just tried it again on 2 different DBs, these are the results for the And using the piped Where-Object approach: So now it's pretty close, but still a tad slower. |
hum, I don't recall if they are enumed by database_id or listed alphabetically. But, and I may digress from the diagnosis a bit, at this point it seems the big performance penalty is not on the |
In my previous results I had already tested alphabetically. Database A started with 'b....' Checked again with the first database_id vs the last and the results were basically the same for both lookups. I think it's safe to assume activeconnections is fetched after the filter. |
Perfect. Once again, @maxim-vde thanks a lot for your collaboration. Let us mumble on this a bit and we'll get back to you |
Another possible workaround is to use SetDefaultInitFields so that SMO doesn't eagerly fetch that property. Which is ironic as the typical use case that I've seen for that is the opposite. That is, to eagerly fetch some property (say, |
@ben-thul : that's actually what dbatools Connect-DbaInstance does. Point here is that is faster for everything EXCEPT 2022. |
If that's what it does, why is it still fetching the field? Is SMO ignoring the directive to not get that attribute? |
Related, I just took a look at what the default fields are for a database out of the box and ActiveConnections is still in there.
|
I have neither a SQL 2016 nor a 2022 instance handy to test, but this should work to validate the approach:
Noting both the timing and also tracing in the same way to ensure that the sysprocesses count isn't present in the query. |
lemme refrase... connect-dbainstance sets activeconnection as a default field, which improves performances for everything except 2022. setting it makes SMO (when the user has enough permissions) fetch data for all dbs with one query instead of querying database per database (which is usually slower).
this doesn't fix timing for enumeration of the whole set of databases, but it'll improve the "feel" when the target of operations is a small percentage of dbs of the instance. |
I don't understand. Why wouldn't it? Doesn't the presence of ActiveConnections in the default properties list control whether the long-running query runs or not? Honest question - if there's something I'm missing, I'm happy to learn. |
Well, let's make examples:
Then, another set of examples:
Let's say prefetching activeconnections on 2022 is the culprit and accounts for 4 minutes. This doesn't mean that "post-fetching" 100 activeconnections (for each db, let's assume 3 seconds per db) is faster than a single prefetch. So, for the "enumeration of the whole set of databases" examples, we may go from 4 minutes (240 seconds) "prefetch" to 3*100 seconds "post-fetch". But, at least, when targeting a specific database, you won't wait 4 minutes, but just 3 seconds (which is STILL bad when compared to, say, 2016, but not "brutal") |
Ah... okay. Your explanation makes sense. @maxim-vde - have you had a chance to test whether removing ActiveConnections from the default init fields gives you better performance? Doing so will (I think) inform a potential way forward. |
@ben-thul Unless I'm misinterpreting your question, yes I have. This turned out to be the solution to our performance issues as mentioned a few comments above:
|
Have we tested on latest release of SMO? |
Thanks for bringing this back up. It was a bummer that ActiveConnections are in the default init. Once the slowness has been confirmed, I can create an issue in the SMO repo to have them address it. |
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
Get-DbaDatabase consumes around 4 minutes on SQL2022, while it only takes 12 seconds on SQL2016
Steps to Reproduce
Or just:
Please confirm that you are running the most recent version of dbatools
2.1.8
Other details or mentions
I'll try to list everything we've discovered so far here:
When listing databases, Microsoft.SqlServer.Smo.dll executes following query on the server instance:
This query takes up the majority of the 4 minute total wait time. When analyzing the execution plan, we discovered most of the slowness came from following select statement within said query:
So basically, querying the active connections for each database is very slow on SQL 2022 while it wasn't on SQL2016.
This also means it gets slower for each database we add to the server, we currently have around 190 databases on the SQL2022 instance.
An example, executing
select count(*) from master.dbo.sysprocesses
on both SQL2016 and SQL2022:SQL2016:
SQL2022:
We are able to reproduce this issue on 2 separate SQL2022 instances with the exact same amount of DBs (Our Production and Test environment).
I have tried replacing the SMO DLLs with a newer version '17.100.23.0' available on Nuget, but the issue persists.
As a temporary workaround, we're running our dbatools commands from a non-sysadmin SQL account, that has all privileges except for 'View server state'. Which means this user is not allowed to query all connections on the instance and thus is much, much faster when listing databases.
Again, this slowness only becomes apparent if you have many databases running on a single SQL instance, as it queries the connections for each active DB on the server.
What PowerShell host was used when producing this error
PowerShell Core (pwsh.exe)
PowerShell Host Version
PSVersion 7.4.1
PSEdition Core
GitCommitId 7.4.1
OS Microsoft Windows 10.0.22621
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0
SQL Server Edition and Build number
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)
.NET Framework Version
.NET 8.0.1
The text was updated successfully, but these errors were encountered: