Для получения полного доступа
зарегистрируйтесь.
Сниппет,  SQL

MS SQL Server. Принудительная перекомпиляция хранимых процедур


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

-- Исходная хранимая процедура
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
GO

-- ------------------
-- 1 вариант: Использование DBCC FREEPROCCACHE
-- Выполнение этой инструкции каждый раз будет очищать кеш планов запросов.
-- Эта инструкция не очищает план нативно-скомпилированных хранимых процедур.
-- Подробнее о синтаксисе можно почитать по ссылке:
-- https://msdn.microsoft.com/en-us/library/ms174283.aspx
DBCC FREEPROCCACHE
GO
EXEC usp_countrysearch 'UK'

-- ------------------
-- 2 вариант: Использование опции WITH RECOMPILE команды EXEC.
-- При каждом запуске хранимой процедуры с данной опцией будет производиться перекомпиляция:
EXEC usp_contrysearch 'UK' WITH RECOMPILE
GO

-- ------------------
-- 3 вариант: Использование опции RECOMPILE при создании хранимой процедуры,
-- после чего все запросы на исполнение будут приводить к ее принудительной перекомпиляции 
-- (даже без указания EXEC WITH RECOMPILE):
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
OPTION (RECOMPILE)
GO

-- ------------------
-- 4 вариант: Строго говоря, этот вариант не относится к принудительной перекомпиляции. 
-- Это вариант предварительной оптимизации, но я о нем все равно упомяну.
-- Если мы имеем известный набор планов для хранимой процедуры
-- и набор значений входных параметров для каждого из них, то мы можем выбрать наиболее оптимальный 
-- и скомпилировать хранимую процедуру так, чтобы всегда использовался этот план:
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
OPTION (OPTIMIZE FOR (@country = 'UK')) 
-- теперь для любых входных параметров будет использоаться план как для @country = 'UK'
GO
  sql MS SQL Server

Автор


jestonedev
  • Репутация: 99
  • Сниппеты : 11
  • Ревизии : 1
Подписаться

Чтобы увидеть комментарии, нужно быть участником сообщества

Регистрация