Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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

Print | posted on Monday, February 26, 2007 5:05 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# 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.
2/27/2007 2:03 PM | Stephen Moore
Gravatar

# 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. ;)
2/27/2007 2:50 PM | Gordon Rayburn
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET