Automating SQL Server Management with WMI (part 4)

This fourth article in “SQL Server management with WMI” series provides
examples of using WMI in common maintenance tasks, such as truncating the
database transaction logs, or updating statistics and rebuilding indexes on
database tables.

Truncating the database transaction logs removes the inactive portion
(containing transactions committed to the database). This typically happens as
the result of the transaction log backup, or on every checkpoint, when the simple
recovery model is used, (Truncate log on checkpoint database option is ON); but
it can also be forced (e.g. by using T-SQL command BACKUP LOG TRUNCATE_ONLY or
by the method presented here). Truncating the transaction log helps to keep the
size of the log under control and negatively impacts the database up-to-point
recoverability. Do not truncate transaction logs when performing transaction
log backup as part of the recovery strategy. If it is decided to truncate the
transaction logs using the method presented here, immediately follow it with a
full database backup. Note also that truncating transaction logs does not
necessarily affect the size of the physical file containing the transaction log
– it simply clears transactions contained within the file, leaving space for
new ones. Truncate is the method of MSSQL_TransationLog class. To execute it,
specify the name of the SQL server, SQL server instance, and the target
database.

The next two methods belong to the MSSQL_Table class and deal with commonly
performed maintenance tasks on a table level. The first allows rebuilding all
indexes for a specific table. Rebuilding indexes is recommended after
operations involving large amounts of data (such as bulk inserts), but should
also be included as part of regular maintenance procedures. The RebuildIndexes
method of MSSQL_Table class is equivalent to the DBCC DBREINDEX statement (in
fact, it uses it directly to perform the reindexing). To run the sample script,
specify the name of the SQL server, SQL server instance, target table and the
database. You also need to provide the value of the fill factor that will be
used when rebuilding indexes. This integer value, (between 1 and 100), controls
the percentage of space occupied by index data on each index page,
(called index density), that is taken into
consideration when the index is rebuilt initially. Using a small value will
prevent frequent index page splits when adding data to the table, which
effectively speeds up INSERT operations. However, it will increase amount of
space used by the index pages at the same time. Using a large fill factor value
will have the opposite effect. Note that the RebuildIndexes method recreates
all indexes on a table. This might be computationally expensive, (in terms of
both processor utilization and time), so schedule this process according to the
business requirements. In case you want to limit the scope to just one specific
index, use the Rebuild method of MSSQL_Index class. WMI offers also RebuildIndex
methods in MSSQL_PrimaryKey and MSSQL_UniqueKey classes.

The third method presented in this article is the UpdateStatistics of the MSSQL_Table
class. Query optimizer uses the statistics on the data distribution across table indexes.
Accuracy of these statistics significantly benefits query performance. Just
as with rebuilding indexes, updating statistics should be performed after
operations involving large amounts of data, but it should also be part of regular
maintenance procedures. The WMI method is equivalent to the UPDATE STATISTICS
T-SQL statement (in fact, it executes it behind the scenes). To run the sample
script, specify the name of the SQL server, SQL server instance, target table
and database. In case you want to update statistics for a particular index
only, use the UpdateStatistics method of MSSQL_Index instead.

As you can see, functionality of WMI can be utilized to perform
standard SQL server maintenance tasks. I will continue this topic in
the next article of this series, covering methods (such as UpdateStatistics and Rebuild of MSSQL_Index class), only briefly mentioned in this article .

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles