Параметризованная процедура и гарантированный план запроса

by Alexey Knyazev 30. января 2012 00:07

- Процедура стала работать медленнее, чем обычно?
- Запрос выполняется быстро, а процедура, в которой подобный запрос, работает очень долго?
- У процедуры неоптимальный план запроса?

Если ответ "Да" хоть на один из вопросов, то эта статья для вас. Я расскажу и покажу, как можно повлиять на работу процедуры и быть уверенным, что в кэше окажется ожидаемый план запроса (а значит никаких больше сюрпризов) для вашей процедуры.

Тема не нова, но если вы это читаете, то моё время потрачено не зря.

Описание проблемы

Первым делом создадим таблицу с тестовым набором данных. Это будет таблица, в которой хранится информация по пользователям. При этом я умышленно создам очень много записей по одному из пользователей и небольшой набор строк, которые относятся к другим юзерам.

--Тестовая таблица dbo.user_activity_log;
if object_id ( N'dbo.user_activity_log', N'U' ) is not null
  drop table dbo.user_activity_log;
go
create table dbo.user_activity_log ( user_name        varchar(10)
                                   , process_datetime datetime  
                                   );
go

--1000 строк с информацией по пользователю 'User01'
with cte as
(
  select 1 i
  union all
  select i + 1 from cte where i < 1000
)
insert into dbo.user_activity_log ( user_name, process_datetime )
  select 'User01', dateadd( hh, i, '20100101' ) 
    from cte 
    option (maxrecursion 999);

--По одной строке о пользователях 'User02' - 'User09'
insert into dbo.user_activity_log
  values ( 'User02', '20120101' )
       , ( 'User03', '20120101' ) 
       , ( 'User04', '20120101' ) 
       , ( 'User05', '20120101' ) 
       , ( 'User06', '20120101' ) 
       , ( 'User07', '20120101' ) 
       , ( 'User08', '20120101' )
       , ( 'User09', '20120101' );  
go

--Индекс по полю user_name, для иллюстрации различного плана, который выбирает оптимизатор
create index idx_user_name on dbo.user_activity_log ( user_name );
go
Мы знаем, что Оптимизатор запросов использует статистику для оценки количества элементов или количества строк в результате запроса, что позволяет оптимизатору запросов создавать высококачественные планы запросов. Например, на основе оценки количества элементов оптимизатор запросов может выбрать в плане запроса оператор поиска по индексу, а не оператор просмотра индекса, повышая производительность запроса за счет использования менее ресурсоемкого поиска по индексу (http://msdn.microsoft.com/ru-ru/library/ms174384.aspx). Поэтому первым делом обратимся к статистике по нашему индексу:
dbcc show_statistics ( 'dbo.user_activity_log', idx_user_name ) with histogram;


Рис 01. Статистика для индекса idx_user_name

По статистике (на рисунке) видно, что при построении запроса, в котором в качестве условия поиска указан пользователь, в зависимости от входного параметра оптимизатор должен построить разный план. Так, если в качестве пользователя указан User02 - User09, то для более оперативного поиска выгодно использовать поиск по некластерному индексу по полю user_name, но если на входе указан User01, то оптимизатор, скорее всего, выберет полное сканирование таблицы, т.к. необходимо просмотреть много строк таблицы (в нашем примере более 99%) и полный скан будет требовать меньшего количества операций ввода - вывода, и должен выполняться быстрее, чем повторное сканирование не кластерного индекса.

Но это все теория, теперь вернёмся к проблеме, связанной с неоптимальным планом параметризованных процедур.
Создадим примитивную процедуру с одним входным параметром @user_name, которая будет осуществлять выборку из нашей таблицы.
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log;
go

create procedure dbo.p_user_activity_log
(
  @user_name varchar(10)
)
as
select * from dbo.user_activity_log
  where user_name = @user_name;
go

Теперь при первом вызове этой процедуры создастся план актуальный для входного параметра, который и будет помещён в кэш. При следующих вызовах не компилируется новый план, процедура берёт его из кэша, при этом он может быть не самым оптимальным для нового входного значения.
--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Первый запуск нашей процедуры dbo.p_user_activity_log c пустым входным параметром
exec dbo.p_user_activity_log @user_name = '';

--Просмотр плана для нашей процедуры, который был помещён в кэш
select cacheobjtype, query_plan 
  from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
  where cp.objtype  = 'Proc'
    and qp.objectid = object_id ( N'dbo.p_user_activity_log', N'P' ); 


Рис 02. Графический план запроса

Оптимизатор построил план на основании входного параметра @user_name = ''. Т.к. план строится на основании статистики, то оптимизатор ожидаемо выбрал поиск по некластерному индексу.
Теперь в качестве входного параметра передадим @user_name = 'User02'. Очевидно, что для данного параметра поиск по некластерному индексу будет так же оптимален, т.к. такая запись в таблице одна.
set nocount on;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--и статистику ввода-вывода
set statistics io on;

exec dbo.p_user_activity_log @user_name = 'User02';


Рис 03. Сведения о профиле для инструкции


Рис 04. Статистика ввод-вывода

А теперь посмотрим на те же статистики, но для @user_name = 'User01'.
set nocount on;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--и статистику ввода-вывода
set statistics io on;

exec dbo.p_user_activity_log @user_name = 'User01';


Рис 05. Сведения о профиле для инструкции

Не смотря на то, что для параметра 'User01' выгоднее использовать полное сканирование таблицы, мы опять использовали план из кэша и осуществляли поиск по некластерному индексу. При этом особенно стоит обратить внимание на поля
Rows - Фактическое количество строк, созданных каждым оператором.
EstimateRows - Предполагаемое количество строк вывода от данного оператора.

А теперь статистика ввода-вывода:


Рис 06. Статистика ввод-вывода

Количество чтений просто зашкаливает для нашей небольшой таблицы, их больше 1000(!).
Теперь попробуем снова очистить кэш и выполнить процедуру, но уже с параметром 'User01'. Посмотрим, какой теперь план будет создан и помещён в кэш и другие статистические данные.
set nocount on;

--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--и статистику ввода-вывода
set statistics io on;

--Первый запуск нашей процедуры dbo.p_user_activity_log c входным параметром @user_name = 'User01'
exec dbo.p_user_activity_log @user_name = 'User01';

set statistics profile off;
set statistics io off;

--Просмотр плана для нашей процедуры, который был помещён к вэш
select cacheobjtype, query_plan 
  from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
  where cp.objtype  = 'Proc'
    and qp.objectid = object_id ( N'dbo.p_user_activity_log', N'P' ); 

Сперва план, который попал в кэш:


Рис 07. Графический план запроса

Теперь в кэше план в котором оптимизатор делает полное сканирование таблицы.


Рис 08. Сведения о профиле для инструкции


Рис 09. Статистика ввод-вывода

Теперь у нас всего 4 логических чтения для параметра 'User01', против 1005 при предыдущем плане.
Нетрудно догадаться, что теперь наш план будет не оптимален для других входных параметров.

Именно кэширование плана, которое является одним из преимуществ использования процедур, может стать причиной "замедления" работы некоторого нашего кода.
Кроме того, даже если сейчас в кэше оптимальный план, то вы не застрахованы от того, что после перекомпиляции плана выполнения в кэше окажется совсем не то, что мы ожидаем. Некоторые изменения в базе данных могут привести к тому, что план выполнения при изменении ее состояния станет неэффективным или неправильным. SQL Server обнаруживает изменения, которые могут сделать план выполнения недействительным, и помечает такой план как неправильный. При следующем выполнении данного запроса план должен быть перекомпилирован. План может стать недействительным в следующих случаях:
  • Изменены таблица или представления, на которые ссылается запрос (ALTER TABLE или ALTER VIEW).
  • Изменены индексы, используемые планом выполнения.
  • Обновлена статистика, используемая планом выполнения, сформированная либо явным образом по UPDATE STATISTICS, либо автоматически.
  • Удалены индексы, используемые планом выполнения.
  • Явно вызвана процедура sp_recompile.
  • Частое изменение ключей (инструкциями INSERT или DELETE от пользователей, изменяющих таблицу, на которую ссылается запрос).
  • Для таблиц с триггерами: значительный рост числа строк в таблицах inserted и deleted.
  • Выполнение хранимой процедуры с параметром WITH RECOMPILE.


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

Способы решения

Варианты, рассмотренные ниже, описывают, как решить проблемы с неоптимальным планом для разных версий SQL Server. Некоторые из решений появились в более поздних версия SQL Server, по возможности я опишу, как применить похожее решение, если ваша версия ниже.

Постоянная перекомпиляция (RECOMPILE)

Создание хранимой процедуры с параметром WITH RECOMPILE в определении указывает, что SQL Server не будет кэшировать план этой процедуры; хранимая процедура будет перекомпилироваться при каждом запуске. Параметр WITH RECOMPILE полезен в том случае, когда хранимая процедура принимает параметры, значения которых сильно меняются между выполнениями, что приводит к созданию каждый раз новых планов выполнения. Этот параметр используется редко и замедляет выполнение хранимых процедур, так как они должны перекомпилироваться при каждом запуске.
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log;
go

create procedure dbo.p_user_activity_log
(
  @user_name varchar(10)
)
with recompile
as
select * from dbo.user_activity_log
  where user_name = @user_name;
go

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

Начиная с версии SQL Server 2005, мы можем указать перекомпиляцию не для всей процедуры, а только для конкретных инструкций. Перекомпиляция на уровне инструкции дает выигрыш в производительности, поскольку в большинстве случаев перекомпиляция небольшого числа инструкций и связанных с этим потерь занимает меньше ресурсов в плане использования процессорного времени и затрат на блокировки. Этих потерь, таким образом, удается избежать для остальных инструкций пакета, которые в перекомпиляции не нуждаются. Пользуйтесь этим параметром в том случае, если нетипичные или временные значения встречаются только в части запросов, входящих в хранимую процедуру.
CREATE PROC proc_name 
(
  input_parameters
) 
AS 
Query1
Query2 OPTION(RECOMPILE)
Query3
Query4
Query5 OPTION(RECOMPILE)

GO

В примере выше мы вызываем перекомпиляцию не у всей процедуры, а только для запросов 2 и 5. Таким образом, планы запросов 1,3 и 4 не будут пересоздаваться, а будут браться из кэша.

Использование нескольких процедур

Мы можем сами по входящим параметрам анализировать, какой сценарий нам нужен. Например, для данных в таблице dbo.user_activity_log может быть два сценария: для пользователя User01, т.к. по нему 99% данных в таблице и для всех остальных, тогда наша процедура будет выглядеть примерно так:
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log;
go

create procedure dbo.p_user_activity_log
(
  @user_name varchar(10)
)
as
  if @user_name = 'User01'
    exec dbo.p_user_activity_log_1 @user_name
  else 
    exec dbo.p_user_activity_log_2 @user_name
go

Т.е. у нас процедура dbo.p_user_activity_log становится оберткой для анализа входного параметра и дальнейшего вызова новых процедур. Таким образом, у нас будет для 2-х процедур (dbo.p_user_activity_log_1 и dbo.p_user_activity_log_2) два разных плана в кэше, которые будут оптимальными для наших входных параметров. Достаточно универсальный метод для всех версий SQL Server, но, к сожалению, не всегда легко реализуем, если мы не можем предсказать, как наши данные в исходных таблицах будут меняться с течением времени или сценариев не два, как в моём примере, а несколько десятков.

Динамические запросы

Ещё одна хитрость, которой мы можем воспользоваться - это обернуть наш "непредсказуемый" запрос в виде динамического SQL:
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
declare @str nvarchar(max);

set @str = 'select * from dbo.user_activity_log '
         + 'where user_name = ' + quotename( @user_name, '''' );

exec sp_executesql @str;
go

Хитрость заключается в том, что при каждом вызове нашей процедуры в кэш будет помещаться план для конкретного запроса, с конкретным параметром(или браться из кэша, если такой запрос там уже есть):
set nocount on;

--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Вызов процедуры с параметром @user_name = 'User01'
exec dbo.p_user_activity_log @user_name = 'User01';

--Вызов процедуры с параметром @user_name = 'User02'
exec dbo.p_user_activity_log @user_name = 'User02';

--Три вызова процедуры с параметром @user_name = 'User03'
exec dbo.p_user_activity_log @user_name = 'User03';
exec dbo.p_user_activity_log @user_name = 'User03';
exec dbo.p_user_activity_log @user_name = 'User03';

select cacheobjtype
     , objtype
     , text
     , usecounts
     , query_plan 
  from sys.dm_exec_cached_plans cp
    outer apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
    outer apply sys.dm_exec_sql_text ( cp.plan_handle ) st
  where st.text like 'select * from dbo.user_activity_log%'; 

В примере я вызвал нашу процедуру 5 раз, по разу с параметром @user_name = 'User01' и @user_name = 'User02' и три раза с параметром @user_name = 'User03', теперь посмотрим, что у нас в кэше:


Рис 10. Кэшированные планы запросов

В кэше наши запросы фигурируют, как Ad hoc Query-произвольный запрос. При этом в поле usecounts видно, что для параметра @user_name = 'User03' было три случая использования данного объекта кэша с момента его создания, т.е. при повторном запуске процедуры, план брался из кэша. При этом, если посмотреть на графический план для каждого запроса, то мы увидим, что для параметра @user_name = 'User01' в кэше план сканирования таблицы, а для параметров @user_name = 'User02' и @user_name = 'User03' используется поиск по некластерному индексу.

Опция OPTIMIZE FOR

Подсказка, которая появилась в SQL Server 2005, позволяет уменьшить "головную боль" от того, что оптимизатор запросов автоматически использует значения параметра, переданного в хранимую процедуру для определения плана исполнения запроса. Подсказка OPTIMIZE FOR может использоваться для отмены определения параметров по умолчанию в оптимизаторе или при создании структуры плана.
Предположим, что в нашем случае, как правило, процедура dbo.p_user_activity_log будет вызываться с параметром @user_name = 'User01' и для того, чтобы избежать попадания в кэш плана для других входных параметров, который может значительно понизить производительность нашей процедуры мы создадим её с подсказкой OPTIMIZE FOR:
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log 
  where user_name = @user_name
option ( optimize for ( @user_name = 'User01' ) );
go

А теперь вызовем эту процедуру с параметром @user_name = 'User02' и посмотрим, какой план попадёт в кэш:
set nocount on;

--Для чистоты эксперимента, удаляем все элементы из кэша планов
dbcc freeproccache;

--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';

--Просмотр плана для нашей процедуры, который был помещён в кэш
select cacheobjtype, query_plan 
  from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_query_plan ( cp.plan_handle ) qp
  where cp.objtype  = 'Proc'
    and qp.objectid = object_id ( N'dbo.p_user_activity_log', N'P' ); 

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

Но, как я сказал выше, подсказка optimize for появилась только в SQL Server 2005, что же делать, если у вас более ранняя версия? Для того чтобы гарантирует в SQL Server 2000 (да и SQL Server 7.0) выбор определенного плана исполнения запроса есть одна хитрость, которая позволяет предотвращать прослушивание параметров и гарантировать лучшей стабильности. Мы используем локальный параметр внутри процедуры, который позволит в момент компиляции выбрать план, который мы хотим (для того параметра, который нам нужен):
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10) 
 ,  @fake_user_name varchar(10) = 'User01'
)
as
--Переопределяем фальшивый параметр для обмана оптимизатора входным параметром
set @fake_user_name = @user_name;

select * from dbo.user_activity_log 
  where user_name = @fake_user_name;
go

Опять смотри план, который попадает в кэш, при вызове процедуры с параметром @user_name = 'User02'
set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;

--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';



Рис 11. Сведения о профиле для инструкции

Таким, достаточно нехитрым методом мы добились поведения аналогичного при подсказке optimize for. Более подробно об этом методе можно прочитать в статье: Методика, которая гарантирует в SQL Server 2000 выбор определенного плана исполнения запроса.

Опция OPTIMIZE FOR UNKNOWN

Выше мы рассмотрели, как можно благодаря подсказке optimize for добиться попадания в кэш гарантированного плана запроса для определённого параметра (-ов), но этот опция не сможет нам помочь, когда наши данные регулярно меняются и мы не можем передать в качестве подсказки конкретное значение. В качестве решения этой проблемы мы можем использовать подсказку, которая появилась в SQL Server 2008 - OPTIMIZE FOR UNKNOWN. Эта подсказка предписывает оптимизатору запросов использовать статистические данные вместо начальных значений для всех локальных переменных, включая параметры, созданные с принудительной параметризацией.
Указав эту опцию в нашей процедуре, мы можем быть уверены, что план запроса будет построен независимо от того, какой параметр будет передан в момент компиляции. В любом случае оптимизатор использует статистику для построения плана, а не наши входные данные.
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log 
  where user_name = @user_name
option ( optimize for ( @user_name unknown ) );
go

А теперь посмотрим, какой план построит оптимизатор для параметра @user_name = 'User02'
set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';



Рис 12. Сведения о профиле для инструкции

Оптимизатор использовал сканирование таблицы, при этом он апеллировал статистическими данными и в поле EstimateRows (Предполагаемое количество строк вывода от данного оператора) значение 112 не случайно. Вернёмся к статистике, по нашему индексу:
dbcc show_statistics ( N'dbo.user_activity_log', idx_user_name );



Рис 13. Статистические данные для индекса idx_user_name

По статистике видно, что общее число строк (Rows) = 1008, а плотность (All Density) «1/различающиеся значения» = 0,111111. Исходя из этих данных оптимизатор и получил число ожидаемых строк = 112 ( Rows * All Density ).

Действительно, в некоторых случаях эта подсказка может оказаться идеальным решение производительности некоторый процедур, но, что делать, если в вашей версии нет подсказки optimize for unknown (повторюсь, появился этот хинт в SQL Server 2008)? Обходное решение есть и для этого случая. Мы опять будем использовать локальные переменные в теле процедуры, чтобы обмануть оптимизатор в момент компиляции плана запроса:
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10) 
)
as
declare @fake_user_name varchar(10);

--Переопределяем фальшивый параметр для обмана оптимизатора входным параметром
set @fake_user_name = @user_name;

select * from dbo.user_activity_log 
  where user_name = @fake_user_name;
go

Ниже информация о профиле для параметра @user_name = 'User02'
set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';



Рис 14. Сведения о профиле для инструкции

Таким образом мы можем эмулировать подсказку optimize for unknown на более ранних версиях.

Принуждаем использовать конкретный индекс

Ещё один из вариантов повлиять на оптимизатор - это "заставить" его всегда использовать тот или иной индекс с помощью ряда подсказок.

INDEX
Синтаксис INDEX(index_value) указывает имя или идентификатор одного или нескольких индексов, используемых при обработке инструкции оптимизатором запросов. Альтернативный синтаксис INDEX = (index_value) допустим только для единичного значения индекса.
Если имеется кластеризованный индекс, аргумент INDEX(0) приводит к просмотру кластеризованного индекса, а INDEX(1) — к просмотру или поиску по кластеризованному индексу. Если кластеризованного индекса нет, аргумент INDEX(0) приводит к просмотру таблицы, а INDEX(1) интерпретируется как ошибка.

FORCESEEK
Указывает, что в качестве пути доступа к данным таблиц или представлений оптимизатор запросов использует только операцию поиска в индексе. Начиная с SQL Server 2008 R2 с пакетом обновления 1 (SP1) могут быть указаны также параметры индекса. Подсказка FORCESEEK с параметрами индекса аналогична использованию FORCESEEK с подсказкой INDEX. Но более эффективного контроля над путем доступа, который использует оптимизатор запросов, можно добиться указанием индекса для поиска и столбцов индекса, которые предполагается использовать в операции поиска.

FORCESCAN
Эта подсказка, впервые появившаяся в SQL Server 2008 R2 с пакетом обновления 1 (SP1), указывает оптимизатору запросов использовать для доступа к указанной таблице или представлению только операцию просмотра индекса. Подсказка FORCESCAN может оказаться полезной в тех запросах, где оптимизатор недооценивает число обрабатываемых строк и выбирает операцию поиска, а не просмотра. В этом случае объем памяти, выделенный для данной операции, будет слишком мал, и это может повлиять на производительность запроса.

В нашем примере мы знаем, что в большинстве случаев для нас выгодно использовать полное сканирование таблицы, поэтому мы можем смело ставить подсказку:
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log with ( index (0) )
  where user_name = @user_name;
go

Хотелось бы обратить внимание, что если ваши данные не статичны, то такой подход может со временем сказаться на производительности, т.к. через какое-то время полное сканирование может стать причиной замедления работы и вам придётся пересматривать целесообразность подсказки по использованию конкретного индекса.

Форсирование плана

Подсказка в запросе USE PLAN (появилась в SQL Server 2005) применяется, чтобы заставить оптимизатор запросов использовать для формирования запроса определенный план. Подсказка USE PLAN работает посредством приема плана запроса, который необходимо использовать в формате XML в качестве аргумента. Подсказку USE PLAN можно использовать для запросов, планы которых замедляют их выполнение, но для которых существует лучший план.

Всё, что нам нужно, это получить "идеальный" (нужный нам) план для определённых условий и указать его в качестве подсказки в теле нашей процедуры
--Выполним наш запрос с параметром user_name = 'User01' (для получения нужного нам плана)
select * from dbo.user_activity_log 
  where user_name = 'User01';
go

--Вытащим нужный нам план из кэша
declare @xml_showplan nvarchar(max);
select @xml_showplan = query_plan
  from sys.dm_exec_query_stats as qs 
    cross apply sys.dm_exec_sql_text( qs.sql_handle ) as st
    cross apply sys.dm_exec_text_query_plan( qs.plan_handle, default, default ) as qp
  where st.text like N'select * from dbo.user_activity_log%';

select @xml_showplan;
go
Теперь, когда у нас есть план запроса в виде XML мы можем использовать его в качестве подсказки
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log
  where user_name = @user_name
  option ( use plan N'План запроса в виде XML' );
go
Метод достаточно топорный, но зато мы можем быть уверены, что план запроса будет именно тот, который нам нужен. Но это решение подходит нам, когда мы можем вносить изменения в наш код. При этом если с течением времени план становится неактуальным, нам необходимо опять вносить изменения в текст процедуры. Для того чтобы мы могли более гибко управлять форсированием плана, в нашем распоряжении есть процедура sp_create_plan_guide. Кроме того, эта процедура может использоваться, когда нельзя или не нужно напрямую менять текст запроса. Руководства планов полезны, когда небольшое подмножество запросов в приложении базы данных стороннего разработчика выполняются не так, как ожидается.
--Выполним наш запрос с параметром user_name = 'User01' (для получения нужного нам плана)
go
select * from dbo.user_activity_log 
  where user_name = 'User01';
go

--Вытащим нужный нам план из кэша
declare @xml_showplan nvarchar(max);
select @xml_showplan = query_plan
  from sys.dm_exec_query_stats as qs 
    cross apply sys.dm_exec_sql_text( qs.sql_handle ) as st
    cross apply sys.dm_exec_text_query_plan( qs.plan_handle, default, default ) as qp
  where st.text like N'select * from dbo.user_activity_log%';

--Создаем структуру плана 
exec sp_create_plan_guide 
      @name = N'guide_for_p_user_activity_log'
    , @stmt = N'select * from dbo.user_activity_log
                  where user_name = @user_name;'
    , @type = N'OBJECT'
    , @module_or_batch = N'dbo.p_user_activity_log'
    , @params = null
    , @hints = @xml_showplan;
go

--Смотрим, что для нашей процедуры создана структура плана
select * from sys.plan_guides
  where scope_object_id = object_id( N'dbo.p_user_activity_log', N'P' );
go
Наша процедура при этом не содержит никаких подсказок:
if object_id ( N'dbo.p_user_activity_log', N'P' ) is not null
  drop procedure dbo.p_user_activity_log
go

create procedure dbo.p_user_activity_log
(
    @user_name varchar(10)
)
as
select * from dbo.user_activity_log
  where user_name = @user_name;
go
Теперь достаточно вызвать нашу процедуру с уже привычным параметром @user_name = 'User02', чтобы убедиться, что план для процедуры выбран нужный нам
set nocount on;

--Для чистоты эксперимента, очистим весь процедурный кэш
dbcc freeproccache;

--Выведем общую статистику по нашему запросу
set statistics profile on;
--Вызов процедуры с параметром @user_name = 'User02'
--для этого параметра в обычном случае оптимизатор выберет поиск по индексу
exec dbo.p_user_activity_log @user_name = 'User02';



Рис 15. Сведения о профиле для инструкции

Оптимизатор выбрал сканирование всей таблицы, что нам и было нужно.
Мы можем вносить изменение в выбор плана оптимизатором для нашей процедуры не меняя код самой процедуры. При этом хочу обратить ваше внимание, что удалить или изменить процедуру нельзя, пока вы не удалите для неё все структуры планов, для этого необходимо запустить хранимую процедуру sp_control_plan_guide.
exec sp_control_plan_guide N'drop', N'guide_for_p_user_activity_log'


Вместо заключения

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

Полезные ссылки по текущей теме

Tags: , , , , ,

SQL Server

Комментарии (2) -

Andraptor
Andraptor Ukraine
22.02.2012 22:35:08 #

Хочу добавить небольшое дополнение по поводу использования динамических запросов.
В случае выполнения sp_executesql с параметрами, мы опять же утыкаемся в прослушивание параметров. И дальнейшие вызовы с любыми значениями параметров будут использовать первый сохраненный план.
Пример:
DBCC FREEPROCCACHE
--сканирование кластерного индекса
EXEC sp_executesql
N'select * from AdventureWorks.Production.WorkOrder
where productid = @prodid'
,N'@prodid int'
,@prodid=722
--поиск по некластерному индексу
EXEC sp_executesql
N'select * from AdventureWorks.Production.WorkOrder
where productid = @prodid'
,N'@prodid int'
,@prodid=725

SELECT st.text,cp.usecounts,cp.objtype,qp.*
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
st.text  LIKE '%Production.WorkOrder%'
AND st.text  NOT LIKE '%sys.dm_exec_cached_plans %'
GO


Поэтому, если мы хотим для каждого параметра хранимки использовать собственный план, то нужно использовать EXECUTE или sp_executesql без параметров (как в статье).

Reply

Александр Булкин
Александр Булкин Kazakhstan
24.07.2012 12:22:18 #

У меня был интересный случай с планом выполнения. Один раз после обновления статистики в базе данных ,одна из основных процедур полностью перестала работать, ее более сложные аналоги работали прекрасно. Что мы только не побывали и чистили процедурный кэш, рекомпилировали, обновляли статистику. Целый день боролись с планом данной процедуры, простаивал определенный функционал нашей компании. Все попытки не увенчались успехом, вечером мы решили отключили пользователей и полностью перестроили все индексы, после этого все заработало.

Статья отличная, спасибо большое!

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading