Share via

SQL Server: Tuning your SMO Application for great performance - PART 1 (featured at TechED)

  • Article

I'm ramping up for TechED 2005 (Both USA and Europe) and will be giving a couple of talks on SMO. This is a repost of an article from SqlJunkies, as I am still moving these blog entries to MSDN. Ping me if you are visiting TechED and are interested in certain SMO topics.

Unlike SQL-DMO, SQL Server Management Objects (SMO) gives you a fairly fine level of control how data is retrieved from the server.

When using default behavior, an SMO object transitions into the following states:

1. Partially instantiated -- some properties are available (such as Name, Schema)
2. Fully instantiated -- all low cost properties are retrieved in bulk when you retrieve any of these
3. Expensive properties -- are fetched when needed, one at the time (such as database space)

The rationale behind this is to allow the object model to scale when a high number of objects need to be retrieved, for example when populating a collection of 10,000 tables. In contrast, SQL-DMO always fetches all properties when populating a collection. This can be compared with doing a 'select * from xxx', when you only want to know something about name and the creation date of the object. SMO fetches only the minimal set of properties that are required to populate the collection (i.e. for Table, Name and Schema are needed to uniquely identify the object).

Now here comes the catch: if your application does fetch extra properties, besides the minimally required ones, it will submit a query to retrieve these extra properties. This can cause your application to become extremely 'chatty', and will likely cause it to perform not as good (or just bluntly bad). This is especially noticed when doing 'foreach' enumerations, such as shown in the example below:

Server svr = new Server();

Database db = svr.Databases["AdventureWorks"];
foreach (Table t in db.Tables)
{
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);
}

You can inspect the result by switching SQL Profiler on and looking at the number of batches that are emitted to SQL Server.

When running above sample you will find about 155 rows in the SQL Profiler. The application is certainly chattier than it should be. This app's performance is especially down the drain on networks with a higher latency than a typical LAN, but even on a LAN it does not perform very well.

Enter Server.SetDefaultInitFields() . This call allows you to set the fields that are retrieved when the object gets initialized. This would add one extra line to the above sample:

Server svr = new Server();

Database db = svr.Databases["AdventureWorks"];
svr.SetDefaultInitFields(typeof(Table), "CreateDate");
foreach (Table t in db.Tables)
{
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);
}

Note that you do not have to indicate any of fields that are retrieved by default (Name, Schema).

After running the above sample, you will find only 11 rows in SQL Profiler, and you will also notice that the first sample you could see it fill the screen line by line (I'm running this on a 1.8Ghz P4 Toshiba notebook with 1Gb of memory) and the optimized sample flashes by and disappears in a split second (could have added some timing to the sample, but for clarity I keeping it as short as possible.

The bottom line is that you need to understand your application behavior and tuning will make a big difference. With more options to tune SMO, the responsibility to tune your application has shifted to you, as SMO cannot guess what your application is going to request. "With great power comes great responsibility" :-)

Next post will be on some of the more advanced tuning options SMO has to offer. If you have a special request about what you like me post on in the SMO space (or SQL-DMO, SQLCMD, OSQL, SQL WMI Provider, SQL Computer Manager, or XP's for that matter), then let me know.

Michiel Wories (a PM on the SQL Server Team)
---
See also: https://staff.develop.com/bobb/weblog/PermaLink.aspx?guid=0c070aff-a30f-4669-b0ae-ef7a2cde81c6
---
This posting is provided "AS IS" with no warranties, and confers no rights.

Comments