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