Automating Reindexing In SQL Server 2000

Usage

This stored procedure should be created in the master database, which will enable it to be run in any user database on a server.

It is run by calling it in a user database, and passing it a parameter (MAXFRAG). This is a percentage value. What this means is to defragment any indexes whose scan density fall below this value. For example, if you want to defragment any indexes who scan density is less than 95%:

USE pubs
GO

EXEC sp_deframent_indexes 95.00

Limitations

This procedure depends upon the measure scan density, but scan density is not a valid measure of fragmentation of indexes which span multiple files. If your indexes do span multiple files, you will need to modify this SP to fragment on the basis of another measure (e.g. Logical Frag). However, this kind of modification is beyond the scope of this article; if your indexes span multiple files, you’ll need to do more work.

How does it work and what does it do?

The stored procedure has two distinct parts.

Stage 1

In this part, the stored procedure checks index fragmentation by running the command:

DBCC SHOWCONTIG (‘tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

on every table in the database. The results of this command are stored in a pre-created temporary table, #fraglist. Here, we are utilizing the benefit of the WITH TABLERESULTS clause of DBCC SHOWCONTIG, and really this feature alone saves an inordinate amount of hassle and effort that were suffered in previous versions of the product to obtain the same result.

You should note that this stored procedure works in databases which contain tables that are not dbo-owned, as well as (the more common) dbo-owned tables. I discovered that my original version would not work in all situations when a software vendor supplied us with a new system whose database contained only non-dbo-owned tables. I set up this defragmenting procedure to run on it and was rudely awoken to the shortcomings in my original when its first run on the new system, and failed completely. This is actually an issue in the defragment section (Stage 2), where the table has to be referred to by name, whereas in Stage 1, the DBCC SHOWCONTIG command references the table by object_id.

Stage 2

Here, we use another cursor to loop through the #fraglist table and conditionally run:

DBCC DBREINDEX()

on tables whose Scan Density has fallen below the threshold specified by the parameter passed to the procedure. The results of this execution are printed into the output file after the contents of the table #fraglist, so that you can review the tables’ and indexes’ fragmentation, as shown above in the screen shot, and also review the action taking by reviewing the printed list of execution of DBCC DBREINDEX(). From these you can also deduce the duration of the reindexing of each index.

What does the output mean?

Sample output: