March 10, 2011 at 11:28 am
What is the most efficient and accurate method for measuring index fragmentation? Below is the query that I have been using, but my server performance is so poor this week that it's taking hours to run.
Thanks
-- View index fragmentation ordered by fragmentation level
SELECT stats.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'Live'), NULL, NULL, NULL, NULL) as stats
INNER JOIN sys.indexes AS b
ON stats.object_id = b.object_id AND stats.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC
March 10, 2011 at 11:51 am
Yup, that's the query to use, and you've already got it in limited mode (the most efficient)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2011 at 12:09 pm
Thanks for the quick response!
March 10, 2011 at 12:13 pm
This query runs in 10 seconds on my SQL 2008 server, but is returning 0 results on my 2005 box. The 2005 box is at compat. level 80.
Should I move it up?
March 10, 2011 at 12:26 pm
It shouldn't return 0 even in compat mode 80. Are you running it from the right DB? Index physical stats is not specific to a database (can be run from anyDB with the same result), sys.indexes is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply