PVI_COPY

Индексы_Степень фрагментации_avg_fragmentation_FillFactor

Apr 1st, 2021
251
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Индексы_Степень фрагментации_avg_fragmentation_FillFactor
  2.  
  3. -- https://habr.com/ru/post/155933/
  4.  
  5. -- https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
  6. -- sys.dm_db_index_physical_stats
  7. -- Значение avg_fragmentation_in_percent    Корректирующая инструкция
  8. --> 5 % и < = 30 %     ALTER INDEX REORGANIZE
  9. --> 30 %            ALTER INDEX REBUILD WITH (ONLINE = ON)
  10. SELECT getdate() as nowdate,
  11.     a.object_id, object_name(a.object_id) AS TableName,
  12.     a.index_id -- index_id > 0 – 0 – это куча
  13.     , name AS IndedxName
  14.     , avg_fragmentation_in_percent
  15.     --
  16.     ,a.fragment_count
  17.     ,a.page_count -- количеством страниц
  18.     ,b.fill_factor
  19.     ,a.partition_number
  20. FROM sys.dm_db_index_physical_stats
  21.     (DB_ID (N'amcrm')
  22. -- , OBJECT_ID(N'dbo.as_users')  
  23.   --, OBJECT_ID(N'dbo.am_departments')  
  24.      -- , OBJECT_ID(N'dbo.am_clients')  
  25.         --  , OBJECT_ID(N'dbo.am_clientUsers')  
  26.         --  , OBJECT_ID(N'dbo.am_clientTags')  
  27.         --  , OBJECT_ID(N'dbo.am_userTransitions')         
  28.      
  29.       -- , OBJECT_ID(N'dbo.am_alphaReportData3')  
  30.                , OBJECT_ID(N'dbo.am_events')
  31.  
  32. -- , OBJECT_ID(N'dbo.am_clientChanges')
  33. -- , OBJECT_ID(N'dbo.as_en_statusesLog')
  34. -- , OBJECT_ID(N'dbo.as_comments')
  35. -- , OBJECT_ID(N'dbo.as_en_entityInstances')
  36. -- , OBJECT_ID(N'dbo.am_salaryPlan')
  37. -- , OBJECT_ID(N'dbo.am_accounts')
  38. -- , OBJECT_ID(N'dbo.am_clientAccounts')
  39.         , NULL
  40.         , NULL
  41.         , NULL) AS a
  42. INNER JOIN sys.indexes AS b
  43.     ON a.object_id = b.object_id
  44.     AND a.index_id = b.index_id;
  45. GO
RAW Paste Data