SQL-Ex blog

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

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

Корешки планов

Вместо заполнения кэша планов выполнения планами, которые никогда не будут использованы повторно, опция optimize for ad hoc workloads будет кэшировать корешки (stub) планов, а не весь план. Корешок плана значительно меньше по размерам, и он заменяется полным планом выполнения, когда SQL Server распознает, что тот же самый план выполняется неоднократно.

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

sp_configure 'show advanced options',1

GO

reconfigure

GO	

sp_configure 'optimize for ad hoc workloads',1

GO

reconfigure

go

Как только опция включена, вы можете увидеть, что корешок плана занимает меньше места в кэше:

-- Запускаем каждый из этих запрсов один раз

DECLARE @Username varchar = 'A'

SELECT UserName 

FROM IndexDemos.dbo.[User] 

WHERE UserName like @Username+'%';

GO

DECLARE @Username varchar = 'B'

SELECT UserName 

FROM IndexDemos.dbo.[User] 

WHERE UserName like @Username+'%';

GO

 SELECT 

    cp.cacheobjtype,

    cp.objtype,

    cp.plan_handle,

    cp.size_in_bytes,

    qp.query_plan,

    st.text

FROM

    sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    INNER JOIN sys.dm_exec_query_stats qs

        ON cp.plan_handle = qs.plan_handle

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

WHERE 

    st.text like 'DECLARE @Username varchar =%';

424 байтов каждый, эти корешки планов малы!

Теперь, если мы выполним еще раз второй запрос, фильтрующий по UserName LIKE 'B%', а затем проверим кэш плана, мы заметим, что корешок заменился на фактический скомпилированный план:



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

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

Замечательно! Все мои проблемы с кэшем будут решены

Не факт.

Если вашу нагрузку действительно составляют множество напрямую передаваемых запросов (например, большое число аналитиков, работающих над различными проблемами, или динамический SQL, которые генерируют совершенно различные операторы при каждом выполнении), включение опции Optimize for Ad hoc Workloads может стать для вас лучшим вариантом (Kimberly Tripp также предлагает отличную альтернативу: автоматическая очистка однократно выполняемых планов по расписанию).

Однако очень часто однократно исполняемые планы запросов имеют более приземленную природу: непараметризованные запросы. В этом случае включение опции Optimize for Ad hoc Workloads не может негативно повлиять на ваш сервер, хотя и определенно не поможет. Почему? Потому что эти исходные запросы будут по-прежнему генерироваться.

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

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