SMO Enumerations slow with hundreds of databases
SMO Enumeration is notoriously slow, especially if a SQL Server contains hundreds or thousands of databases. This is impacting our project and we have to get around it by creating new objects that are populated by T-SQL.
I know this is a known issue. Can it be revisited?
Hi, thanx for the feedback.
As Ben points out, if you know which fields on the Database object you need, you can use Server.SetDefaultInitFields to make sure the collection is initialized with all those fields in the first query.
Is there a particular scenario, using the latest SMO NuGet, where use of SetDefaultInitFields isn’t sufficient to speed up the query noticeably?
If you have an Intellitrace or XEvents trace I could use for reference it’d be a great help.
Michal Poreba commented
This also involves individual requests to obtain permissions for each database. I tried queried server with hundreds of databases over network. it can take hours. I think it could be done in a single request cutting down time by removing all that network latency.
we've made changes to the "bad query" in recent versions of SMO to improve the worst case time. Previous versions where joining on DMVs like db_hasaccess and such that really slowed things down. Do you see any improvement in recent SMO NuGet package versions?
Ben Miller commented
all you have to do is to have 300 databases and get a Server object whether by new-object or by importing SQLServer module. Then using this:
$server.Databases.ID and sit and wait and wait.
But these IDs are in sys.databases. If you look at the query that is issued behind the scenes includes mirroring and many other things that really do not relate and may never be interrogated.
I will let Chrissy illustrate her take on the enumeration she is talking about.
Ben Miller commented
Yes, this is due to the silly query that comes from a Database object where the only default init field is Name. Whenever any other property is accessed (without employing Server.SetDefaultInitFields) it produces a very bad query that takes a while to execute and with 100's of databases or more, it is deadly.