Индексы являются важной частью базы данных SQL Server, которые могут значительно улучшить производительность запросов. Однако, они требуют регулярного мониторинга и обслуживания для обеспечения оптимальной производительности. В этой статье мы рассмотрим различные способы мониторинга и анализа индексов SQL Server с использованием встроенных функций, таких как динамические представления управления (DMV) и системные таблицы/каталоговые представления.

Информация о существующих индексах

Для начала давайте получим информацию о существующих индексах в базе данных SQL Server. Это включает идентификатор индекса, имя, тип и столбцы, включенные в индекс. Мы можем использовать следующий запрос:

SELECT
   @@SERVERNAME AS [ИмяСервера],
   DB_NAME() AS [ИмяБазыДанных],
   [SchemaName] AS [ИмяСхемы],
   [ObjectName] AS [ИмяОбъекта],
   [ObjectType] AS [ТипОбъекта],
   [IndexID] AS [ИдентификаторИндекса],
   [IndexName] AS [ИмяИндекса],
   [IndexType] AS [ТипИндекса],
   COALESCE([0],[1],'') AS [Столбец1],
   ISNULL([2],'') AS [Столбец2],
   ISNULL([3],'') AS [Столбец3],
   ISNULL([4],'') AS [Столбец4],
   ISNULL([5],'') AS [Столбец5],
   ISNULL([6],'') AS [Столбец6],
   ISNULL([7],'') AS [Столбец7],
   ISNULL([8],'') AS [Столбец8],
   ISNULL([9],'') AS [Столбец9],
   ISNULL([10],'') AS [Столбец10],
   CASE 
      WHEN [IsIncludedColumn] = 0x1 THEN 'Да'
      WHEN [IsIncludedColumn] = 0x0 THEN 'Нет'
      WHEN [IsIncludedColumn] IS NULL THEN 'Н/Д'
   END AS [ПокрывающийИндекс],
   [IsDisabled] AS [Отключен]
FROM (
   SELECT
      SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName],
      [sObj].[name] AS [ObjectName],
      CASE
         WHEN [sObj].[type] = 'U' THEN 'Таблица'
         WHEN [sObj].[type] = 'V' THEN 'Представление'
      END AS [ObjectType],
      [sIdx].[index_id] AS [IndexID],
      ISNULL([sIdx].[name], 'Н/Д') AS [IndexName],
      CASE
         WHEN [sIdx].[type] = 0 THEN 'Heap'
         WHEN [sIdx].[type] = 1 THEN 'Кластеризованный'
         WHEN [sIdx].[type] = 2 THEN 'Некластеризованный'
         WHEN [sIdx].[type] = 3 THEN 'XML'
         WHEN [sIdx].[type] = 4 THEN 'Пространственный'
         WHEN [sIdx].[type] = 5 THEN 'Зарезервировано для будущего использования'
         WHEN [sIdx].[type] = 6 THEN 'Некластеризованный индекс столбцового хранилища'
      END AS [IndexType],
      [sCol].[name] AS [ColumnName],
      [sIdxCol].[is_included_column] AS [IsIncludedColumn],
      [sIdxCol].[key_ordinal] AS [KeyOrdinal],
      [sIdx].[is_disabled] AS [IsDisabled]
   FROM 
      [sys].[indexes] AS [sIdx]
      INNER JOIN [sys].[objects] AS [sObj]
         ON [sIdx].[object_id] = [sObj].[object_id]
      LEFT JOIN [sys].[index_columns] AS [sIdxCol]
         ON [sIdx].[object_id] = [sIdxCol].[object_id]
         AND [sIdx].[index_id] = [sIdxCol].[index_id]
      LEFT JOIN [sys].[columns] AS [sCol]
         ON [sIdxCol].[object_id] = [sCol].[object_id]
         AND [sIdxCol].[column_id] = [sCol].[column_id]
   WHERE
      [sObj].[type] IN ('U','V')
      AND [sObj].[is_ms_shipped] = 0x0
) AS [UnpivotedData]
PIVOT 
(
   MIN([ColumnName])
   FOR [KeyOrdinal] IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS [ColumnPivot]

Информация о покрывающих индексах

Покрывающий индекс – это некластеризованный индекс, который включает все столбцы, необходимые для выполнения запроса, что позволяет избежать поиска в кластеризованном индексе. Чтобы получить информацию о покрывающем индексе, мы можем использовать следующий запрос:

SELECT
    SCHEMA_NAME([sObj].[schema_id]) AS [ИмяСхемы],
    [sObj].[name] AS [ИмяОбъекта],
    CASE
        WHEN [sObj].[type] = 'U' THEN 'Таблица'
        WHEN [sObj].[type] = 'V' THEN 'Представление'
    END AS [ТипОбъекта],
    [sIdx].[index_id] AS [ИдентификаторИндекса],
    ISNULL([sIdx].[name], 'Н/Д') AS [ИмяИндекса],
    CASE
        WHEN [sIdx].[type] = 0 THEN 'Heap'
        WHEN [sIdx].[type] = 1 THEN 'Кластеризованный'
        WHEN [sIdx].[type] = 2 THEN 'Некластеризованный'
        WHEN [sIdx].[type] = 3 THEN 'XML'
        WHEN [sIdx].[type] = 4 THEN 'Пространственный'
        WHEN [sIdx].[type] = 5 THEN 'Зарезервировано для будущего использования'
        WHEN [sIdx].[type] = 6 THEN 'Некластеризованный индекс столбцового хранилища'
    END AS [ТипИндекса],
    [sCol].[name] AS [ИмяСтолбца],
    CASE 
        WHEN [sIdxCol].[is_included_column] = 0x1 THEN 'Да'
        WHEN [sIdxCol].[is_included_column] = 0x0 THEN 'Нет'
        WHEN [sIdxCol].[is_included_column] IS NULL THEN 'Н/Д'
    END AS [ВключенныйСтолбец],
    [sIdxCol].[key_ordinal] AS [ПорядковыйНомерКлюча]
FROM 
    [sys].[indexes] AS [sIdx]
    INNER JOIN [sys].[objects] AS [sObj]
        ON [sIdx].[object_id] = [sObj].[object_id]
    LEFT JOIN [sys].[index_columns] AS [sIdxCol]
        ON [sIdx].[object_id] = [sIdxCol].[object_id]
        AND [sIdx].[index_id] = [sIdxCol].[index_id]
    LEFT JOIN [sys].[columns] AS [sCol]
        ON [sIdxCol].[object_id] = [sCol].[object_id]
        AND [sIdxCol].[column_id] = [sCol].[column_id]
WHERE
    SCHEMA_NAME([sObj].[schema_id]) = 'ИмяВашейСхемы'
    AND [sObj].[name] = 'ИмяВашегоОбъекта'
    AND [sIdx].[name] = 'ИмяВашегоИндекса'

Физическая статистика индексов

Далее давайте запросим физическую статистику индекса, такую как размер и фрагментация. Эта информация может помочь определить индексы, требующие обслуживания. Используйте следующий запрос:

SELECT
   @@SERVERNAME AS [ИмяСервера],
   DB_NAME() AS [ИмяБазыДанных],
   SCHEMA_NAME([sObj].[schema_id]) AS [ИмяСхемы],
   [sObj].[name] AS [ИмяОбъекта],
   CASE
      WHEN [sObj].[type] = 'U' THEN 'Таблица'
      WHEN [sObj].[type] = 'V' THEN 'Представление'
   END AS [ТипОбъекта],
   [sIdx].[index_id] AS [ИдентификаторИндекса],
   ISNULL([sIdx].[name], 'Н/Д') AS [ИмяИндекса],
   CASE
      WHEN [sIdx].[type] = 0 THEN 'Heap'
      WHEN [sIdx].[type] = 1 THEN 'Кластеризованный'
      WHEN [sIdx].[type] = 2 THEN 'Некластеризованный'
      WHEN [sIdx].[type] = 3 THEN 'XML'
      WHEN [sIdx].[type] = 4 THEN 'Пространственный'
      WHEN [sIdx].[type] = 5 THEN 'Зарезервировано для будущего использования'
      WHEN [sIdx].[type] = 6 THEN 'Некластеризованный индекс столбцового хранилища'
   END AS [ТипИндекса],
   ISNULL([sPtn].[partition_number], 1) AS [НомерРаздела],
   [sdmfIPS].[alloc_unit_type_desc] AS [ТипВыделенияИндекса],
   [IdxSizeDetails].[IndexSizeInKB] AS [РазмерИндексаВКБ],
   [sIdx].[fill_factor] AS [ФакторЗаполнения],
   CAST([sdmfIPS].[avg_fragmentation_in_percent] AS NUMERIC(5,2)) AS [СредняяФрагментацияВПроцентах]
FROM 
   [sys].[indexes] AS [sIdx]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sIdx].[object_id] = [sObj].[object_id]
   LEFT JOIN  [sys].[partitions] AS [sPtn]
      ON [sIdx].[object_id] = [sPtn].[object_id]
      AND [sIdx].[index_id] = [sPtn].[index_id]
   LEFT JOIN (
            SELECT
               [sIdx].[object_id],
               [sIdx].[index_id],
               SUM([sAU].[used_pages]) * 8 AS [IndexSizeInKB]
            FROM 
               [sys].[indexes] AS [sIdx]
               INNER JOIN [sys].[partitions] AS [sPtn]
                  ON [sIdx].[object_id] = [sPtn].[object_id]
                  AND [sIdx].[index_id] = [sPtn].[index_id]
               INNER JOIN [sys].[allocation_units] AS [sAU]
                  ON [sPtn].[partition_id] = [sAU].[container_id]
            GROUP BY [sIdx].[object_id], [sIdx].[index_id]
   ) [IdxSizeDetails]
      ON [sIdx].[object_id] = [IdxSizeDetails].[object_id]
      AND [sIdx].[index_id] = [IdxSizeDetails].[index_id]
   LEFT JOIN [sys].[dm_db_index_physical_stats] (DB_ID(),NULL,NULL,NULL,'LIMITED') [sdmfIPS]
      ON [sIdx].[object_id] = [sdmfIPS].[object_id]
      AND [sIdx].[index_id] = [sdmfIPS].[index_id]
      AND [sdmfIPS].[database_id] = DB_ID()
WHERE
   [sObj].[type] IN ('U','V')
   AND [sObj].[is_ms_shipped] = 0x0
   AND [sIdx].[is_disabled] = 0x0

Статистика использования индексов

Важно понимать, насколько эффективно используются существующие индексы. Мы можем получить статистику использования индексов с помощью следующего запроса:

SELECT
   @@SERVERNAME AS [ИмяСервера],
   DB_NAME() AS [ИмяБазыДанных],
   SCHEMA_NAME([sObj].[schema_id]) AS [ИмяСхемы],
   [sObj].[name] AS [ИмяОбъекта],
   CASE
      WHEN [sObj].[type] = 'U' THEN 'Таблица'
      WHEN [sObj].[type] = 'V' THEN 'Представление'
   END AS [ТипОбъекта],
   [sIdx].[index_id] AS [ИдентификаторИндекса],
   ISNULL([sIdx].[name], 'Н/Д') AS [ИмяИндекса],
   CASE
      WHEN [sIdx].[type] = 0 THEN 'Heap'
      WHEN [sIdx].[type] = 1 THEN 'Кластеризованный'
      WHEN [sIdx].[type] = 2 THEN 'Некластеризованный'
      WHEN [sIdx].[type] = 3 THEN 'XML'
      WHEN [sIdx].[type] = 4 THEN 'Пространственный'
      WHEN [sIdx].[type] = 5 THEN 'Зарезервировано для будущего использования'
      WHEN [sIdx].[type] = 6 THEN 'Некластеризованный индекс столбцового хранилища'
   END AS [ТипИндекса],
   [sdmvIUS].[user_seeks] AS [ВсегоПользовательскихПоисков],
   [sdmvIUS].[user_scans] AS [ВсегоПользовательскихСканирований],
   [sdmvIUS].[user_lookups] AS [ВсегоПользовательскихПоисковПоЗначению],
   [sdmvIUS].[user_updates] AS [ВсегоПользовательскихОбновлений],
   [sdmvIUS].[last_user_seek] AS [ПоследнийПользовательскийПоиск],
   [sdmvIUS].[last_user_scan] AS [ПоследнийПользовательскийСкан],
   [sdmvIUS].[last_user_lookup] AS [ПоследнийПользовательскийПоискПоЗначению],
   [sdmvIUS].[last_user_update] AS [ПоследнийПользовательскийОбновление],
   [sdmfIOPS].[leaf_insert_count] AS [КоличествоВставокНаУровнеЛистьев],
   [sdmfIOPS].[leaf_update_count] AS [КоличествоОбновленийНаУровнеЛистьев],
   [sdmfIOPS].[leaf_delete_count] AS [КоличествоУдаленийНаУровнеЛистьев]
FROM
   [sys].[indexes] AS [sIdx]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sIdx].[object_id] = [sObj].[object_id]
   LEFT JOIN [sys].[dm_db_index_usage_stats] AS [sdmvIUS]
      ON [sIdx].[object_id] = [sdmvIUS].[object_id]
      AND [sIdx].[index_id] = [sdmvIUS].[index_id]
      AND [sdmvIUS].[database_id] = DB_ID()
   LEFT JOIN [sys].[dm_db_index_operational_stats] (DB_ID(),NULL,NULL,NULL) AS [sdmfIOPS]
      ON [sIdx].[object_id] = [sdmfIOPS].[object_id]
      AND [sIdx].[index_id] = [sdmfIOPS].[index_id]
WHERE
   [sObj].[type] IN ('U','V')
   AND [sObj].[is_ms_shipped] = 0x0
   AND [sIdx].[is_disabled] = 0x0

Информация о недостающих индексах

С течением времени, по мере изменения базы данных и шаблонов запросов, могут потребоваться новые индексы, а существующие индексы могут устареть. Чтобы определить недостающие индексы, мы можем использовать следующий запрос:

SELECT
   @@SERVERNAME AS [ИмяСервера],
   DB_NAME() AS [ИмяБазыДанных],
   SCHEMA_NAME([sObj].[schema_id]) AS [ИмяСхемы],
   [sObj].[name] AS [ИмяОбъекта],
   CASE [sObj].[type]
      WHEN 'U' THEN 'Таблица'
      WHEN 'V' THEN 'Представление'
      ELSE 'Неизвестно'
   END AS [ТипОбъекта],
   [sdmvMID].[equality_columns] AS [СтолбцыРавенства],
   [sdmvMID].[inequality_columns] AS [СтолбцыНеравенства],
   [sdmvMID].[included_columns] AS [ВключенныеСтолбцы],
   [sdmvMIGS].[user_seeks] AS [ОжидаемыеПоискиИндексаПользовательскимиЗапросами],
   [sdmvMIGS].[user_scans] AS [ОжидаемыеСканированияИндексаПользовательскимиЗапросами],
   [sdmvMIGS].[last_user_seek] AS [ОжидаемыйПоследнийПоискИндексаПользовательскимиЗапросами],
   [sdmvMIGS].[last_user_scan] AS [ОжидаемыйПоследнийСканИндексаПользовательскимиЗапросами],
   [sdmvMIGS].[avg_total_user_cost] AS [ОжидаемоеСреднееУменьшениеЗатратПользовательскихЗапросов],
   [sdmvMIGS].[avg_user_impact] AS [ОжидаемаяСредняяВыгодаотПользовательскихЗапросовВПроцентах]
FROM 
   [sys].[dm_db_missing_index_details] AS [sdmvMID]
   LEFT JOIN [sys].[dm_db_missing_index_groups] AS [sdmvMIG]
      ON [sdmvMID].[index_handle] = [sdmvMIG].[index_handle]
   LEFT JOIN [sys].[dm_db_missing_index_group_stats] AS [sdmvMIGS]
      ON [sdmvMIG].[index_group_handle] = [sdmvMIGS].[group_handle]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sdmvMID].[object_id] = [sObj].[object_id]
WHERE
   [sdmvMID].[database_id] = DB_ID()
   AND [sObj].[type] IN ('U','V')
   AND [sObj].[is_ms_shipped] = 0x0

Информация об неиспользуемых индексах

Наконец, давайте получим список неиспользуемых индексов в базе данных SQL Server. Это индексы, которые не используются ни одним запросом и могут быть кандидатами на удаление. Используйте следующий запрос:

SELECT
   @@SERVERNAME AS [ИмяСервера],
   DB_NAME() AS [ИмяБазыДанных],
   SCHEMA_NAME([sObj].[schema_id]) AS [ИмяСхемы],
   [sObj].[name] AS [ИмяОбъекта],
   CASE
      WHEN [sObj].[type] = 'U' THEN 'Таблица'
      WHEN [sObj].[type] = 'V' THEN 'Представление'
   END AS [ТипОбъекта],
   [sIdx].[index_id] AS [ИдентификаторИндекса],
   ISNULL([sIdx].[name], 'Н/Д') AS [ИмяИндекса],
   CASE
      WHEN [sIdx].[type] = 0 THEN 'Heap'
      WHEN [sIdx].[type] = 1 THEN 'Кластеризованный'
      WHEN [sIdx].[type] = 2 THEN 'Некластеризованный'
      WHEN [sIdx].[type] = 3 THEN 'XML'
      WHEN [sIdx].[type] = 4 THEN 'Пространственный'
      WHEN [sIdx].[type] = 5 THEN 'Зарезервировано для будущего использования'
      WHEN [sIdx].[type] = 6 THEN 'Некластеризованный индекс столбцового хранилища'
   END AS [ТипИндекса]
FROM
   [sys].[indexes] AS [sIdx]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sIdx].[object_id] = [sObj].[object_id]
WHERE
   NOT EXISTS (
      SELECT *
      FROM [sys].[dm_db_index_usage_stats] AS [sdmfIUS]
      WHERE 
         [sIdx].[object_id] = [sdmfIUS].[object_id]
         AND [sIdx].[index_id] = [sdmfIUS].[index_id]
         AND [sdmfIUS].[database_id] = DB_ID()
   )
   AND [sObj].[type] IN ('U','V')
   AND [sObj].[is_ms_shipped] = 0x0
   AND [sIdx].[is_disabled] = 0x0

Эти запросы предоставляют ценную информацию о состоянии и использовании индексов в базе данных SQL Server. Регулярный мониторинг и анализ индексов могут помочь оптимизировать производительность запросов и обеспечить бесперебойную работу системы.

Не забудьте проанализировать результаты этих запросов и принять соответствующие меры на основе вашего анализа. Перед внесением любых изменений, связанных с индексами, важно тщательно оценить их влияние и потенциальные преимущества.

Эти запросы были протестированы с SQL Server 2012, но должны работать с минимальными или без изменений на SQL Server 2005, 2008 и 2008 R2.