Решение проблем с большим кешем запросов SQL Server
Если у вас много непараметризированных запросов, то кеш запросов будет содержать много одноразовых планов запросов.
Просмотреть какой в кеше % 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 запросов
Просмотр основных запросов в кеше по размеру:
-- выдать 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
Либо это сделать через Свойства сервера в Дополнительно Optimize for ad hoc workloads = true
Как очистить весь процедурный кэш для экземпляра:
DBCC FREEPROCCACHE;
Дополнительно:
- https://eazybi.blogspot.com/2015/07/sql.html
- https://skproj.ru/pochemu-dbcc-freeproccache-pomogaet/?doing_wp_cron=1618856089.8024010658264160156250
Что еще посмотреть по SQL Server
Оптимизация SQL запросов MS SQL. Медленно работают запросы SELECT
Как найти медленные проблемные запросы SQL Server. Оптимизация быстродействия SQL Server
SQL Server. Работа с индексами через запросы SQL
Где ставить индексы SQL Server
Оптимизация SQL запросов, план запроса в SQL Server
Инструмент для анализа производительности запросов SQL Server Query Store
SQL Server Как найти запросы с проблемами (warning) в плане выполнения запроса (SQL Plan)
Анализ статистики по индексам в базе данных SQL Server
Определение какая база данных создает нагрузку на CPU
Оптимизация SQL - медленная конвертация таблицы в строку
Медленный sql. Как найти, что вызывает замедление в хранимой процедуре MS SQL
Ищем партнеров-разработчиков на T-SQL
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
- Шаг 1. Создать концепт проекта
- Шаг 2. Получить оценку бюджета (КП)
- Шаг 3. Заключить договор
- Шаг 4. Создать совместно техническое задание
- Шаг 5. Поэтапная реализация проекта