Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with info as
- (SELECT
- ps.[object_id],
- ps.database_id,
- ps.index_id,
- ps.index_type_desc,
- ps.index_level,
- ps.fragment_count,
- ps.avg_fragmentation_in_percent,
- ps.avg_fragment_size_in_pages,
- ps.page_count,
- ps.record_count,
- ps.ghost_record_count
- FROM sys.dm_db_index_physical_stats
- (DB_ID()
- , NULL, NULL, NULL ,
- N'LIMITED') as ps
- inner join sys.indexes as i on i.[object_id]=ps.[object_id] and i.[index_id]=ps.[index_id]
- where ps.index_level = 0
- and ps.avg_fragmentation_in_percent >= 10
- and ps.index_type_desc <> 'HEAP'
- and ps.page_count>=8 --1 экстент
- and i.is_disabled=0
- )
- SELECT
- DB_NAME(i.database_id) as db,
- SCHEMA_NAME(t.[schema_id]) as shema,
- t.name as tb,
- i.index_id as idx,
- i.database_id,
- (select top(1) idx.[name] from [sys].[indexes] as idx where t.[object_id] = idx.[object_id] and idx.[index_id] = i.[index_id]) as index_name,
- i.index_type_desc,
- i.[object_id],
- i.fragment_count as frag_num,
- round(i.avg_fragmentation_in_percent,2) as frag,
- round(i.avg_fragment_size_in_pages,2) as frag_page,
- i.page_count as [page]
- FROM info as i
- inner join [sys].[all_objects] as t on i.[object_id] = t.[object_id];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement