Кэш планов SQL Server: комод для запросов

У каждого из нас дома есть ящик, в который годами складываются всевозможные вещи разнообразного вида и назначения. Для хранения каждой из этих вещей нет определенного места, поэтому они оказываются в общей куче. Заглянув в свой ящик, я обнаруживаю предметы, которые используются часто (четыре пары ножниц), редко (не совсем исправная, но пригодная сырорезка) и почти никогда (держатели для кукурузных початков). Внутри SQL Server существует подобная область памяти (именуемая кэшем планов — plan cache, или кэшем процедур — procedure cache), выделенная для хранения планов исполнения. В первую очередь SQL Server проверяет, нет ли готового плана в кэше планов. Если такой план обнаружен, то нет необходимости тратить время на составление нового плана. В результате повышается эффективность и, как правило, производительность.

Я часто сравниваю кэш планов в SQL Server с ящиком для разных вещей. Это хранилище для объектов, одни из которых используются чаще, чем другие. В кэше планов SQL Server можно обнаружить планы, которые требуются часто, иногда и очень редко (или лишь однажды). Главное различие между ящиком комода и кэшем планов состоит в том, что кэш планов не содержит просто хлама. Из него можно извлечь огромное количество полезной информации.

Что в вашем кэше планов?

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

Например, из кэша планов можно извлечь следующее:

1. Планы, которые использовались лишь однажды (как держатели для кукурузы);
2. Планы, которые можно приспособить для какой-то цели (как немного поврежденная сырорезка);
3. Похожие планы (как ножницы).

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

Рассмотрим подробнее каждую категорию.

Как найти «одноразовые» планы в кэше

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

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

SELECT text, cp.objtype, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN(N'Adhoc', N'Prepared')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC
OPTION (RECOMPILE);

В данном случае выполняется поиск объектов типа Compiled Plan. В SQL Server есть еще один объект, именуемый Compiled Plan Stub. Чтобы приостановить падение производительности из-за разрастания кэша планов, в SQL Server 2008 был реализован режим оптимизации для нерегламентированных рабочих нагрузок. В этом режиме SQL Server не сохраняет полный план при первом выполнении запроса. Создается лишь заглушка, которая занимает меньше места в кэше планов. Если план используется второй раз, то сохраняется полностью скомпилированный план. Режим оптимизации для нерегламентированных рабочих нагрузок по умолчанию не включен. Рекомендуется включить этот режим на серверах: негативные последствия такого решения маловероятны.

Как правило, размер памяти, занимаемый всеми заглушками, невелик. Они будут одними из первых объектов, удаляемых из кэша планов, когда возникнет нехватка памяти.

Приведенный выше запрос удобен, если нужно отыскать инструкции, использованные лишь однажды. Но как установить, что единственный раз использованные планы действительно мешают? Можно измерить количество памяти, занимаемой такими планами, и сравнить его с размером всего кэша планов. Сделать это можно с помощью следующего запроса (предоставленного Кимберли Трипп):

SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs — USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans — USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs — USE Count 1] DESC

Трудно рекомендовать определенное соотношение одноразовых планов и кэша планов в целом, но можно руководствоваться следующим критерием: если более 50 % кэша планов приходится на одноразовые планы, то весьма вероятно, что стоит применить режим оптимизации для нерегламентированных рабочих нагрузок (если он еще не задействован). Аналогично, если использованные лишь однажды планы занимают более 2 Гбайт памяти, также полезно принять корректирующие меры. Но граница в 2 Гбайт зависит от размера оперативной памяти, доступной для SQL Server.

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

Если вы хотите периодически очищать кэш планов от инструкций, использовавшихся лишь однажды, то познакомьтесь со сценариями, составленными Кимберли Трипп (http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/). Сценарии рассчитаны на SQL Server 2005 и SQL Server 2008, но совместимы и с SQL Server 2008 R2 и SQL Server 2012.

Представьте, что однажды использовавшиеся планы — это держатели для кукурузы. Затрудняюсь сказать, почему я до сих пор их храню, они только занимают место. От них нужно избавиться и освободить угол для миниатюрных шестигранников, которые я получаю с каждым предметом мебели, купленным в Ikea.

Как найти планы, нуждающиеся в настройке

Еще один пункт, представляющий интерес для старших администраторов баз данных — выяснить, какие планы, находящиеся в кэше, нуждаются в настройке. Можно исследовать кэш планов и выбрать планы с отсутствующими подсказками индекса (на экране 1 приведен пример от Confio Ignite) или выполняющие извлечение ключа, или имеющие предупреждения о неявном преобразовании.

Экран 1. Просмотр хэша планов

Ниже приводится пример, как быстро найти все текущие планы с отсутствующими индексами:

; WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT dec.usecounts, dec.refcounts, dec.objtype
, dec.cacheobjtype, des.dbid, des.text
, deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE
deq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

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

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

; WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.query_hash,cp.query_plan_hash,
PhysicalOperator= operators.value('@PhysicalOp','nvarchar(50)'),
LogicalOp= operators.value('@LogicalOp','nvarchar(50)'),
AvgRowSize= operators.value('@AvgRowSize','nvarchar(50)'),
EstimateCPU= operators.value('@EstimateCPU','nvarchar(50)'),
EstimateIO= operators.value('@EstimateIO','nvarchar(50)'),
EstimateRebinds= operators.value('@EstimateRebinds','nvarchar(50)'),
EstimateRewinds= operators.value('@EstimateRewinds','nvarchar(50)'),
EstimateRows= operators.value('@EstimateRows','nvarchar(50)'),
Parallel= operators.value('@Parallel','nvarchar(50)'),
NodeId= operators.value('@NodeId','nvarchar(50)'),
EstimatedTotalSubtreeCost= operators.value('@EstimatedTotalSubtreeCost','nvarchar(50)')
FROM sys.dm_exec_query_stats cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//RelOp') rel(operators)

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

Приведенные выше сценарии похожи на набор маленьких отверток в ящике комода. С их помощью можно починить сломанные вещи (запросы, в которых отсутствует индекс).

Как найти похожие планы

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

Например, следующие простые запросы отличаются только искомым символьным значением:

SELECT P.FirstName, P.LastName
FROM Person.Person AS P
WHERE P.FirstName = 'Amanda'
GO
SELECT P.FirstName, P.LastNa me
FROM Person.Person AS P
WHERE P.FirstName = 'Logan'
GO

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

SELECT st.text, qs.query_hash
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text = 'SELECT P.FirstName, P.LastName
FROM Person.Person AS P
WHERE P.FirstName = ''Amanda''
'OR st.text = 'SELECT P.FirstName, P.LastName
FROM Person.Person AS P
WHERE P.FirstName = ''Logan'' '
GO

Результирующий набор должен быть таким, как на экране 2.

Экран 2. Один результирующий набор для двух разных запросов

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

SELECT COUNT(*) AS [Count], query_stats.query_hash,
query_stats.statement_text AS [Text]
FROM
(SELECT QS.*,
SUBSTRING(ST.text,(QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
— QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash, query_stats.statement_text
ORDER BY 1 DESC

Отсюда получаем список значений хэша и связанный код SQL, см. экран 3.

Экран 3. Получение списка значений хэша

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

Если невозможно изменить исходный код для создания одного параметризованного запроса, полезно применить структуры плана, чтобы повысить степень повторного использования плана. Более подробную информацию об использовании структуры плана можно найти в документе Specifying Query Parameterization by Using Plan Guides (http://technet.microsoft.com/en-us/library/ms191275(v=sql.105).aspx).

Похожие запросы — аналог моих четырех пар ножниц: на самом деле мне достаточно одних ножниц, остальные просто занимают место.

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

Чтобы получить дополнительные сведения о кэшировании планов в SQL Server, прочитайте превосходный технический документ Грега Лоу (http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx). В нем объясняется, как кэшируются планы, как увеличить уровень повторного использования планов и как SQL Server управляет памятью, выделенной кэшу планов.

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

В заключение еще хочу поблагодарить Тима Чэпмена, PFE в компании Microsoft, за помощь с некоторыми запросами, приведенными в статье.