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

1. Использование динамических объектов управления (DMO)

Один из самых простых способов проверить, используется ли план повторно, – это запросить динамические объекты управления (DMO), которые предоставляют информацию о кэше планов. Два полезных DMO для этой цели – это sys.dm_exec_procedure_stats и sys.dm_exec_query_stats.

sys.dm_exec_procedure_stats предоставляет агрегированную информацию о хранимых процедурах, а sys.dm_exec_query_stats – агрегированную информацию о запросах, которые могут выполняться внутри хранимых процедур. Оба DMO включают счетчик execution_count, который указывает, используется ли план повторно.

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

SELECT deps.execution_count,
       OBJECT_NAME(deps.object_id, deps.database_id) AS 'Procedure',
       deqp.query_plan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
ORDER BY deps.execution_count DESC

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

2. Отслеживание на стороне сервера

Еще один способ определить, используется ли план повторно, – это настройка отслеживания на стороне сервера и захват событий SP:CacheMiss или SP:CacheHit. Событие “cache miss” указывает, что запрос отсутствовал в кэше, а событие “cache hit” указывает, что он был найден.

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

Кроме того, вы также можете захватывать события повторной компиляции с помощью SQL:StmtRecompile. В то время как SP:Recompile может использоваться для процедур, рекомендуется использовать SQL:StmtRecompile для захвата повторной компиляции на уровне оператора, что применимо для ad-hoc запросов и триггеров.

Заключение

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