December 15, 2008 at 2:20 pm
When I execute "dm_db_index_physical_stats", what should I be looking for in the results? What field(s) tell me if my data/index is fragmented and in need of a reorg/rebuild?
thanks in advance
December 15, 2008 at 2:35 pm
Below is a query that can help you understand this table:
SELECT s.name AS schema_name, o.name AS table_name, i.name AS index_name,
ps.partition_number, ps.avg_fragmentation_in_percent, ps.avg_page_space_used_in_percent, ps.fragment_count, ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') ps
INNER JOIN sys.objects o ON ps.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.index_id <> 0
AND ps.page_count > 1
avg_fragmentation_in_percent will tell you how fragmented the index pages are. I've seen people say from 10 to 30 percent REORGANIZE, greater than 30 percent REBUILD.
avg_page_space_used_in_percent will tell you how efficient the pages in the index are being used, and can be compared to the fill factor of the index. Values for this will probably vary for different environmnets, with OLTP systems averaging lower page space percent used lower than reporting / DW systems.
December 15, 2008 at 2:40 pm
Hi,
avg_fragmentation_in_percent is the field you need to look for. If it is bet 0-10 you can ignore, if bet 11-30 REORGANIZE if greater then 30% REBUILD index.
check this link : http://msdn.microsoft.com/en-us/library/ms189858.aspx
Shree
December 15, 2008 at 3:02 pm
Thanks Chris and Shree! This is exactly the type of info I was looking for.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply