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

Вариант первый, можно изменить хранимую процедуру саму на себя. Например в Object Explorer выбрать хранимую процедуру, сделать Script Store Procedure as -> ALTER to -> New Query Window и выполнить то, что получилось.

Script Stored Procedure

Это приведет к тому, что при следующем запуске хранимой процедуры произойдет ее перекомпиляция. Метод очень простой и быстрый, но имеет один недостаток: это ведет к изменению поля modify_date в системном представлении sys.objects, хотя по сути дела никаких изменений мы не вносили.

Вариант второй, более сложный, сделать запрос к системному преставлению sys.dm_exec_cached_plans, получить нужный хэндл (или хэндлы) неоптимальных планов и удалить их из кэша с помощью DBCC FREEPROCCACHE. Например, в своей тестовой базе я сделаю такой запрос и получу значение поля plan_handle.

SELECT TOP 10 cp.cacheobjtype, cp.objtype, cp.plan_handle, t.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE '%uspGetBillOfMaterials%'
	AND t.dbid = DB_ID('AdventureWorks2008R2')

Результаты запроса

Проверяем, что в поле text у нас действительно нужная хранимая процедура, и после этого запускаем DBCC FREEPROCCACHE. В качестве параметра передаем значение поля plan_handle.

DBCC FREEPROCCACHE(0x050005005C31690140016C9A000000000000000000000000)

Все, ненужный нам план из кэша удален и при следующем запуске процедуры произойдет перекомпиляция.