Предлагаю вашему вниманию запрос, который должен быть в арсенале каждого администратора базы данных. Краткое (и значительно упрощенное) описание способа функционирования этого запроса следующее. Каждый раз, когда предстоит создать новый план выполнения, SQL Server отмечает доступные подходящие индексы для наиболее эффективного выполнения запроса. Выясняя, какие индексы существуют и подходят для использования в формировании плана выполнения, SQL Server отмечает столбцы (или комбинации столбцов), где индекса не существует, но если бы он был, то можно было бы составить лучший план. Иными словами, создавая планы выполнения, SQL Server как бы говорит: «Было бы здорово, если бы существовал индекс для этого столбца или для этих трех столбцов, упорядоченных определенным образом, с добавлением вон того столбца».

Но хотя SQL Server может учитывать такие рекомендации, программе не разрешается самостоятельно создавать индексы (и это очень хорошо). Вместо этого рекомендации по индексам передаются в динамические административные представления (DMV) sys.dm_db_missing_index*. Эти рекомендации также входят в планы выполнения. Естественно, существует множество сценариев и приемов для анализа этой информации.

Подход к отсутствующим индексам на основе стоимости

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

Несколько замечаний

Конечно, как всегда, имея дело с SQL Server, приходится учитывать ряд осложняющих факторов.

Первое и самое важное условие — никогда не следует заранее полагать, что можно взять результаты запроса и построить процедуру для динамической обработки результатов, которая начнет давать SQL Server все необходимые индексы, которые тот считает необходимыми.

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

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

Динамическое добавление каждого индекса, который SQL Server считает необходимым иметь, можно сравнить с азартной игрой. Поначалу можно и выиграть (как я слышал), но спустя немного времени приходится занимать в долг у друзей, чтобы оплатить свое пагубное пристрастие — и именно это происходит, если предоставлять SQL Server любой индекс, который программа считает необходимым. В конечном итоге это приведет к увеличению стоимости или дополнительным затратам времени при обновлении данных (то есть при выполнении операций INSERT, UPDATE и DELETE). Вы начнете замечать замедление работы и, вероятно, столкнетесь с проблемами блокировки, которые нарушат параллелизм систем. Перед оптимизатором может возникнуть так много вариантов, что для выбора между ними при создании планов выполнения, которые перекомпилируют и формируют специальные запросы, потребуется значительное время (компиляция довольно простых запросов может занимать до 5 секунд).

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

Листинг 1. Поиск пропущенных индексов

— Создан на основе комбинации:
-- превосходных запросов Missing Indexes Джейсона Стейта для получения планов выполнения:
-- http://www.jasonstrate.com/2010/12/can-you-dig-it-missing-indexes/
-- и моей собственной технологии для сбора данных о стоимости и умножения их
-- на число запусков плана выполнения, чтобы получить совокупную стоимость:
-- http://sqlmag.com/blog/performance-tip-find-your-most-expensive-queries
-- ЗАДАЧА: нужно найти способ
-- a) получить список индексов по их воздействию, а затем
-- b) перекрестно применить их планы и получить стоимости.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
PlanMissingIndexes AS (
SELECT
query_plan,
usecounts
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE
qp.query_plan.exist('//MissingIndexes') = 1
),
MissingIndexes AS (
SELECT
stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]'
, 'sysname') AS DatabaseName,
stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]'
, 'sysname') AS SchemaName,
stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]'
, 'sysname') AS TableName,
stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]'
, 'float') AS Impact,
ISNULL(CAST(stmt_xml.value('(@StatementSubTreeCost)[1]'
, 'VARCHAR(128)') as float),0) AS Cost,
pmi.usecounts UseCounts,
STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname')
FROM
stmt_xml.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE
cg.value('(@Usage)[1]', 'sysname') = 'EQUALITY'
FOR XML PATH('')), 1, 2, '') AS equality_columns
, STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname')
FROM stmt_xml.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'sysname') = 'INEQUALITY'
FOR XML PATH('')), 1, 2, '') AS inequality_columns
, STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname')
FROM stmt_xml.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'sysname') = 'INCLUDE'
FOR XML PATH('')), 1, 2, '') AS include_columns
, query_plan
, stmt_xml.value('(@StatementText)[1]', 'varchar(4000)') AS sql_text
FROM PlanMissingIndexes pmi
CROSS APPLY query_plan.nodes('//StmtSimple') AS stmt(stmt_xml)
WHERE stmt_xml.exist('QueryPlan/MissingIndexes') = 1
)
SELECT TOP 200
DatabaseName,
SchemaName,
TableName,
equality_columns,
inequality_columns,
include_columns,
usecounts,
Cost,
Cost * UseCounts [AggregateCost],
Impact,
query_plan
FROM MissingIndexes
ORDER BY
Cost * usecounts DESC;