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

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

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

Чтобы продемонстрировать это, рассмотрим следующий скрипт:

USE AdventureWorks

-- Очистить кэш
DBCC FREEPROCCACHE

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [dbo].[CompSP]

-- Создать новую хранимую процедуру
CREATE PROCEDURE CompSP
AS
    SELECT * FROM HumanResources.Department
GO

-- Проверить план запроса для SQL-пакета
-- Вы увидите, что нет ObjectName с именем CompSP
SELECT cp.objtype AS PlanType,
       OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
       cp.refcounts AS ReferenceCounts,
       cp.usecounts AS UseCounts,
       st.TEXT AS SQLBatch,
       qp.query_plan AS QueryPlan
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;
GO

-- Выполнить хранимую процедуру
EXEC CompSP
GO

-- Проверить план запроса для SQL-пакета
-- Вы увидите, что есть одна запись с именем ObjectName с именем CompSP
SELECT cp.objtype AS PlanType,
       OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
       cp.refcounts AS ReferenceCounts,
       cp.usecounts AS UseCounts,
       st.TEXT AS SQLBatch,
       qp.query_plan AS QueryPlan
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;
GO

В приведенном выше скрипте мы сначала очищаем кэш с помощью команды DBCC FREEPROCCACHE. Затем мы проверяем план запроса для SQL-пакета до и после выполнения хранимой процедуры CompSP.

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

Это ясно демонстрирует, что хранимые процедуры не предварительно компилируются. Они компилируются “на лету” во время первого выполнения, а затем кэшируются для последующих запусков.

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

Для получения более подробной информации о кэшировании планов в SQL Server рекомендую прочитать белую книгу “SQL SERVER – Plan Caching in SQL Server 2008” Грега Лоу. Кроме того, вам может быть интересна моя статья “SQL SERVER – Plan Caching and Schema Change – An Interesting Observation”, где я обсуждаю интересный разговор с Грегом Лоу.

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