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

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

-- https://habr.com/ru/post/155933/

-- https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
-- sys.dm_db_index_physical_stats
-- Значение avg_fragmentation_in_percent    Корректирующая инструкция
--> 5 % и < = 30 %     ALTER INDEX REORGANIZE
--> 30 %            ALTER INDEX REBUILD WITH (ONLINE = ON)
SELECT getdate() as nowdate,
    a.object_id, object_name(a.object_id) AS TableName,
    a.index_id -- index_id > 0 – 0 – это куча
    , name AS IndedxName
    , avg_fragmentation_in_percent
    --
    ,a.fragment_count
    ,a.page_count -- количеством страниц
    ,b.fill_factor
    ,a.partition_number
FROM sys.dm_db_index_physical_stats
    (DB_ID (N'amcrm')
-- , OBJECT_ID(N'dbo.as_users')
  --, OBJECT_ID(N'dbo.am_departments')
     -- , OBJECT_ID(N'dbo.am_clients')
        --  , OBJECT_ID(N'dbo.am_clientUsers')
        --  , OBJECT_ID(N'dbo.am_clientTags')
        --  , OBJECT_ID(N'dbo.am_userTransitions')

      -- , OBJECT_ID(N'dbo.am_alphaReportData3')
               , OBJECT_ID(N'dbo.am_events')

-- , OBJECT_ID(N'dbo.am_clientChanges')
-- , OBJECT_ID(N'dbo.as_en_statusesLog')
-- , OBJECT_ID(N'dbo.as_comments')
-- , OBJECT_ID(N'dbo.as_en_entityInstances')
-- , OBJECT_ID(N'dbo.am_salaryPlan')
-- , OBJECT_ID(N'dbo.am_accounts')
-- , OBJECT_ID(N'dbo.am_clientAccounts')
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;
GO