SQL-Ex blog

Что такое хранимая процедура SQL Server?

Хранимая процедура - это готовый код T-SQL, который может повторно использоваться снова и снова. Основные преимущества использования хранимых процедур перечислены ниже:

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

Повторное использование кода: Хранимая процедура SQL помогает избежать неоднократного переписывания одного и того же кода.

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

Безопасность: Хранимые процедуры SQL помогают избежать работы с настройками безопасности различных объектов базы данных. Достаточно дать разрешение только на выполнение хранимой процедуры.

Когда компилируется хранимая процедура?

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

• Динамические административные представления - это специальные системные представления, которые содержат различную информацию о счетчиках производительности SQL Server и другие метрики.


• Расширенные события - очень продвинутый инструмент, который позволяет захватить и вывести различные события, происходящие в SQL Server. Расширенные события используют меньше системных ресурсов, имеют развитые возможности фильтрации и группировки и предоставляют огромное число событий для мониторинга. Поэтому мы создадим расширенное событие для наблюдения за тем, что происходит за сценой процесса компиляции плана запроса хранимых процедур в SQL server. Расширенное событие, которое мы создадим, будет включать следующие события:
  • query_post_compilation_showplan: Это событие захватывает изначально скомпилированный план. В то же время, это событие возвращает предварительный план, когда запрос скомпилирован.
  
  
  • sp_cache_hit: Это событие происходит, когда план хранимой процедуры извлекается из кэша процедурных планов оптимизатором запросов.
  
  
  • sp_cache_insert: Это событие происходит, когда хранимая процедура помещается в процедурный кэш.
  
  
  • sp_cache_miss: Это событие происходит, когда хранимая процедура не обнаруживается в процедурном кэше.
  
  
  • sp_cache_remove Это событие происходит, когда хранимая процедура удаляется из процедурного кэша.
  
  

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

CREATE EVENT SESSION [TrackStoredProcedureEvents] ON SERVER 

ADD EVENT sqlserver.query_post_compilation_showplan(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_hit(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_insert(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_miss(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_remove(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC'))

WITH (STARTUP_STATE=ON)

GO

ALTER EVENT SESSION TrackStoredProcedureEvents ON SERVER   STATE = START

После создания и запуска расширенного события щелкнем на опции “Watch Live Data” созданного расширенного события. Так мы сможем мониторить захваченные события вживую в SQL Server Management Studio (SSMS).



Создадим теперь простую хранимую процедуру в базе данных Adventureworks.

CREATE OR ALTER PROCEDURE GetProductionList

@ProductIdNumber AS INT

AS

SELECT 

    p.[ProductID] 

    ,p.[Name] 

    ,pm.[Name] AS [ProductModel] 

    ,pmx.[CultureID] 

    ,pd.[Description] 

FROM [Production].[Product] p 

    INNER JOIN [Production].[ProductModel] pm 

    ON p.[ProductModelID] = pm.[ProductModelID] 

    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 

    ON pm.[ProductModelID] = pmx.[ProductModelID] 

    INNER JOIN [Production].[ProductDescription] pd 

    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]

WHERE p.ProductID =@ProductIdNumber

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



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

SELECT * FROM (

SELECT cp.objtype AS [Plan Type],

OBJECT_NAME(st.objectid,st.dbid) AS [Object Name],

cp.refcounts AS [Reference Counts],

cp.usecounts AS [Use Counts],

st.TEXT AS [SQL Batch],

qp.query_plan AS [Query Plan]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE cp.objtype='Proc'

) AS TMP_TBL WHERE [SQL Batch] LIKE '%GetProductionList%'

На этом шаге мы выполним нашу тестовую хранимую процедуру со случайным параметром и посмотрим на расширенное событие.

EXEC GetProductionList @ProductIdNumber = 757

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

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



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



Другой вопрос, относящийся к перекомпиляции плана выполнения хранимых процедур SQL Server, связан с опциями соединения. SQL Server позволяет менять некоторые установки сессионных подключений с помощью опций SET. Однако эти опции могут привести к созданию нового плана выполнения. Например, изменим перед выполнением нашего тестового запроса NUMERIC_ROUNDABORT в значение ON, а затем выполним его.

SET NUMERIC_ROUNDABORT ON

GO

EXEC GetProductionList  @ProductIdNumber = 757

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

SELECT * FROM (

SELECT pa.attribute,pa.value,cp.objtype AS [Plan Type],

OBJECT_NAME(st.objectid,st.dbid) AS [Object Name],

cp.refcounts AS [Reference Counts],

cp.usecounts AS [Use Counts],

st.TEXT AS [SQL Batch],

qp.query_plan AS [Query Plan]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st 

OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa

WHERE cp.objtype='Proc' and   pa.attribute = 'set_options'

) AS TMP_TBL WHERE [SQL Batch] LIKE '%GetProductionList%'

Как ясно показано на рисунке, значения опции SET различны, и эта разница вызывает генерацию нового плана выполнения.

Хранимая процедура SQL и sp_recompile

sp_recompile является системной процедурой, которая используется для перекомпиляции хранимых процедур. Использование этой процедуры очень простое - достаточно только передать имя процедуры в качестве параметра.

EXEC sp_recompile N'Имя процедуры'

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

EXEC sp_recompile N'GetProductionList'

После выполнения sp_recompile будет возвращено сообщение “Object ‘GetProductionList’ was successfully marked for recompilation” (“Объект ‘GetProductionList’ был успешно помечен для перекомпиляции”. Фактически это означает, что план выполнения процедуры был удален из кэша планов. Это событие можно увидеть в расширенном событии.



Теперь мы повторно выполним нашу тестовую хранимую процедуру и проанализируем её поведение с помощью расширенного события.

EXEC GetProductionList @ProductIdNumber = 757

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

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

Как использовать хинт WITH RECOMPILE с хранимыми процедурами SQL Server?

В некоторых случаях (parameter sniffing - прослушивание параметра) нам требуется генерировать свежий план запроса для каждого выполнения хранимой процедуры. В этих случаях мы можем добавить хинт WITH RECOMPILE в хранимую процедуру, чтобы оптимизатор запросов генерировал новый план выполнения при каждом выполнении хранимой процедуры. В следующем запросе мы можем добавить хинт WITH RECOMPILE и изменить хранимую процедуру.

CREATE OR ALTER PROCEDURE GetProductionList

@ProductIdNumber AS INT

WITH RECOMPILE

AS

SELECT 

    p.[ProductID] 

    ,p.[Name] 

    ,pm.[Name] AS [ProductModel] 

    ,pmx.[CultureID] 

    ,pd.[Description] 

FROM [Production].[Product] p 

    INNER JOIN [Production].[ProductModel] pm 

    ON p.[ProductModelID] = pm.[ProductModelID] 

    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 

    ON pm.[ProductModelID] = pmx.[ProductModelID] 

    INNER JOIN [Production].[ProductDescription] pd 

    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]

WHERE p.ProductID =@ProductIdNumber

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



При каждом выполнении хранимой процедуры GetProductionList оптимизатор запросов будет перекомпилировать процедуру из-за хинта WITH RECOMPILE. В результате использование хинта RECOMPILE внутри хранимой процедуры приводит к:

• Перекомпиляции при каждом выполнении хранимой процедуры.


• План запроса хранимой процедуры не помещается в кэш планов запросов.

Если включить фактический план выполнения, а затем посмотреть на свойства оператора SELECT, то увидим, что атрибут RetrievedFromCache (взято из кэша) имеет значение false. При выполнении любого запроса с хинтом RECOMPILE этот атрибут будет иметь значение false в плане запроса.



Мы можем использовать хинт WITH RECOMPILE, размещенным в конце вызова хранимой процедуры с тем, чтобы не требовать изменения исходного кода хранимой процедуры.

EXEC GetProductionList @ProductIdNumber = 757 WITH RECOMPILE

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

Заключение

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

• Использоване системной процедуры sp_recompile.


• Использование хинта WITH RECOMPILE.


• Изменение хранимой процедуры.