Overview

The primary focus of recent SMO development has been to improve performance of scripting operations at scale, with thousands of tables/columns/indexes. For most applications, poor performance stems from improperly fetching objects in collections by creating as many as 2 queries per object in the collection. If you are writing a SMO based application, there are several coding patterns that can help you achieve the best performance.

Patterns for best performance

Use ScriptScriptPublish or Transfer or Scripter to script multiple objects

These scripting container implementations are able to fetch all the objects and their dependencies with the fewest number of queries possible in order to generate scripts.

Use Server.SetDefaultInitFields or SmoCollectionBase.ClearAndInitialize to populate collections

See the SMO samples at https://github.com/microsoft/sql-server-samples/blob/master/samples/features/sql-management-objects/src/CollectionSamples.cs for examples.

Knowing in advance which properties of an object your code consumes enables you to populate a collection of those objects with a single query instead of triggered a query for each object in the collection as your loops through it.

Instrument your application to track connections and query volume

You can track metrics about your application's use of TSQL in several ways. We wrote a proxy that lets our SMO test applications monitor the number of connections and the volume of data fetched from the server. It allows tests to inject artificial latency at the network level so any increase in the number of round trips due to a code change can be quickly identified.

You can also turn on SqlClient traces using xperf to see exactly what queries are running without needing any trace or xe session on the server. See https://learn.microsoft.com/sql/connect/ado-net/enable-eventsource-tracing?view=sql-server-ver16#use-xperf-to-collect-trace-log

Sql Server conditions that affect SMO scripting performance

Full text index fragmentation

See https://support.microsoft.com/en-us/help/4045273/full-text-indexes-population-completes-slowly-in-sql-server The queries that SMO uses to enumerate tables and indexes can be slowed down immensely by non-merged full text indexes. It's important to follow the guidance in that KB for structuring your full text indexes to avoid slowing down SMO (and by extension SMO-based apps like SSMS and Azure Data Studio).

Changing SMO query isolation levels

Query isolation level can be set on a per-application, per-user basis via a registry key. For example, to enable SMO queries in SSMS to use read uncommitted isolation, set the following registry value:

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft SQL Server\SMO\QueryIsolation\SSMS]
"Prefix"="read uncommitted"
"Postfix"="read committed"

Breaking this down a bit - the SSMS key name refers to the application name. If you have your own SMO-based application where you want the same benefit, replicate these values in a key named after your app. The Prefix value is the isolation level that will be used to make the query. The Postfix value is the isolation level to which the connection will be reset after running the query. If your server default is not read committed you'll probably want to change this to match your server.

We've found that some operations like expanding Object Explorer nodes, Intellisense completions, and scripting objects will be more likely to complete in the presence of long running transactions when using read uncommitted isolation. I'd be interested in hearing from users who have tried this feature out to learn if it has enough benefit to warrant true productizing for mainstream usage.