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

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: http://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 (20)

  1. I’m starting to post articles around SMO and other topics, like WMI, SQLCMD, and other areas. This index…

  2. Doxycycline says:

    Re: <a href=http://medjetnet.info/doxycycline/buy-doxycycline.html>Doxycycline</a>”>http://medjetnet.info/doxycycline/buy-doxycycline.html>Doxycycline</a> is used to treat bacterial infections, including pneumonia and other respiratory tract infections; Lyme disease; acne; infections of skin, genital, and urinary systems; and anthrax (after inhalational exposure). It is also used to prevent malaria. Doxycycline is in a class of medications called tetracycline antibiotics. It works by preventing the growth and spread of bacteria. Antibiotics will not work for colds, flu, or other viral infections.

    [URL=http://medjetnet.info/doxycycline/buy-doxycycline.html]buy doxycycline[/URL]|

  3. Britneykpxhk says:

    Very nice! I have some LJ with news, check this out:

    <a href= http://michelas.livejournal.com >My live journal</a>

    <a href= http://homerius.livejournal.com >Lastest news</a>

    <a href= http://johnyknoxw.livejournal.com >My live journal</a>

  4. Britneylvkpq says:

    Very nice! I have some LJ with news, check this out:

    <a href= http://iwantubadlyz.livejournal.com >Newest news</a>

    <a href= http://annakubat.livejournal.com >Check this out</a>

    <a href= http://jackie_simpson.livejournal.com >livejournal</a>

  5. Wried says:

    Very nice! I have some sites with news, check this out:

    <a href= http://nuhost.info >Politics news</a>

    <a href= http://susearch.info >Lastest news</a>

    <a href= yanasearch.info >Lifestyle news</a>

  6. Peni says:

    Your site is best look my site – <a href="http://freecal.brownbearsw.com/Magnarx">Magna rx</a>

  7. Wried says:

    Very nice! I have some sites with news, check this out:

    <a href= http://kreolikko.livejournal.com >Politics news</a>

    <a href= http://icefroggg.livejournal.com >Lastest news</a>

    <a href= http://icyiceman.livejournal.com >Lifestyle news</a>

  8. The Best Catalog.

    <a href=http://healthpiece.info/>Real”>http://healthpiece.info/>Real Catalog</a>[url=http://healthpiece.info/]The Real Catalog[/url]

  9. Hi Sam! Photos i send on e-mail.

    Green,Hi Sam! Photos i send on e-mail.


  10. ZZzz says:

    <a href= http://lujaho.angelfire.com >time attendance management system</a> <a href= http://felija.angelfire.com >painting tile floors</a> <a href= http://myxune.angelfire.com >diani beach hotels mombasa</a> <a href= http://leloqu.angelfire.com >imaging systems</a> <a href= http://hatodo.angelfire.com >bessey bar clamp</a>

  11. Arnie says:

    Great site! You can find related info on the following sites:

    Preved, los

  12. Jon Sayce says:

    By default, when SMO queries the database for the properties of an object, it only loads the basic properties.

  13. I like this <a href="http://hondaonline4.info/924.html ">honda rebel specs</a> <a href="http://hondaonline4.info/705.html ">honda passports for sale</a> <a href="http://hondaonline4.info/730.html ">honda pilot gas mileage</a> <a href="http://hondaonline4.info/639.html ">honda of slidell</a> [URL=http://hondaonline4.info/594.html]honda odyssey troubleshooting[/URL] [URL=http://hondaonline4.info/489.html]honda nr750[/URL] new