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.
