/*
In this script we create a temporal table and then review all indexes with next conditions.
If the index have avg_fragmentation_in_percent > 10 % or avg_page_space_used_in_percent > 90 %
then we have run alter index ... on ... with rebuild
*/
SELECT sysobj.name object_name,
sysobj.xtype object_type,
indexes.name index_name,
index_data.database_id,
index_data.object_id,
index_data.index_id,
index_data.avg_fragmentation_in_percent,
index_data.avg_fragment_size_in_pages,
index_data.avg_page_space_used_in_percent,
index_data.record_count
Into #fragmentados
FROM sys.dm_db_index_physical_stats (6, NULL,NULL, NULL,'SAMPLED') index_data -- Review all tables on database
inner join
sys.sysobjects sysobj
on index_data.object_id = sysobj.id
left outer join sys.sysindexes indexes
on index_data.index_id = indexes.indid
and index_data.object_id = indexes.id
WHERE (avg_fragmentation_in_percent > 10
OR avg_page_space_used_in_percent < 90)
-- avg_fragmentation_in_percent (sys.dm_db_index_physical_stats) / logical scan fragmentation (dbcc showcontig) < 10%
-- avg_fragment_size_in_page (sys.dm_db_index_physical_stats) / Extent Scan Fragmentation (dbcc showcontig)
-- avg_page_space_used_in_percent (sys.dm_db_index_physical_stats) / Avg. Page Density (dbcc showcontig) > 90 %
Sunday, July 27, 2008
Check Fragmentation on All Indexes on Database - SQL Server Central
Check Fragmentation on All Indexes on Database - SQL Server Central
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment