In this article, I will continue with the topic of performing common SQL server maintenance tasks using Windows Management Instrumentation. In the part 4 of the series, I described methods that allow rebuilding indexes and updating statistics on the table level. These operations can take a considerable amount of processing power and time, in the case of large tables. In such cases, it might be beneficial to limit the scope of these tasks to the level of individual indexes.

Fortunately, WMI includes the MSSQL_Index class, which includes two methods, Rebuild and UpdateStatistics. These methods can be applied to a specific index. Before using these methods, you will need to determine the list of indexes on a target table. This list can be created by enumerating all instances of the MSSQL_Index class for that table. One way to accomplish this is by using the ExecQuery method, which takes as its parameter a WQL statement. WQL (an acronym for WMI Query Language) is a subset of Structured Query Language. WQL typically takes a format of the SELECT statement. In case of WQL, the SELECT statement can use only equality comparison (unlike T-SQL). This comparison checks the values of the properties of the instances of the MSSQL_Index class, which can be any of of the following:

• DatabaseName - name of the database where the index resides
• FillFactor - integer value representing the percentage of the page used to store the index data at the time when the index was created
• IsFullTextKey - boolean value (i.e. True or False) identifying whether the index is used for Full Text indexing
• Name - name of the index
• NoRecompute - boolean value specifying whether data-distribution statistics are automatically recalculated (when set to False) or not (when set to True)
• SpaceUsed - integer value representing the amount of disk space, (in kiloBytes), used to store index data
• SQLServerName - name of the SQL Server instance where the database containing the index is stored
• StatisticsIndex - boolean value indicating whether the index maintains the data distribution statistics
• TableName - name of the table on which the index has been created
• Type - bit mask, with individual bits specifying index characteristics. When none of the bits are set (all of them are 0s), the index is non-clustered. Among more commonly used bit positions are:
• 0 - designates index ignoring duplicate key
• 1 - means that the index implements unique constraint
• 4 - represents a clustered index
• 11 - indicates that the index implements primary key constraint

Using these properties, you can specify exactly which indexes you want to list. The following example lists all indexes on the SQL Server SWYNKSRV01, in Database Northwind, on table Customers. The WQL query simply looks for a match on the values of three properties: SQLServerName, DatabaseName, and TableName.

sComputer  = "SWYNKSRV01"    '-- computer where SQL Server is installed

sSQLServer = "SWYNKSRV01"    '-- SQL server instance containing database with the target table

sDb        = "Northwind"            '-- database containing the target table

sTable     = "[dbo].[Customers]"    '-- name of the target table



Set cInstances = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _

	sComputer & "/root/MicrosoftSQLServer").ExecQuery("SELECT * " & _

	"FROM MSSQL_Index WHERE DatabaseName=" & Chr(34) & sDb & _

	Chr(34) & " AND SQLServerName=" & Chr(34) & sSQLServer & _

	Chr(34) & " AND TableName=" & Chr(34) & sTable & Chr(34))



For Each oInstance In cInstances

	WScript.Echo oInstance.Name

	WScript.Echo oInstance.NoRecompute

Next

The above would provide you with the listing of all the indexes of the table, along with their NoRecompute value. If this value is False, it means that data distribution statistics on the index are automatically updated. Now, let's assume that we have a database with a large table and an index that has the NoRecompute value set to True. We can force the update of the distribution statistics on this index using the UpdateStatistics method. To do this, we need to first create a reference to this index and than invoke the method for it. The following code illustrates how to accomplish this:

sComputer  = "SWYNKSRV01"    '-- computer where SQL Server is installed

sSQLServer = "SWYNKSRV01"    '-- SQL server instance containing database with the target table

sDb        = "BigDB"         '-- database containing the target table

sTable     = "BigTable"      '-- name of the target table

sIndex     = "BigIndex"      '-- name of the target index



Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _

	sComputer & "/root/MicrosoftSQLServer:MSSQL_Index.DatabaseName=" & Chr(34) & _

	sDb & Chr(34) & ",Name=" & Chr(34) & sIndex & Chr(34) & ",SQLServerName=" & _

	Chr(34) & sSQLServer & Chr(34) & ",TableName=" & Chr(34) & sTable & Chr(34))



Set oOutParam = oInstance.UpdateStatistics



If oOutParam.ReturnValue = 0 Then

  WScript.Echo "Statistics on the index " & sIndex & _

    " on the table " & sTable & " have been updated successfully"

Else

  WScript.Echo "Updating statistics on the index " & sIndex & _

    " on the table " & sTable & " failed with the error " & oOutParam.Description

End If

Similarly, to recreate an individual index, you would need to first reference it and then call the Rebuild method, as the following sample code illustrates:

sComputer  = "SWYNKSRV01"  '-- computer where SQL Server is installed

sSQLServer = "SWYNKSRV01"  '-- SQL server instance containing database with the target table

sDb        = "BigDB"       '-- database containing the target table

sTable     = "BigTable"    '-- name of the target table

sIndex     = "BigIndex"    '-- name of the target index



Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _

	sComputer & "/root/MicrosoftSQLServer:MSSQL_Index.DatabaseName=" & Chr(34) & _

	sDb & Chr(34) & ",Name=" & Chr(34) & sIndex & Chr(34) & ",SQLServerName=" & _

	Chr(34) & sSQLServer & Chr(34) & ",TableName=" & Chr(34) & sTable & Chr(34))



Set oOutParam = oInstance.Rebuild



If oOutParam.ReturnValue = 0 Then

  WScript.Echo "Index " & sIndex & " on the table " & _

    sTable & " has been rebuilt successfully"

Else

  WScript.Echo "Rebuilding index " & sIndex & " on the table " & _

    sTable & " failed with the error " & oOutParam.Description

End If

As you can see, WMI provides a lot of flexibility when dealing with different aspects of SQL Server management. This article concludes the series describing features of WMI SQL Administration Provider. You can further explore this topic by searching for relevant information on the MSDN Web Site). In my next series of articles, I'll focus on XML related features in SQL Server 2000.

» See All Articles by Columnist Marcin Policht