November 4, 2015 at 3:41 am
Got a wonderful script to rebuild\reorg indexes based on framentation level from http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx
but even after running this for multiple times ,can see many fragmented indexes when checked using below script, wondering how??
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'PCSTESTDBV2_11042015'), null, NULL, NULL , 'detailed') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY avg_fragmentation_in_percent desc
GO
November 4, 2015 at 3:46 am
Whats the page count of the indexes still fragmented and what are the index types?
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'PCSTESTDBV2_11042015'), null, NULL, NULL , 'detailed') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 and page_count > 1000 and ips.index_id > 0
ORDER BY avg_fragmentation_in_percent desc
Generally filter out anything with less than 1000 pages as these generally wont rebuild due to the size of them being small and also filter out any heaps as these don't rebuild
November 4, 2015 at 4:16 am
Thanks for the quick response. You are right the page count is 1 for all of them
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply