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
