Allen White

Improving SMO Performance - Two Hours down to Six Minutes

I've finalized the programs I built to automatically rebuild my database maintenance jobs. One of the programs rebuilds the database integrity checks and optimizations jobs, and the other rebuilds the backup jobs. The process of rebuilding the optimizations jobs involves issuing this command for every index in every database:

ALTER INDEX [PKUser_Table] ON [dbo].[User_Table] REORGANIZE WITH ( LOB_COMPACTION = ON )

Of course, this means iterating through every database, and every table, and every index. The code was fairly simple to do this:

        Dim objDBColl As DatabaseCollection
        Dim dbDatabase As Database
        Dim objTableColl As TableCollection
        Dim objTable As Table
        Dim objIndexColl As IndexCollection
        Dim objIndex As Index
        Dim objViewColl As ViewCollection
        Dim objView As View

        objDBColl = srv.Databases
        For Each dbDatabase In objDBColl
            objTableColl = dbDatabase.Tables
            For Each objTable In objTableColl
                If objTable.IsSystemObject = False Then
                    objIndexColl = objTable.Indexes
                    For Each objIndex In objIndexColl
                        Console.WriteLine("ALTER INDEX [" + objIndex.Name + "] ON [" + objTable.Schema + "].[" + objTable.Name + "] REORGANIZE WITH ( LOB_COMPACTION = ON )" + CStr(Now))
                    Next
                End If
            Next
            objViewColl = dbDatabase.Views
            For Each objView In objViewColl
                If objView.IsSystemObject = False Then
                    objIndexColl = objView.Indexes
                    For Each objIndex In objIndexColl
                        Console.WriteLine("ALTER INDEX [" + objView.Name + "] ON [" + objView.Schema + "].[" + objView.Name + "] REORGANIZE WITH ( LOB_COMPACTION = ON )" + CStr(Now))
                    Next
                End If
            Next
        Next

Of course, the problem is that on one of my servers I have almost 100 databases, so this iterative process took over two hours! I've seen a number of people complain about the performance of SMO but I felt that there was something I could do to change it. There was, in the form of the SetDefaultInitFields method of the server object. Prior to iterating through the databases and views I added the following code:

        srv.SetDefaultInitFields(GetType(Table), "IsSystemObject")
        srv.SetDefaultInitFields(GetType(View), "IsSystemObject")

The application went from over two hours down to just over six minutes! Let's just say that two hours was unacceptable, but six minutes is "well within expected operating parameters."

Allen