Index maintenance

Scripts and tips for index maintenance in SQL Server.

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:

CREATE CLUSTERED INDEX ix_your_table_name WITH DROP EXISTING

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.

SET NOCOUNT ON   
  
-- 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 (  
 table_name,   
 index_name)  
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  
ORDER BY 1  
  
IF @@ERROR <> 0  
 BEGIN  
  RAISERROR('error occured while populating a temp table', 16, 1)  
  RETURN  
 END  
  
  
-- temp table to hold results of   
-- DBCC SHOWCONTIG:  
CREATE TABLE #showcontig_results (  
 ObjectName VARCHAR(255) ,   
 ObjectID  BIGINT ,   
 IndexName VARCHAR(255) ,   
 IndexID   TINYINT ,   
 [LEVEL]   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   
ORDER BY 1  
  
OPEN table_index_cursor  
FETCH NEXT FROM table_index_cursor INTO @table_name, @index_name  
  
WHILE @@FETCH_STATUS = 0  
     BEGIN  
               SELECT @sql = 'DBCC SHOWCONTIG(''' + @table_name + ''', ''' +           
          @index_name + ''') WITH TABLERESULTS'   
  
             INSERT #showcontig_results  
              EXEC(@sql)     
  
            IF @@ERROR <> 0  
                   BEGIN  
                           EXEC master..xp_logevent 'error occured while determining   
                                            running DBCC SHOWCONTIG', ERROR  
                         RETURN  
                     END  
   
 FETCH NEXT FROM table_index_cursor INTO @table_name, @index_name  
 END  
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  
 BEGIN  
  EXEC master..xp_logevent 'error occured while determining appropriate index needs', ERROR  
  RETURN  
 END  
  

/* 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  
WHILE @@FETCH_STATUS = 0   
 BEGIN  
  EXEC(@sql)  
   IF @@ERROR <> 0  
    BEGIN  
     EXEC master..xp_logevent 'error occured while executing  
         DBCC DBREINDEX or INDEXDEFRAG', ERROR  
     RETURN  
    END  
  FETCH NEXT FROM sql_statement INTO @sql  
 END  
CLOSE sql_statement  
DEALLOCATE sql_statement  

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

This was first published in August 2002

Dig deeper on SQL Server and .NET development

0 comments

Oldest 

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:

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close