Архив метки: SQL

Раздуло кэш SQL? Лечимся народными средствами.

Если при разработке приложений не пользоваться хранимыми процедурами и параметризованными запросами, чем грешат большинство разработчиков, посылая произвольные (Adhoc) запросы серверу
То рано или поздно можно столкнуться с проблемой раздутого процедурного кэша.
Подробнее о механизмах кэширования тут
1.Проверяем есть ли проблемы с процедурным кэшем.
Чтобы понять есть ли проблема с Adhoc запросами, достаточно запустить следующий запрос
SELECT T.*, cast(T.[Total Plans — USE Count 1]*1.0/nullif(t.[Total Plans],0) *100  AS decimal(5,2)) Percentage
FROM
(
       SELECT objtype AS [CacheType]
                    , count_big(*) AS [Total Plans]
                    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
                    , 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 cp
       WHERE cp.cacheobjtype = N'Compiled Plan'
       AND cp.objtype IN (N'Adhoc', N'Prepared') — adhoc (произвольный запрос), prepared (параметризованный)
       GROUP BY objtype
) T
ORDER BY [Total MBs — USE Count 1] DESC
OPTION (RECOMPILE);  —план не будет сохраняться, эту опцию желательно использовать для adhoc запросов
Из результатов запроса понятно что процент  Adhoc  запросов  которые выполнялись только 1 раз очень высокий 82%

Ниже разница  в планах  adhoc и prepared планах
— выдать 100 первых запросов раздувающих кэш по размеру
SELECT top 100 *
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
Как-же это лечится если повлиять на разработчиков никак не возможно, есть как говорится одно народное средство …..

USE master
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE WITH OVERRIDE
GO
Использование этого параметра не очищает процедурный кэш, его очистку необходимо выполнить вручную, есть несколько вариантов:
1.
— очистить весь процедурный кэш для экземпляра
DBCC FREEPROCCACHE;

2. очистить для базы
—Идентификатор базы
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'IntegrationDB')

—select @intDBID
—очищаем процедурный кэш для базы
DBCC FLUSHPROCINDB (@intDBID)

3. удалить конкретные планы вручную
— получаем указатель на план
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

— удаляем план из кэша
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

Параметр optimize for ad hoc workloads используется для повышения эффективности кэширования планов рабочих нагрузок, содержащих много отдельных нерегламентированных пакетов. Если этот параметр имеет значение 1, компонент Database Engine при первой компиляции пакета, сохраняет в кэше планов небольшую скомпилированную заглушку плана Compiled Plan Stub, а не полный откомпилированный план. Это несколько снижает требования к памяти, так как кэш планов не заполняется скомпилированными, не используемыми повторно планами.
Скомпилированная заглушка плана принадлежит к объектам cacheobjtypes, которые можно просмотреть в представлении каталога sys.dm_exec_cached_plans. У каждой заглушки есть уникальный дескриптор SQL и дескриптор плана. Со скомпилированной заглушкой плана не связан план выполнения. Запрос по дескриптору плана не вернет XML-код Showplan
Для чего это надо ?
Каждая инструкция (T-SQL, SP и другие) сохраняет свой план выполнения в кэше, для дальнейшего использования. По этой причине, большое число планов хранится в системе и множество из них могут использоваться только 1 раз. Это в пустую тратит ресурсы сервера.
Чтобы не сохранялись в кэше планы запросов которые выолняются 1 раз придумали этот параметр сервера.
В загруженной системе легко может быть более миллиона разных инструкций, представьте сколько ресурсов вы тратите в пустую. Благодаря параметру optimize for ad hoc workloads  можно избежать хранения ненужных планов.
Если посмотреть на результаты  внизу после включения опции optimize for ad hoc workloads можно у

Новые «старые» ошибки MS SQL 2012

С каждой новой версией продукта от Microsoft пользователи, да чего уже там даже системные администраторы/ИТ-инженера/ и прочие причастные к этому миру ждут, что новая версия будет более лучше работать, и перестанет сыпать непонятными ошибками. Или, по крайней мере, научится решать ошибки, которые возникали в ранних продуктах. Но нет.

И так сегодня осветим пару “старых” ошибок, которые возникают при установке нового MS SQL 2012.

Читать →