Устранение неполадок при перекомпиляции хранимых процедур

Переводы статьи Переводы статьи
Код статьи: 243586 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

Аннотация

Эта статья посвящена определенному типу проблем с производительностью, которые могут возникать у приложений при работе с Microsoft SQL Server: проблемах при перекомпиляции хранимых процедур во время выполнения. В случае работы над устранением проблемы с производительностью, которая может быть вызвана и какой-то другой причиной, ознакомьтесь со следующей статьей базы знаний Майкрософт перед тем, как приступать к работе:

224587 ИНСТРУКЦИИ: Устранение проблем с производительностью приложений при работе с SQL Server
В данной статье предполагается, что вышеупомянутая статья использована для уточнения масштаба проблемы и что описанные в ней записи событий и столбцов данных в профилировщике SQL Server сохранены.

Дополнительная информация

Когда пользователь исполняет хранимую процедуру, если она еще не доступна в кэше, SQL Server загружает процедуру и компилирует план запроса. Скомпилированный план сохраняется в кэше и используется повторно при последующих вызовах хранимой процедуры, пока не происходит какого-либо действия, делающего план недействительным и приводящего к перекомпиляции. Перекомпиляцию плана хранимой процедуры могут вызвать следующие действия.
  • Использование предложения WITH RECOMPILE в операторах CREATE PROCEDURE или EXECUTE.
  • Изменения схемы в любых объектах, на которые имеются ссылки, включая добавление или устранение ограничений, правил либо значений по умолчанию.
  • Применение sp_recompile для таблицы, на которую ссылается процедура.
  • Восстановление базы данных, содержащей процедуру или любой из объектов, на которые ссылается процедура (в случае выполнения операций, затрагивающих несколько баз данных)
  • Интенсивная деятельность сервера, вызывающая удаление плана из кэша вместе с прочим устаревшим содержимым.
Все эти причины для перекомпиляции хранимой процедуры существовали и в предыдущих версиях, вызывая перекомпиляцию плана перед началом исполнения процедуры. В SQL Server 7.0 появилось новое поведение, могущее вызвать перекомпиляцию хранимой процедуры в ходе исполнения. Это новое поведение гарантирует наличие у оптимизатора лучшего плана из возможных для каждого конкретного оператора внутри процедуры. Следующие события могут вызвать перекомпиляцию хранимой процедуры во время выполнения следующих действий.
  • Изменение достаточного процента данных в таблице, на которую ссылается хранимая процедура.
  • Процедура чередует операции языка определения данных DDL и языка обработки данных DML.
  • Процедура выполняет определенные операции со временными таблицами.
О каждой из этих причин рассказано более подробно ниже.

В некоторых случаях затраты ресурсов на перекомпиляцию хранимой процедуры превосходят выгоды от нее, особенно для крупных процедур. Очень важно отметить, что в случае начала перекомпиляции пакет или процедура перекомпилируются целиком. Это означает, что понижение производительности прямо пропорционально размеру процедуры или пакета. Дополнительные сведения по этому вопросу приведены в разделе "Полезные советы для Transact-SQL" в электронной документации по SQL Server.


Приведенные ниже сведения посвящены определению причин перекомпиляции хранимых процедур во время выполнения и методам предотвращения этого.

Рекомендация

При исполнении процедуры лучше всего квалифицировать имя владельца хранимой процедуры. Это повышает ясность и упрощает повторное использовование существующего плана исполнения текущим пользователем. Например, если пользователь, не являющийся владельцем базы данных (dbo), исполняет принадлежащую этому владельцу хранимую процедуру (именуемую myProc в данном примере) в базе данных pubs, используйте следующий оператор:
exec dbo.myProc
				
Вместо этого:
exec myProc
				
С точки зрения программирования и обслуживания такой прием устраняет возможную путаницу с другими версиями процедуры от различных владельцев, а также она позволяет SQL Server получать доступ к плану исполнения конкретной процедуры более прямо.

Если имя владельца не квалифицировано, SQL Server входит в код компиляции и получает блокировку COMPILE на процедуру. Однако рано или поздно он определяет, что новый план не требуется (в случае отсутствия других причин) и не перекомпилирует план на этом этапе из-за отсутствия квалификации. Тем не менее, дополнительное действие получения блокировки COMPILE на процедуре может вызвать конкуренцию блокировок в серьезных случаях. Ознакомьтесь со статьей Q263889 ИНФО: "Блокировка SQL из-за блокировок [[COMPILE]]" для получения дополнительных сведений.

Если владелец квалифицирует вызов процедуры посредством owner.procedure, в получении блокировки COMPILE нет необходимости, так что вероятность конкуренции уменьшается.

Определение и разрешение проблем

Если это еще не сделано, ознакомьтесь со следующей статьей базы знаний Майкрософт для получения сведений, касающихся записи данных профилировщика с целью анализа производительности системы.
224587 ИНСТРУКЦИИ: Устранение проблем с производительностью приложений при работе с SQL Server

Просмотр данных профилировщика

В профилировщике SQL Server имеется событие SP:Recompile, которое можно использовать для отслеживания числа происходящих перекомпиляций. Событие SP:Recompile происходит при перекомпиляции хранимой процедуры в ходе исполнения.
  • Сгруппировать трассу профилировщика по классу события

    1. В меню Файл выберите команду Свойства.
    2. На вкладке Столбцы данных используте кнопку "Вверх", чтобы переместить Класс события и Текст под заголовок Группы, причем Класс события должен быть первым. Используйте кнопку "Вниз", чтобы удалить все прочие столбцы под заголовком Группы.
    3. Нажмите кнопку ОК.
    Проверьте число событий SP:Recompile.

    Группу SP:Recompile можно развернуть для просмотра сведений об отдельных случаях. Столбец Текст события указывает имя перекомпилированной хранимой процедуры. В случае, если перекомпиляция вызывалась несколькими процедурами, они упорядочиваются по числу случаев. При наличии большого числа событий SP:Recompile и высокой загрузке ЦП сосредоточьтесь на обработке процедур с наибольшим числом перекомпиляций. Запишите идентификатор системного процесса (SPID) и время запуска события SP:Recompile для одного из экземпляров какой-либо хранимой процедуры и выполните нижеописанные действия.

    Если событий SP:Recompile не наблюдается, но проблема с производительностью остается, ознакомьтесь со следующей статьей базы знаний Майкрософт:
    224587 ИНСТРУКЦИИ: Устранение проблем с производительностью приложений при работе с SQL Server
  • Определите оператор, вызвавший событие перекомпиляции

    1. В меню Файл выберите команду Свойства.
    2. На вкладке Столбцы данных, используйте кнопку "Вниз", чтобы удалить все прочие столбцы под заголовком Группы.
    3. На вкладке События удалите все события, кроме SP:Starting, SP:StmtStarting, SP:Recompile и SP:Completed. Если событие SP:StmtStarting не было записано, вместо него можно использовать SP:StmtCompleted, но включать их оба не следует, поскольку это удваивает объем информации, которую необходимо просмотреть.
    4. Если определен конкретный экземпляр перекомпиляции хранимой процедуры, который необходимо изучить, объем просматриваемых данных можно ограничить до конкретного SPID и времени события, используя вкладку Фильтры.
    5. Нажмите кнопку ОК.

    Событие SP:Recompile будет порождено прямо после события SP:StmtStarted оператора хранимой процедуры, вызвавшего перекомпиляцию. После завершения события перекомпиляции можно будет увидеть повторение события SP:StmtStarted, что указывает на использование свежесозданного плана при исполнении оператора.

    Рассмотрим следующий пример:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    В случае выполнения этого кода в анализаторе запросов при просмотре вышеописанных событий в трассе профилировщика можно будет увидеть следующее:

    Свернуть эту таблицуРазвернуть эту таблицу
    Класс событияТекст
    SP:StartingRecompProc
    SP:StmtStartingcreate table #t (a int)
    SP:StmtStartingselect * from #t
    SP:RecompileRecompProc
    SP:StmtStartingselect * from #t
    SP:CompletedRecompProc


    Из этого можно сразу заключить, что перекомпиляцию вызвал следующий оператор:
    select * from #t
    						
    , поскольку он появляется как до, так и после события SP:Recompile.

    Если было записано только событие SP:StmtCompleted, но не событие SP:StmtStarting, событие SP:Recompile будет показано прямо перед вызвавшим его оператором:

    Свернуть эту таблицуРазвернуть эту таблицу
    Класс событияТекст
    SP:StartingRecompProc
    SP:RecompileRecompProc
    SP:StmtCompletedselect * from #t
    SP:CompletedRecompProc


    Можно заметить, что событие SP:Recompile порождается перед событием SP:StmtCompleted для оператора "select * from #t", который вызвал перекомпиляцию. Это вполне объяснимо, поскольку оператор может быть завершен лишь после завершения создания нового плана запроса для перекомпиляции. Все прочие примеры в этой статье используют событие SP:StmtStarting. Если было записано только событие SP:StmtCompleted, просто просмотрите оператор после SP:Recompile, как показано выше.

    Обратите внимание на то, что при многократном исполнении хранимой процедуры SQL Server повторно использует существующий для нее план. Событие перекомпиляции можно будет увидеть только для первого исполнения процедуры, если только она не сбрасывается и не воссоздается заново каждый раз. О причинах перекомпиляции в данном конкретном случае рассказывается в разделе данной статьи "Перекомпиляции, вызванные чередованием операций языка определения данных DDL и языка обработки данных DML"; здесь просто приведен пример, иллюстрирующий простой способ определения оператора, вызывающего перекомпиляцию.

Перекомпиляции, вызванные изменениями строк

При изменении достаточного процента данных в таблице, на которую ссылается хранимая процедура, с момента создания первоначального плана запроса SQL Server перекомпилирует хранимую процедуру, чтобы обеспечить наличие плана, основанного на последних статистических данных. В качестве примера можно рассмотреть следующую хранимую процедуру
drop procedure RowModifications 
go
create procedure RowModifications as
-- предполагается, что существует таблица SomeTable с тем же определением, что и #t, 
-- и в ней более 1000 строк
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
				
При втором исполнении процедуры RowModifications в профилировщике будут показаны следующие события.:

Свернуть эту таблицуРазвернуть эту таблицу
Класс событияТекст
SP:StartingRowModifications
SP:StmtStartingcreate table #t (a int, b char(10))
SP:StmtStartingselect * from #t
SP:StmtStartinginsert #t select * from SomeTable
SP:StmtStartingselect count(*) from #t where a = 37
SP:RecompileRowModifications
Auto-UpdateStatsa
SP:StmtStartingselect count(*) from #t where a = 37
SP:CompletedRowModifications

Примечание. При первом выполнении также будет показано событие SP:Recompile для оператора "select * from #t". О причине рекомпиляции для данного конкретного случая рассказывается в разделе "Перекомпиляции, вызванные чередованием операций языка определения данных DDL и языка обработки данных DML" данной статьи. В данном примере обратите основное внимание на событие SP:Recompile, показанное выше, поскольку оно происходит при каждом исполнении процедуры.

В данном примере оператор "select count(*) from #t where a = 37" вызывает перекомпиляцию процедуры в силу изменения числа строк со времени создания таблицы. Присутствие события Auto-UpdateStats подтверждает, что перекомпиляция была вызвана изменениями в строках. Столбец Текст указывает столбец, для которого были изменены показатели.

При создании таблицы #t число строк равнялось нулю. План для первоначального "select * from #t" разрабатывался с этим числом строк, равно как и план для запроса "select count (*)". Однако перед исполнением "select count(*)" в таблицу #t было добавлено 1000 новых строк. Поскольку изменился достаточный объем данных, оптимизатор перекомпилирует процедуру, чтобы обеспечить выбор наиболее эффективного плана для данного оператора. Перекомпиляция будет происходить при каждом исполнении хранимой процедуры, поскольку вставка 1000 строк всегда будет считаться достаточной причиной для перекомпиляции.

Алгоритм, используемый SQL Server для определения необходимости перекомпиляции плана, - это тот же алгоритм, что используется для автообновления статистики, как описано в следующей статье базы знаний Майкрософт:
195565 ИНФО: Как работает автоматическая статистика в SQL Server 7.0 и SQL Server 2000 (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
(эта ссылка может указывать на материал полностью или частично на английском языке) В вышеуказанном примере хранимая процедура достаточно мала, чтобы перекомпиляция не оказала заметного воздействия на производительность. Однако в случая наличия крупной хранимой процедуры, выполняющей подобные действия, приводящие к многочисленным перекомпиляциям, возможно падение производительности.

Существуют следующие методы противодействия перекомпиляциям, вызванным изменениями строк.
  • Исполнение оператора с использованием sp_executesql.
    Этот метод предпочтителен. Операторы, исполненные с использованием хранимой процедуры sp_executesql, не компилируются как часть плана хранимой процедуры. Следовательно, при исполнении оператора SQL Server сможет использовать существующий в кэше план для оператора или создать новый во время выполнения. В обоих случаях план для вызова хранимой процедуры остается незатронутым и не потребует перекомпиляции.

    Оператор EXECUTE возымеет тот же эффект, однако использовать его не рекомендуется. Использование оператора EXECUTE не так эффективно, как использование sp_executesql, поскольку он не допускает параметризации запроса.

    Приведенная выше процедура RowModifications может быть написана для использования sp_executesql:

    drop procedure RowModifications2 
    go
    create procedure RowModifications2 as
    set nocount on
    -- предполагается, что существует таблица SomeTable с тем же определением, что и #t, 
    -- и в ней более 1000 строк
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2
    						

    Для второго исполнения процедуры RowModifications2 в профилировщике будут показаны следующие события:

    Свернуть эту таблицуРазвернуть эту таблицу
    Класс событияТекст
    SP:StartingRowModifications2
    SP:StmtStartingcreate table #t (a int, b char(10))
    SP:StmtStartingselect * from #t
    SP:StmtStartinginsert #t select * from SomeTable
    SP:StmtStartingexec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP:Starting
    SP:StmtStartingselect count(*) from #t where a = @a
    Auto-UpdateStatsa
    SP:StmtStartingselect count(*) from #t where a = @a
    SP:Completed
    SP:CompletedRowModifications2


    Обратите внимание на отсутствие событий SP:Recompile для процедуры RowModifications2. При этом имеются законченные события от SP:Starting до SP:Completed для контекста вызова sp_executesql и события Auto-UpdateStats для столбца a. Однако, поскольку данный вызов находится вне контекста хранимой процедуры, процедура RowModifications2 в данном случае не нуждается в перекомпиляции.

    Дополнительные сведения о использовании хранимой процедуры sp_executesql можно найти в разделах "sp_executesql (T-SQL)" и "Использование sp_executesql" электронной документации по SQL Server.
  • Использование подпрограмм для исполнения операторов, вызывающих перекомпиляции.
    В этом случае оператор может по-прежнему вызывать перекомпиляцию, но вместо перекомпиляции крупной хранимой процедуры будет перекомпилироваться только небольшая подпрограмма.
  • Использование параметра KEEP PLAN.
    У временных таблиц имеются специальные правила по отношению к перекомпиляциям, которые в некоторых случаях могут быть более жесткими, чем алгоритм перекомпиляции по умолчанию. Параметр KEEP PLAN можно использовать, чтобы ослабить их до уровня алгоритма по умолчанию. Дополнительные сведения приведены в разделе данной статьи "Предотвращение перекомпиляции путем использования параметра KEEP PLAN".
Примечание. Процедура RowModifications является очень упрощенным примером процедуры, перекомпилируемой из-за изменений строк. Ознакомьтесь с приведенными ниже оговорками, касающимися данного примера:

  • Хотя в примере используется временная таблица, ситуация аналогична и для хранимых процедур, ссылающихся на постоянные таблицы. В случае изменения с момента создания плана запроса достаточного объема данных в таблице, на которую ссылается хранимая процедура, хранимая процедура будет перекомпилирована. Различия в оценцке временных таблиц с целью перекомпиляции описаны в разделе данной статьи "Предотвращение перекомпиляции путем использования параметра KEEP PLAN".
  • Первые исполнения двух вышеупомянутых процедур также вызовут перекомпиляцию при первом выборе из временной таблицы #t. О причинах этой перекомпиляции рассказывается в разделе "Перекомпиляции, вызванные чередованием операций языка определения данных DDL и языка обработки данных DML" данной статьи.
  • В примере был использован оператор "select count(*) from #t" вместо простого оператора "select * from #t". Для предотвращения излишних перекомпиляций SQL Server не проводит перекомпиляций "тривиальных планов" (таких как выбор select * из таблицы) по причине изменений строк.

Перекомпиляции, вызванные чередованием операций языка определения данных DDL и языка обработки данных DML

Если внутри процедуры или пакета выполняются операции DDL, то процедура или пакет перекомпилируется при встрече с первой последующей операцией DML, затрагивающей таблицу, задействованную в DDL.

Рассмотрим следующий пример хранимой процедуры:
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table t1 (a int)
-- DML
select * from t1
-- DDL
create index idx_t1 on t1(a)
-- DML
select * from t1
-- DDL
create table t2 (a int)
-- DML
select * from t2
go
exec Interleave
				
В случае выполнения этого кода в анализаторе запросов при просмотре вышеописанных событий в трассе профилировщика можно будет увидеть следующее:

Свернуть эту таблицуРазвернуть эту таблицу
Класс событияТекст
SP:StartingInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t2
SP:RecompileInterleave
SP:StmtStartingselect * from t2
SP:CompletedInterleave


В данном случае в ходе исполнения хранимая процедрура перекомпилируется три раза. Чтобы понять, почему это происходит, рассмотрим выработку оптимизатором плана для этой хранимой процедуры.
  1. В ходе первоначальной компиляции процедуры таблиц t1 и t2 не существовало. Следовательно, невозможно создать план для запросов, ссылающихся на эти таблицы. Их необходимо создавать во время исполнения.
  2. Первым действием при первом исполнении процедуры является создание таблицы t1 Следующим действием является выбор из таблицы t1, для которого здесь нет плана. Следовательно, на этом этапе процедура перекомпилируется, чтобы разработать план для оператора SELECT. План создается для текущего выбора из t1, а также для выбора из t1 после создания индекса. Невозможно создать план для выбора из t2, поскольку t2 все еще не существует.
  3. Следующее действие - создание индекса на t1. Вслед за этим на t1 выполняется очередной выбор, для которого первая перекомпиляция создала план. Однако, поскольку схема t1 была изменена с момента создания плана, процедура должна быть перекомпилирована снова, чтобы создать новый план для выбора из t1. И все еще невозможно создать план для выбора из t, поскольку t2 по-прежнему не существует.
  4. Далее создается таблица t2, и исполняется выбор из t2. Поскольку плана для оператора не существует, процедура перекомпилируется в последний раз.
Эти перекомпиляции происходят при каждом исполнении хранимой процедуры. Чтобы сократить количество перекомпиляций, измените процедуру так, чтобы она сперва выполняла все операции DDL, а затем операции DML, как показано ниже:
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- Сначала все DDL
create table t1 (a int)
create index idx_t1 on t1(a)
create table t2 (a int)
-- Потом DML 
select * from t1
select * from t1
select * from t2
go
exec NoInterleave 
exec NoInterleave 
				
Первое исполнение процедуры NoInterleave приведет к показу следующих событий в профилировщике:

Свернуть эту таблицуРазвернуть эту таблицу
Класс событияТекст
SP:StartingNoInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t1
SP:RecompileNoInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t2
SP:CompletedNoInterleave


В данном случае сначала выполняются все операторы DDL. Оптимизатор компилирует эту процедуру следующим образом.
  1. В ходе первоначальной компиляции процедуры таблиц t1 и t2 не существовало. Следовательно, невозможно создать план для запросов, ссылающихся на эти таблицы. Их необходимо создавать во время исполнения.
  2. Первыми действиями, выполняемыми процедурой, являются операции DDL, создающие таблицы t1 и t2, а также индекс для t1.
  3. Следующим действием является первый выбор из t1. Поскольку плана для оператора SELECT не существует, процедура перекомпилируется. Поскольку все объекты существуют, планы создаются для всех операторов SELECT в процедуре одновременно.
  4. Остальная часть процедуры исполняется с использованием созданных планов. Поскольку в объектах, на которые даются ссылки, отсутствуют изменения, в дальнейшей перекомпиляции процедуры нет нужды.
Примечание. Второе и последующие исполнения используют существующие план запроса и кэш безо всяких перекомпиляций. Процедуры, которые создают, изменяют или удаляют таблицы, следут изменить, чтобы обеспечить обнаружение всех операторов DDL в начале процедуры.

Перекомпиляции, вызванные определенными операциями с временными таблицами

Использование временных таблиц в хранимых процедурах может вызвать перекомпиляцию хранимой процедуры при каждом исполнении.

Чтобы избежать этого, измените хранимую процедуру так, чтобы она соответствовала следующим требованиям.
  • Все операторы, содержащие имя временной таблицы, указывают на временную таблицу, созданную в той же хранимой процедуре, а не в вызывающей или вызываемой хранимой процедуре, либо строке, исполняемой с использованием оператора EXECUTE или хранимой процедуры sp_executesql.
  • Все операторы, содержащие имя временной таблицы, синтаксически появляются в хранимой процедуре или триггере после временной таблицы.
  • Нет операторов DECLARE CURSOR, чьи операторы SELECT ссылаются на временную таблицу.
  • Все операторы, содержащие имя любой временной таблицы, предшествуют любому оператору DROP TABLE, ссылающемуся на временную таблицу.

    Операторы DROP TABLE не нужны временным таблицам, созданным в хранимой процедуре. Таблицы автоматически удаляются по завершении процедуры.
  • Операторы, создающие временные таблицы, такие как (CREATE TABLE или SELECT... INTO), появляются в операторах контроля потока, таких как как IF... ELSE или WHILE.

Предотвращение перекомпиляции путем использования параметра KEEP PLAN

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

Рассмотрим следующий пример:
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Сделаем в #t более 6 изменений
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Теперь сошлемся на #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
				
В данном случае для второго исполнения в профилировщике можно будет увидеть следующие события.

Свернуть эту таблицуРазвернуть эту таблицу
Класс событияТекст
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - Семь операторов insert -
SP:StmtStartingselect count(*) from #t1
SP:RecompileuseKeepPlan
SP:StmtStartingselect count(*) from #t1
SP:CompleteduseKeepPlan

Процедура перекомпилируется при выборе, происходящем после семи изменений во временной таблице #t.

Эта интенсивная перекомпиляция полезна в случаях, когда изменения в распределении данных во временной таблице могут существенно повлиять на оптимальный план запроса для ссылающегося на таблицу оператора. Однако в случае крупных процедур, часто вносящих мелкие изменения во временные таблицы, перекомпиляции могут привести к общему снижению производительности. Параметр KEEP PLAN оператора SELECT был введен как раз для таких случаев.

KEEP PLAN предотвращает перекомпиляции хранимых процедур, вызванные более чем шестью изменениями во временных таблицах внутри процедуры, и возвращается к стандартному алгоритму перекомпиляции, вызванной изменениями в строках, о котором было рассказано выше, в разделе "Перекомпиляции, вызванные изменениями строк". KEEP PLAN не предотвращает перекомпиляции полностью - только те, которые вызваны более чем шестью изменениями во временных таблицах, на которые ссылается процедура. В вышеприведенном примере, если снять комментарий со строки "option (KEEP PLAN)" в хранимой процедуре, событие SP:Recompile не будет создано.

Если снять комментарий со строки "option (KEEP PLAN)" в вышеприведенном коде, в профилировщике можно будет увидеть следующие события.

Свернуть эту таблицуРазвернуть эту таблицу
Класс событияТекст
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - Семь операторов insert -
SP:StmtStartingselect count(*) from #t1 option (KEEP PLAN)
SP:CompleteduseKeepPlan


Обратите внимание на отсутствие события SP:Recompile.

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

Следующие пять параметров SET имеют по умолчанию значение ON:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
В случае исполнения оператора SET для установки значения OFF для любого из этих параметров хранимая процедура будет перекомпилироваться при каждом запуске. Причина этого заключается в том, что изменения здесь могут повлиять на результаты запроса, вызвавшего перекомпиляцию.

Возьмем для примера следующий код:
Use pubs
drop procedure test_recompile
go

create procedure test_recompile as
Set ANSI_DEFAULTS OFF
Select au_lname, au_fname, au_id from authors
where au_lname like 'L%'
--Option (Keep Plan)
Go
				
В данном случае для каждого исполнения хранимой процедуры в профилировщике SQL можно будет увидеть следующие события:
+---------------------------------------------------+
| Event Class     | Text                            | 
+---------------------------------------------------+
| SP:Starting     | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Recompile    | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Completed    | test_recompile                  | 
+---------------------------------------------------+
				
Замена параметра SET для любого из указанных выше пяти параметров даст те же результаты. Кроме того, использование параметра KEEP PLAN не поможет здесь избежать перекомпиляции, поскольку причиной перекомпиляции является оператор SET.

Рекомендуемым способом предотвращения перекомпиляции является отказ от использования этих пяти операторов SET в хранимой процедуре. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
294942 PRB: SET CONCAT_NULL_YIELDS_NULL может вызвать перекомпиляцию хранимых процедур (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
(эта ссылка может указывать на материал полностью или частично на английском языке) Однако, хоть это и не рекомендуется, запуск оператора SET для сброса параметра подключения на то же значение, что и у хранимой процедуры, также может избежать перекомпиляции:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
В трассе профилировщика SQL более не будет появляться событий SP:Recompile.

В следующей статье перечислены некоторые распростаненные операторы SET с указанием того, вызовет ли изменение оператора SET в хранимой процедуре перекомпиляцию или нет:
Свернуть эту таблицуРазвернуть эту таблицу
Оператор SetПерекомпиляция
Set quoted_identifierНет
Set arithabortДа
Set ansi_null_dflt_onДа
Set ansi_defaultsДа
Set ansi_warningsДа
Set ansi_paddingДа
Set concat_null_yields_nullДа
Set numeric_roundabortНет
Set nocountНет
Set rowcountНет
Set xact_abortНет
Set implicit_transactionsНет
Set arithignoreНет
Set lock_timeoutНет
Set fmtonlyНет

Ссылки

308737 ИНФО. Как определить причину рекомпиляции в событии SP:Recompile (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
(эта ссылка может указывать на материал полностью или частично на английском языке)
Сведения по использованию профилировщика SQL Server привелдены в электронной документации по SQL Server.

Свойства

Код статьи: 243586 - Последний отзыв: 29 декабря 2007 г. - Revision: 2.3
Информация в данной статье относится к следующим продуктам.
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
Ключевые слова: 
kbinfo KB243586

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com