SHARE
TWEET

Untitled

a guest Jul 24th, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with info as
  2.     (SELECT
  3.         ps.[object_id],
  4.         ps.database_id,
  5.         ps.index_id,
  6.         ps.index_type_desc,
  7.         ps.index_level,
  8.         ps.fragment_count,
  9.         ps.avg_fragmentation_in_percent,
  10.         ps.avg_fragment_size_in_pages,
  11.         ps.page_count,
  12.         ps.record_count,
  13.         ps.ghost_record_count
  14.         FROM sys.dm_db_index_physical_stats
  15.         (DB_ID()
  16.         , NULL, NULL, NULL ,
  17.         N'LIMITED') as ps
  18.         inner join sys.indexes as i on i.[object_id]=ps.[object_id] and i.[index_id]=ps.[index_id]
  19.         where ps.index_level = 0
  20.         and ps.avg_fragmentation_in_percent >= 10
  21.         and ps.index_type_desc <> 'HEAP'
  22.         and ps.page_count>=8 --1 экстент
  23.         and i.is_disabled=0
  24.         )
  25.     SELECT
  26.         DB_NAME(i.database_id) as db,
  27.         SCHEMA_NAME(t.[schema_id]) as shema,
  28.         t.name as tb,
  29.         i.index_id as idx,
  30.         i.database_id,
  31.         (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,
  32.         i.index_type_desc,
  33.         i.[object_id],
  34.         i.fragment_count as frag_num,
  35.         round(i.avg_fragmentation_in_percent,2) as frag,
  36.         round(i.avg_fragment_size_in_pages,2) as frag_page,
  37.         i.page_count as [page]
  38.     FROM info as i
  39.     inner join [sys].[all_objects]  as t    on i.[object_id] = t.[object_id];
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top