Defragmenting indexes in SQL Server 2005
To defragment your indexes, we now use ALTER INDEX in SQL Server 2005 rather than DBCC DBREINDEX and DBCC INDEXDEFRAG, which have both been deprecated in 2005.
The REBUILD option of ALTER INDEX is equivalent to the old DBCC DBREINDEX; the REORGANIZE option of ALTER INDEX is equivalent to the old DBCC INDEXDEFRAG.
I have not yet written a stored procedure to run ALTER INDEX on the fragmented indexes, so I'll point you to two different resources.
1. In the sys.dm_db_index_physical_stats topic in SQL Server 2005 Books Online, you will find a script to defragment the indexes of a particular database. This script can be found in D of the Examples section.
2. I found this link when I googled "alter index" last week. It also can defragment the indexes of a particular database.
Both options have their strengths and weaknesses.
EDIT: My version of the code
Legacy Comments
|
Stephen Moore
2007-02-27 |
re: Defragmenting indexes in SQL Server 2005 FYI - the BOL sample script won't run against databases in SQL 2000 compatibility mode. You can fix it by declaring and setting a variable @db_id = DB_ID() first, and then referencing the variable in the call to sys.dm_db_index_physical_stats. I don't know if anyone else out there who has vendors that won't support SQL 2005 yet, but if so, this will help. |
|
Gordon Rayburn
2007-02-27 |
re: Defragmenting indexes in SQL Server 2005 I'm the author of the code you linked to over on SQLServerPerformance.com. I'm looking forward to your updates on the topic as I've not heard any feedback on my script... Nice to see I got another fifteen minutes of fame. ;) |