Index maintenance

Useful indexes can speed up your application's performance dramatically. I've seen performance improvements of 10- to 1000-fold with appropriate indexes. Determining what indexes will help your application the most depends on your application type, architecture and other factors. Indeed, adding the indexes for improving the performance is perhaps one of the most exciting parts of the database administrator's job. Maintaining indexes, on the other hand, isn't very exciting. Such tasks can take many hours of fairly boring and tedious work while your boss and developers are looking over your shoulder and keep asking how much longer it will take.

So why do we have to maintain indexes in the first place? When your application executes data modification language statements (INSERT, UPDATE, DELETE) SQL Server has to automatically update the index pointers. During such activity index pages tend to get fragmented. So in a nutshell the index maintenance is a task of removing fragmentation and ensuring all pages within an index are ordered appropriately.

The easiest and oldest way to maintain indexes is to drop and recreate them. If your application has hundreds or even thousands of tables with a few indexes on each it would be quite a task to go through each one of them and recreate the indexes. The larger the table the longer it will take to recreate indexes on it; in addition some system tables (SYSINDEXES, SYSINDEXCOLS) as well as the table on which the index is being created will be locked during the index creation. Therefore, you should try creating all of your indexes during the hours of limited user activity to avoid a huge dive in the application's performance.

Somewhat of a different approach is to recreate the clustered index on each table. Note that all non-clustered indexes are rebuilt automatically because they contain pointers to the clustered index. The syntax is:


The advantage of this approach is that you don't have to manually drop and recreate non-clustered indexes, which can be beneficial in some environments.

Finally the newest way to maintain indexes is by removing fragmentation. This approach is by far the most efficient since it doesn't have to re-create the index – it just shuffles the index and data pages and reorders them. The syntax of DBCC INDEXDEFRAG is supported in SQL Server 2000 only. The main advantage of de-fragmenting an index is that it doesn't lock the table – users are still able to read and write to it. The disadvantage of INDEXDEFRAG is that it doesn't remove all fragmentation and therefore doesn't work as well as a complete rebuild.

So what's the happy medium among these approaches? Well, that depends on each individual environment. In my experience it helps to identify what level of fragmentation warrants complete rebuild of an index. For a table that is accessed frequently you should try to keep fragmentation as low as possible. Once again going through each table and deciding per each index is a daunting task and might not be feasible.

I offer you a way to automate your index maintenance with a decision support stored procedure. This procedure runs through all indexes and determines the fragmentation of each. On my production server I don't like to see fragmentation greater than 15%, therefore if an index is 15% (or higher) fragmented then I run DBCC INDEXDEFRAG for that index. If the fragmentation is very heavy – 40% or more then I run a complete rebuild of such indexes. Even though INDEXDEFRAG is an offline operation I still recommend running this procedure during off hours only since collecting fragmentation information can be rather time consuming.

-- declare variables:  
DECLARE @table_name  VARCHAR(255),   
                   @index_name  VARCHAR(255),  
                   @sql   VARCHAR(4000)
-- temporary table to hold  
-- intermediate values:   
CREATE TABLE #table_index (  
 table_index_id  INT IDENTITY(1, 1)  NOT NULL,   
 table_name  VARCHAR(255)   NULL,   
 index_name  VARCHAR(255)   NULL,   
 sql_statement  VARCHAR(4000)   NULL,  
/* get all records from sysindexes for all user tables   
** except for text columns and columns for the table itself:  
** also exclude dtproperties table:  
INSERT #table_index (  
SELECT  c.name + '.' +   
  a.name AS table_name,   
 b.name AS index_name  
FROM sysobjects a   
INNER JOIN sysindexes b   
ON  a.id = b.id  
AND b.indid <> 0 -- table  itself
AND   b.indid <> 255 -- text column  
AND  a.name <> 'dtproperties'  
AND a.type = 'u'
INNER JOIN sysusers c   
ON  c.uid = a.uid  
IF @@ERROR <> 0  
  RAISERROR('error occured while populating a temp table', 16, 1)  
-- temp table to hold results of   
CREATE TABLE #showcontig_results (  
 ObjectName VARCHAR(255) ,   
 ObjectID  BIGINT ,   
 IndexName VARCHAR(255) ,   
 IndexID   TINYINT ,   
 Pages   BIGINT  ,   
 [Rows]   BIGINT  ,   
 MinimumRecordSize INT,   
 MaximumRecordSize INT,   
 AverageRecordSize INT,   
 ForwardedRecords  INT,   
 Extents    INT,   
 ExtentSwitches   INT,   
 AverageFreeBytes  NUMERIC,   
 AveragePageDensity NUMERIC,   
 ScanDensity INT,   
 BestCount INT,   
 ActualCount INT,   
 LogicalFragmentation NUMERIC,   
 ExtentFragmentation NUMERIC)  
/* Run DBCC SHOWCONTIG for all user indexes  
DECLARE table_index_cursor CURSOR FOR   
SELECT table_name, index_name FROM #table_index   
OPEN table_index_cursor  
FETCH NEXT FROM table_index_cursor INTO @table_name, @index_name  
               SELECT @sql = 'DBCC SHOWCONTIG(''' + @table_name + ''', ''' +           
          @index_name + ''') WITH TABLERESULTS'   
             INSERT #showcontig_results  
            IF @@ERROR <> 0  
                           EXEC master..xp_logevent 'error occured while determining   
                                            running DBCC SHOWCONTIG', ERROR  
 FETCH NEXT FROM table_index_cursor INTO @table_name, @index_name  
CLOSE table_index_cursor  
DEALLOCATE table_index_cursor  

UPDATE  #table_index   
SET  sql_statement =   
 'DBCC INDEXDEFRAG(' + db_name() + ', ''' + table_name + ''',''' + index_name + ''')'   
FROM  #table_index a INNER JOIN #showcontig_results b  
ON a.index_name = b.IndexName  
WHERE  (ScanDensity BETWEEN 61 AND 85)
OR LogicalFragmentation BETWEEN 16 AND 40
UPDATE  #table_index   
SET sql_statement =   
 'DBCC DBREINDEX(''' + db_name() + '.' + table_name + ''',''' + index_name + ''')'   
FROM  #table_index a INNER JOIN #showcontig_results b  
ON  a.index_name = b.IndexName  
WHERE ScanDensity < 61  
OR LogicalFragmentation > 40  

IF @@ERROR <> 0  
  EXEC master..xp_logevent 'error occured while determining appropriate index needs', ERROR  

/* Next execute the sql statements created by the   
** previous portion of the procedure  
DECLARE sql_statement CURSOR FOR   
SELECT sql_statement FROM #table_index   
WHERE sql_statement IS NOT NULL  
OPEN sql_statement  
FETCH NEXT FROM sql_statement INTO @sql  
   IF @@ERROR <> 0  
     EXEC master..xp_logevent 'error occured while executing  
  FETCH NEXT FROM sql_statement INTO @sql  
CLOSE sql_statement  
DEALLOCATE sql_statement  

-- cleanup:  
DROP TABLE #table_index  
DROP TABLE #showcontig_results  

This was first published in August 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.