Query to check for index fragmentation in SQL 2005 (shoot for below 10% fragmentation):
| -- Replace "YourDatabase" and "YourTable" with your specfic object names
USE YourDatabase
SELECT CAST(DB_NAME(DATABASE_ID) AS VARCHAR(20)) AS 'DatabaseName',
CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(20)) AS 'TableName',
INDEX_ID,
CAST(INDEX_TYPE_DESC AS VARCHAR(20)) AS INDEX_TYPE_DESC,
AVG_FRAGMENTATION_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('YourDatabase'),OBJECT_ID('dbo.YourTable'),NULL,NULL,NULL );
|