Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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?

36 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Chrissy LeMaire shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

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.

4 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Michal Poreba commented  ·   ·  Flag as inappropriate

    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.

  • Ben Miller commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

Feedback and Knowledge Base