April 3, 2010 at 4:00 pm
Hi,
I'm tring to find Index Fragmentation using the below query:
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (8, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>10 AND INDEX_ID>0 AND page_count>100
order by page_count desc
I'm able to find the Index fragmentation but I unable to find what's that Index Name & which table it belongs to.
Using the below, I did find the table name but NOT Index Name.
select object_name(53575229)
select object_name(277576027)
Please provide me some script which will provide this info in singe step..
thanx
April 3, 2010 at 4:37 pm
Try:
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, 'limited') 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 > 100
order by page_count desc
MJ
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply