Fixing Slow SQL Server Management Objects (SMO) Performance

Just recently I’ve been converting a small utility that uses SQL-DMO to use SQL-SMO instead (SQL-DMO is deprecated in SQL Server 2008 R2; if you really need to use it, install the Backward Compatibility Components from the SQL Server 2008 Feature Pack from the Microsoft Download Centre). Remembering all those posts saying how slow SMO was compared to DMO, a colleague mentioned this:

SMO has the concept of Delayed Instantiation of Object Properties. The concept consists of not returning every property of a database object when the object is requested. Some properties, like the StoredProcedure’s IsSystemObject property, are not returned by default when you request a Stored Procedure or a collection of Stored Procedures from the Database. It is only when you explicitly access the IsSystemObject property that SMO goes back to the database to get its value.

[…]

If you find your SMO code is running slowly, use SQL Profiler to check whether you are accessing properties that are not returned by default and creating extra roundtrips to the database. Force SMO to load those properties by default by using the Server.SetDefaultInitFields method:

            Server server = new Server();

// Load all properties for columns
 server.SetDefaultInitFields(typeof (Column), true);

// ...or load all properties for all objects
 server.SetDefaultInitFields(true);

To build an SMO application, you need to reference the SMO assemblies. Click ‘Add Reference’ and navigate to the folder

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Add references to:

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.SqlEnum.dll

(These are the minimum files that are required to build an SMO application)

Ref.: How to: Create a Visual C# SMO Project in Visual Studio .NET