Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 31, 2003

Automating SQL Server Management with WMI (part 5)

By Marcin Policht

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM