Как удалить конкретный неверный план из кэша запросов SQL Server?

У нас есть один конкретный запрос SQL Server 2008 (не хранимый процесс, а та же строка SQL - выполняется каждые 5 минут), который периодически кэширует очень плохой план запроса.

Этот запрос обычно выполняется за несколько миллисекунд, но с этим неправильным планом запроса он занимает более 30 секунд.

Как хирургическим путем удалить только один неверно кэшированный план запросов из SQL Server 2008, не удаляя весь кэш запросов на сервере производственной базы данных?

4 ответа

Решение

Я понял несколько вещей

select * from sys.dm_exec_query_stats

покажет все кэшированные планы запросов. К сожалению, там нет текста SQL.

Однако вы можете присоединить текст SQL к планам следующим образом:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

Отсюда довольно просто добавить WHERE предложение, чтобы найти SQL, который я знаю, находится в запросе, и тогда я могу выполнить:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

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

edit: дамп всего кэша запросов также будет работать, и это менее опасно, чем кажется, по крайней мере, по моему опыту:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Если вы знаете, как выглядит хороший план, просто воспользуйтесь подсказкой плана.

Вы не можете удалить конкретную запись кэша, но вы можете очистить весь пул кэша с помощью DBCC FREESYSTEMCACHE(cachename/poolname),

Вы можете получить имя кэша неверного плана запроса, если у вас есть дескриптор плана (из sys.dm_exec_requests.plan_handle для идентификатора сессии, в котором возникли проблемы во время выполнения, или из sys.dm_exec_query_stats после выполнения):

select ce.name
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce on cp.memory_object_address = ce.memory_object_address
where cp.plan_handle = @bad_plan

Однако все планы SQL имеют название "Планы SQL", что делает выбор правильного для DBCC FREESYSTEMCACHE... сложным выбором.

Обновить

Неважно, забыл о DBCC FREEPROCCACHE(plan_handle) Да, это будет работать.

Решение FREEPROCCACHE хорошо, но более прямой способ сделать это - использовать OPTION (RECOMPILE) в вашей строке SQL (вы упомянули, что это не SP), это говорит Engine о плане одноразового использования, потому что, скорее всего, вы подозреваете есть анализ параметров или ваши статистические данные сильно отличаются от запуска к запуску, и вы подозреваете, что это проблема плохого кэшированного плана.

DECLARE @SQL NVARCHAR(4000)
SELECT @SQL = 'SELECT * FROM Table WHERE Column LIKE @NAME OPTION (RECOMPILE)'
EXEC sp_executesql @SQL, N'@NAME varchar(15)', 'MyName' 

чтобы найти правильный хеш, просто просмотрите план выполнения, сохраните этот план в формате xml или откройте его как xml,

Если вы не знаете, где найти план выполнения: вы можете активировать этот план в SSMS. В xml плана выполнения найдите

      QueryHash="

хеш находится за этим текстом, и где-то за хешем вы можете увидеть RetievedFromCache="true", если это правда, вы можете найти его в sys.dm_exec_query_stats

      select  query_hash, sql_handle from sys.dm_exec_query_stats qs where convert(varchar(255),query_hash,1) = '[your found hash goes here]'

обратите внимание, что конвертировать важно, потому что хеш является двоичным!!! теперь у вас есть sql_handle, пришло время окончательного ЗАПУСКА

      DBCC FREEPROCCACHE ([the sql_handle goes in here]) 

и план исчез, теперь движок должен создать новый, или, если вам не повезет, вы вернете старый. некоторые (рискованные) трюки по этому поводу:-переписать запрос -обновить статистику -перестроить или реорганизовать индексы -включить столбцы в индекс -изменить настройку maxdop -запустить первый запрос с репрезентативным набором параметров (прогревая сервер правильными упражнения

Другие вопросы по тегам