Automating Reindexing In SQL Server 2000

By : Tom Pullen May 04, 2005

Printer friendly

In all OLTP environments, virtually all indexes will become fragmented over time. Nearly all UPDATE, INSERT or DELETE activity will cause your indexes to become less well organized than they were when they were first created. There will be more page splits, there will be a greater number of pages with less data on them, and consequently, there will be more I/O required to satisfy each SELECT. And the more fragmented your data and indexes become, the slower your application will be, and the more space your data will take up. What can you do to address this? You can reindex them on a regular basis.

So What’s Available Out-of-the-Box?

On a basic level, you can use the Database Maintenance Wizard to perform reindexing, and create Maintenance Plans to do the job. This will work if you are prepared to accept its inherent limitations. Firstly, the reindexing, which is set up and done by the Maintenance Wizard, is indiscriminate. It will reindex everything, regardless of whether it needs it or not. Now if you have a large database with large tables and lots of indexes, this may be problematic, as it may take too long, certainly longer than your available maintenance window, to indiscriminately reindex the entire database. And that’s the problem - it’s all or nothing, you can’t segment your database tables in any way at all.

So what else can you do? You can write a script to reindex selected tables. This way you can segment your database and reduce the time spent during your maintenance window on reindexing. This time is something you will want to reduce to a minimum, as reindexing exclusively locks tables, preventing users from accessing it while the indexing is occurring. So you could, for example, reindex one-fifth of your tables each night per working day of the week, so all are done at least once a week. However, this is also indiscriminate – you will be reindexing tables whether their data and indexes are fragmented or not.

This is where selective reindexing is recommended. You need to be able to check your tables’ indexes and data fragmentation, retain the data, and then act on it, to reindex in a discriminate and deterministic way. Only by taking a systematic approach such as this, can you be assured that you are only reindexing the table data and indexes that actually need it. And only in this way can you minimize the amount of time taken to reindex. Reducing reindexing time is crucial, as during a full reindex, if you don't want to negatively affect your users.

So How Do We Work Out What Needs to be Done?

We use the command

DBCC SHOWCONTIG()

One of the big advances between SQL Server 2000 and previous versions of SQL Server was in this simple, yet crucial command. DBCC SHOWCONTIG is the tool supplied with SQL Server to check how fragmented an index is. In previous versions of SQL Server (7.0 and earlier), this command would only output text. This is fine if the command is being used on a manual basis. However, for automation purposes, it creates serious problems. It means you need to cycle though each table and output to text file, then construct a cumbersome process for reading and interpreting the textual output in order to obtain the information you’re after.

SQL Server 2000 introduced a key clause to the DBCC SHOWCONTIG() command, namely WITH TABLERESULTS. This means you can run the command and capture the output straight into a table, rather than having to output to text file and include a layer of clumsy XP_CMDSHELL manipulation of text files.

This means that in SQL Server 2000, you can construct a procedure to cycle through your tables, running DBCC SHOWCONTIG on them, capturing the fragmentation information from the command on each into a table. You can then cycle through the results, conditionally taking defragmentation action on the indexes, depending on how fragmented they are. This is what the accompanying stored procedure does.