Даже если вы не работали с 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