Мы используем sys.dm_exec_query_stats
для отслеживания медленных запросов и запросов, нарушающих ввод-вывод.
Это отлично работает, мы получаем много очень информативной статистики. Понятно, что это не так точно, как запуск трассировки профилировщика, поскольку вы не знаете, когда SQL Server решит отказаться от плана выполнения.
У нас довольно много запросов, в которых кешируется неправильный план выполнения. Например, запросы вроде следующего:
SELECT TOP 30 a.Id FROM Posts a JOIN Posts q ON q.Id = a.ParentId JOIN PostTags pt ON q.Id = pt.PostId WHERE a.PostTypeId = 2 AND a.DeletionDate IS NULL AND a.CommunityOwnedDate IS NULL AND a.CreationDate > @date AND LEN(a.Body) > 300 AND pt.Tag = @tag AND a.Score > 0 ORDER BY a.Score DESC
Проблема в том, что идеальный план действительно зависит от выбранной даты (скриншот идеального плана):
Однако, если кешируется неправильный план, он полностью подавляется, когда диапазон дат большой: (обратите внимание на большие жирные линии)
Чтобы преодолеть это, нам рекомендовали использовать либо OPTION (OPTIMIZE FOR UNKNOWN)
или OPTION (RECOMPILE)
OPTIMIZE FOR UNKNOWN
приводит к немного лучшему плану, который далек от оптимального. Казни отслеживаются в sys.dm_exec_query_stats
.
RECOMPILE
приводит к выбору лучшего плана, однако казнь не засчитывается и статистика отслеживается в sys.dm_exec_query_stats
.
Есть ли другое DMV, которое мы могли бы использовать для отслеживания статистики по запросам с OPTION (RECOMPILE)
? Это намеренное поведение? Есть ли другой способ перекомпиляции с отслеживанием статистики в sys.dm_exec_query_stats
?
Примечание: платформа всегда будет выполнять параметризованные запросы с использованием sp_executesql.
Возможно, вам следует использовать руководство по плану вместо опции RECOMPILE. У вас уже есть хороший план, поэтому просто добавьте его в качестве руководства по плану для вашего запроса, и оптимизатор будет создавать этот план каждый раз. Видеть Оптимизация запросов в развернутых приложениях с помощью руководств по планам и Определение планов запросов с принудительным включением плана.
В вашем случае это действительно тривиально, просто позвоните sp_create_plan_guide_from_handle
с хорошим дескриптором плана запроса:
Вы можете использовать эту хранимую процедуру, чтобы оптимизатор запросов всегда использовал определенный план запроса для указанного запроса.