Some days ago on a post I was talking about how important is, on an
heavy load database environments (such as a Navision DB), to check the status
of SQL Server indexes on frequently accessed tables.
The examples given was for a SQL Server 2000 database
(actually the most common environment), but to be more precise and complete I've
to signal that on SQL Server 2005 the index management is
changed (but all the considerations on my post are obviously still valid).
If you are using the new SQL Server 2005, you've to remember that the
instructions DBB SHOWCONTIG, DBCC DBREINDEX, DBCC
INDEXDEFRAG are now deprecated (actually you can use them but their
support is not guaranteed on future versions). On SQL Server 2005 you have the
new function ALTER INDEX for all index
management functions.
Here a brief summary of all these new functions (referred to my previous
example):
1) To view size and fragmentation information, you have to use the sys.dm_db_index_physical_stats catalog view. For
example:
USE Northwind;
SELECT * FROM
sys.dm_db_index_physical_stats
2) To handle index fragmentation, you have to use the ALTER INDEX instruction with the REORGANIZE keyword. For example:
ALTER INDEX PK_Order_Details ON 'Order
Details'
REORGANIZE
3) To rebuild an index on a table, you have to use the ALTER INDEX instruction with the REBUILD keyword. For example:
ALTER INDEX PK_Order_Details ON 'Order
Details'
REBUILD
You can use this instruction also for re-enable a previously disabled
index.
4) To rebuild all indexes on a table, you can use the ALL keyword. For example:
ALTER INDEX ALL ON 'Order
Details'
REBUILD
5) To disable an index, you can use the ALTER
INDEX instruction with the DISABLE
keyword. For example:
ALTER INDEX PK_Order_Details ON 'Order
Details'
DISABLE
I think that SQL Server 2005 introduces a more elegant and centralized way of
index management. 