Defrag Those Indexes - Maintenance

This article was written back before I was looking into Sql Server 2005. The underlying idea is the same, in order to keep your database running healthy you will need to maintain and administer the underlying architecture. In this short but subtle refresh I've separated the 2000 concepts and implementations from those used today in Sql Server 2005. If you have comments or corrections do feel free to email me or leave a comment.

SQL Server 2000 =====================================================
It is imperative you maintenance your Database. One way to check up on the indexes per table is to run the DBCC SHOWCONTIG command as DBCC SHOWCONTIG ('tbl_YourTableName') with fast,ALL_INDEXES

You'll end up with a very similar display like the following...

DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 1680
- Extent Switches..............................: 217
- Scan Density [Best Count:Actual Count].......: 96.33% [210:218]
- Logical Scan Fragmentation ..................: 0.18%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 480
- Extent Switches..............................: 64
- Scan Density [Best Count:Actual Count].......: 92.31% [60:65]
- Logical Scan Fragmentation ..................: 0.83%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 5, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 696
- Extent Switches..............................: 95
- Scan Density [Best Count:Actual Count].......: 90.63% [87:96]
- Logical Scan Fragmentation ..................: 0.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What you're really looking for is the Scan fragmentation to be as low as possible. I've also read on other sites that you want the Scan Desity to be as close to each other as possible like for example 87:96 is fairly close and gives you a density over 90%.


From that you can easily run a defrag on each index as follows

DBCC INDEXDEFRAG (8, 1113627606, 5)
DBCC INDEXDEFRAG (8, 1113627606, 2)
DBCC INDEXDEFRAG (8, 1113627606, 1)

More Information can be obtained via: Sql Server Performance\dbcc commands
And sql-server-performance.com/rebuilding_indexes
and here, SQL Server Index Fragmentation and Its Resolution

If you just wish to defrangment the entire database this little script will put you on your way: (from the link above sql server performance site)
SET DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

------END SCRIPT
SQL Server 2005 ===================================================
Fastforward to today,
IndexDefrag still works in 2005, but if you read around the 'net you'll find many more references to choose to rebuild your indexes via the ALTER INDEX command, which is new for SQL Server 2005.

Before you go out and begin de-fragmenting tables like crazy, it's best to have a plan, finding out how much fragmentation is in your table in 2005 is easier too with the newer function that provides this information. below I've expanded on what you can find around the internet, the below script will allow you to identify rows over 100k and with more than 10% fragmentation, I also handled the commonly seen error for databases that are of the standard dictionary order (80), and the common "Error near '(' ".

/script/
SET NOCOUNT ON
DECLARE @db_id SMALLINT
SET @db_id = DB_ID()

SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS TableIndexName,
phystat.avg_fragmentation_in_percent, rows
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') phystat
inner JOIN sys.indexes i WITH(NOLOCK)
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id
INNER JOIN sys.partitions p WITH(NOLOCK)
ON p.OBJECT_ID = i.object_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND ROWS > 100000
/end script/

More information on this system function is availble here:
http://technet.microsoft.com/en-us/library/ms188917.aspx
This link will even give you a cool little script you can use to find out what tables in your database are fragmented over 40%, which is very useful if you are planning a weekend of maintenance.
http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx


Now that you have the needed information on how fragmented the indexes are you can begin defragmenting using the Alter Index command

ALTER INDEX ALL ON TableName REBUILD

What is especially cool about 2005 is that you can choose to throttle the amount of CPUs you use for the defragging process, with the MAXDOP option note, with the following command you can restrict the number of processors down to 2 CPUs:

ALTER INDEX ALL ON TableName REBUILD WITH(MAXDOP=2)

You can also choose to rebuild your INDEX and continue to make it available for your users as it defrags, I suspect this has performance implications but none the less the command would be:

ALTER INDEX ALL ON TableName REBUILD WITH(MAXDOP=2, ONLINE=ON)

More information on the ALTER INDEX command is available on the MSDN here:
http://msdn2.microsoft.com/en-us/library/ms188388.aspx

Happy Defragging!