sys.dm_db_missing_index_details (Transact-SQL)

Статья
08/21/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Возвращает подробные сведения о отсутствующих индексах.

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

Имя столбца	Тип данных	Description
index_handle	int	Идентифицирует специфический отсутствующий индекс. Этот идентификатор уникален для сервера. `index_handle` является ключом этой таблицы.
database_id	smallint	Идентифицирует базу данных, в которой находится таблица с отсутствующим индексом. В База данных SQL Azure значения уникальны в одной базе данных или эластичном пуле, но не в логическом сервере.
object_id	int	Идентифицирует таблицу, в которой отсутствует индекс.
equality_columns	nvarchar(4000)	Список столбцов с разделителями-запятыми, соответствующих предикатам равенства в форме: table.column = constant_value
inequality_columns	nvarchar(4000)	Список столбцов с разделителями-запятыми, который соответствует предикатам неравенства, например предикатам в форме: table.column>constant_value Любой оператор сравнения, кроме «=», выражает неравенство.
included_columns	nvarchar(4000)	Список столбцов с разделителями-запятыми, необходимых в качестве столбцов для запроса. Дополнительные сведения о охватывающих или включенных столбцах см. в разделе "Создание индексов с включенными столбцами". Для индексов, оптимизированных для памяти (хэш и оптимизированных для памяти некластеризованных), игнорируйте `included_columns`. Все столбцы таблицы включаются в каждый индекс с оптимизацией для памяти.
инструкция	nvarchar(4000)	Имя таблицы, в которой отсутствует индекс.

Замечания

Информация, возвращаемая обновлением sys.dm_db_missing_index_details , когда запрос оптимизирован оптимизатором запросов и не сохраняется. Отсутствующие сведения о индексе хранятся только до перезапуска ядра СУБД. Администраторы базы данных должны периодически делать резервные копии сведений об отсутствующих индексах, чтобы сохранить их после перезагрузки сервера. Узнать время последнего запуска ядра СУБД можно в столбце sqlserver_start_time из sys.dm_os_sys_info.

Чтобы определить, какие отсутствующие группы индексов является частью определенного отсутствуюющего индекса, можно запросить sys.dm_db_missing_index_groups динамическое административное представление, обсоединив его с sys.dm_db_missing_index_details учетом столбца index_handle .

Примечание.

Результирующий набор для этого динамического административного представления ограничен 600 строками. Каждая строка содержит один отсутствующий индекс. Если у вас более 600 отсутствующих индексов, следует устранить существующие отсутствующие индексы, чтобы просмотреть новые.

Использование отсутствующих сведений об индексе в инструкциях CREATE INDEX

Чтобы преобразовать данные, возвращаемые sys.dm_db_missing_index_details в инструкцию CREATE INDEX для индексов, оптимизированных для памяти, и на основе дисков, столбцы равенства должны быть помещены перед столбцами неравенства, и вместе они должны сделать ключ индекса. Включенные столбцы должны быть добавлены в инструкцию CREATE INDEX с помощью предложения INCLUDE. Чтобы определить эффективный порядок столбцов равенства, расположите их на основе их выборности, перечисляя наиболее выбираемые столбцы первыми (крайние левые в списке столбцов). Дополнительные сведения см. в разделе "Настройка некластеризованных индексов" с отсутствующими предложениями индекса, включая ограничения функции отсутствующих индексов.

Дополнительные сведения об индексах, оптимизированных для памяти, см. в разделе "Индексы для оптимизированных для памяти таблиц".

Согласованность транзакций

Если транзакция создает или удаляет таблицу, то строки, содержащие сведения отсутствующих индексов об удаленных объектах, удаляются из данного объекта DMO, сохраняя согласованность транзакций. Узнайте больше об ограничениях отсутствующих функций индекса.

Разрешения

На SQL Server и управляемом экземпляре SQL необходимо разрешение VIEW SERVER STATE.

Для целей службы База данных SQL Basic, S0 и S1, а также для баз данных в эластичных пулах, учетной записи администратора сервера, учетной записи администратора Microsoft Entra или членства в ##MS_ServerStateReader## роли сервера требуется. Для всех остальных целей обслуживания базы данных SQL требуется разрешение VIEW DATABASE STATE в базе данных или членство в роли сервера ##MS_ServerStateReader##.

Разрешения для SQL Server 2022 и более поздних версий

Требуется разрешение VIEW SERVER PERFORMANCE STATE на сервере.

Примеры

В следующем примере возвращаются отсутствующие предложения индекса для текущей базы данных. Отсутствующие предложения по индексу должны сочетаться, если это возможно, с существующими индексами в текущей базе данных. Узнайте, как применять эти предложения в настройке некластеризованных индексов с отсутствующими предложениями индекса.

SELECT
  CONVERT (varchar(30), getdate(), 126) AS runtime,  mig.index_group_handle,  mid.index_handle,
  CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns, '') + CASE
    WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ','
    ELSE ''
  END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Примечание.

Скрипт создания индексов на панели элементов Tiger Майкрософт изучает динамические административные представления отсутствующих индексов и автоматически удаляет избыточные предложения, анализирует индексы с низким воздействием и формирует скрипты создания индексов для проверки. Как показано в запросе выше, он не выполняет команды по созданию индексов. Скрипт создания индексов подходит для работы с SQL Server и Управляемым экземпляром SQL Azure. Для Базы данных SQL Azure рекомендуется реализовать автоматическую настройку индексов.

Следующие шаги

Дополнительные сведения о функции отсутствующих индексов см. в следующих статьях:

Поделиться через