11_1_5_SQLServer_ИНДЕКСЫ

-- 11_1_5_SQLServer_ИНДЕКСЫ

--Набор скриптов для знакомства с SQL Server
--https://infostart.ru/1c/articles/1128594/

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.Name AS TableName ,
        i.Name AS IndexName
FROM    sys.objects o
        INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE   o.Type = 'U' -- User table
        AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes
ORDER BY o.NAME ,
        i.name;


-- статистика использования индексов.
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
       ,IX.name AS Index_Name
       ,IX.type_desc Index_Type
       ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
       ,IXUS.user_seeks AS NumOfSeeks
       ,IXUS.user_scans AS NumOfScans
       ,IXUS.user_lookups AS NumOfLookups
       ,IXUS.user_updates AS NumOfUpdates
       ,IXUS.last_user_seek AS LastSeek
       ,IXUS.last_user_scan AS LastScan
       ,IXUS.last_user_lookup AS LastLookup
       ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update
-- Этим скриптом Вы можете получить информацию о количестве операций поиска, сканирования и некоторых других операций на индексах. В итоге можно составить список тех объектов, которых из базы можно удалить.


-- Кроме этого, можно составить список индексов, которые имеют высокие издержки при использовании.
-- Возможно, это "тяжелые" индексы, которые созданы на часто обновляемых таблицах или др. варианты.

SELECT TOP 1
    [Maintenance cost]  = (user_updates + system_updates)
       , [Retrieval usage] = (user_seeks + user_scans + user_lookups)
       , DatabaseName = DB_NAME()
       , TableName = OBJECT_NAME(s.[object_id])
       , IndexName = i.name
INTO #TempMaintenanceCost
FROM sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
        AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_updates + system_updates) > 0 -- Only report on active rows.
    AND s.[object_id] = -999
-- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?];
-- Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
       [Maintenance cost]  = (user_updates + system_updates)
       ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
       ,DatabaseName = DB_NAME()
       ,TableName = OBJECT_NAME(s.[object_id])
       ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
   AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
   AND i.name IS NOT NULL    -- Ignore HEAP indexes.
   AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
   AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost]  DESC
;
'
-- Select records.
SELECT TOP 10
    *
FROM #TempMaintenanceCost
ORDER BY [Maintenance cost]  DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost



-- отсутствующих индексах
SELECT
    @@ServerName AS ServerName, -- Имя сервера
    DB_NAME() AS DBName, -- Имя базы
    t.name AS 'Affected_table', -- Имя таблицы
    (LEN(ISNULL(ddmid.equality_columns, N'')
              + CASE WHEN ddmid.equality_columns IS NOT NULL
    AND ddmid.inequality_columns IS NOT NULL THEN ','
                     ELSE ''
                END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')
                                   + CASE WHEN ddmid.equality_columns
                                                             IS NOT NULL
    AND ddmid.inequality_columns
                                                             IS NOT NULL
                                          THEN ','
                                          ELSE ''
                                     END, ',', '')) ) + 1 AS K, -- Количество ключей в индексе
  COALESCE(ddmid.equality_columns, '')
        + CASE WHEN ddmid.equality_columns IS NOT NULL
    AND ddmid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + COALESCE(ddmid.inequality_columns, '') AS Keys, -- Ключевые столбцы индекса
  COALESCE(ddmid.included_columns, '') AS [include], -- Неключевые столбцы индекса
  'Create NonClustered Index IX_' + t.name + '_missing_'
        + CAST(ddmid.index_handle AS VARCHAR(20))
        + ' On ' + ddmid.[statement] COLLATE database_default
        + ' (' + ISNULL(ddmid.equality_columns, '')
        + CASE WHEN ddmid.equality_columns IS NOT NULL
    AND ddmid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(ddmid.inequality_columns, '') + ')'
        + ISNULL(' Include (' + ddmid.included_columns + ');', ';')
                                                  AS sql_statement, -- Команда для создания индекса
  ddmigs.user_seeks, -- Количество операций поиска
  ddmigs.user_scans, -- Количество операций сканирования
  CAST(( ddmigs.user_seeks + ddmigs.user_scans)
        * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact',
  avg_user_impact, -- Средний процент выигрыша
  ddmigs.last_user_seek, -- Последняя операция поиска
  ( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds
  FROM sys.databases
  WHERE     name = 'tempdb'
        ) SecondsUptime
FROM sys.dm_db_missing_index_groups ddmig
  INNER JOIN sys.dm_db_missing_index_group_stats ddmigs
  ON ddmigs.group_handle = ddmig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details ddmid
  ON ddmig.index_handle = ddmid.index_handle
  INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE   ddmid.database_id = DB_ID()
ORDER BY est_impact DESC;

-- фрагментация индекса

SELECT
    DB_NAME([IF].database_id) AS [Имя базы]
    ,OBJECT_NAME(object_id) AS [Имя таблицы]
    ,OBJECT_NAME([IF].index_id) AS [Имя индкса]
    ,[IF].*
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) AS [IF]
WHERE avg_fragmentation_in_percent > 30 -- процент фрагментации выше 30%.
ORDER BY avg_fragmentation_in_percent