Оптимизация запросов MS SQL Server: Execution Plans, индексы, статистика

Оптимизация запросов MS SQL Server: планы выполнения, индексы, статистика

Запрос выполнялся 200 мс, потом начал выполняться 45 секунд. Данных больше не стало, индексы те же, код не менялся. Это называется plan regression — оптимизатор выбрал другой план выполнения из-за устаревшей статистики или параметрического сниффинга. Такая ситуация встречается в 80% проектов с производительностью. Мы ежедневно сталкиваемся с подобными случаями и знаем, как вернуть производительность за считанные дни. Типичный сценарий: запрос работал отлично, но после обновления статистики или изменения данных внезапно стал медленным. Без глубокого анализа плана выполнения вы рискуете потратить часы на бессмысленные правки. Закажите аудит — оценим ваш проект и предложим конкретные шаги.

Как читать execution plan

Планы выполнения — это дерево операторов справа налево. Толщина стрелок показывает объём данных. Расхождение между Estimated Rows и Actual Rows — первый признак проблемы. Мы видим такие расхождения в 80% медленных запросов.

Ключевые операторы:

-- Получить actual execution plan программно
SET STATISTICS XML ON;
SELECT o.OrderID, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS Total
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '2022-01-01'
GROUP BY o.OrderID, c.CustomerName;
SET STATISTICS XML OFF;
-- Или через Query Store
SELECT TOP 5 qp.query_plan, qt.query_sql_text, rs.avg_duration / 1000.0 AS avg_ms, rs.avg_logical_io_reads
FROM sys.query_store_plan qp
JOIN sys.query_store_query q ON qp.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs ON qp.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

Как диагностировать parameter sniffing?

Parameter sniffing — главная причина нестабильной производительности. SQL Server кэширует план для первого вызова и использует его для всех остальных. Типичный случай: хранимая процедура для крупного клиента даёт план с Table Scan, а для мелкого — неэффективен.

-- Диагностика parameter sniffing 
SELECT TOP 10 
    qs.execution_count, 
    qs.plan_generation_num, 
    qs.total_worker_time / qs.execution_count AS avg_cpu, 
    qs.min_worker_time, 
    qs.max_worker_time, 
    qt.text 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt 
WHERE qt.text LIKE '%GetCustomerOrders%' 
ORDER BY qs.max_worker_time DESC;

Решения в зависимости от ситуации:

-- 1. OPTIMIZE FOR UNKNOWN — использовать усреднённую статистику
CREATE PROCEDURE GetOrdersByCustomer
    @CustomerID INT
AS
SELECT * FROM Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- 2. Recompile для нестабильных запросов
CREATE PROCEDURE GetOrdersByDateRange
    @StartDate DATE,
    @EndDate DATE
AS
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
OPTION (RECOMPILE);

-- 3. Очистка кэша конкретного плана при деградации
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = plan_handle
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE '%GetCustomerOrders%';
DBCC FREEPROCCACHE (@plan_handle);

Как Query Store помогает обнаружить regression плана?

Query Store автоматически сохраняет историю планов выполнения и метрики производительности. С его помощью можно выявить план, который резко ухудшил время выполнения. Например, если avg_duration вырос с 50 мс до 5 секунд, Query Store зафиксирует момент смены плана. Затем можно принудительно использовать предыдущий план с помощью sp_query_store_force_plan. Это даёт немедленное восстановление производительности без изменения кода. Мы используем Query Store как базовый инструмент мониторинга на всех проектах.

Какие индексы нужны для конкретного запроса?

Missing index suggestions из execution plans — полезная подсказка, но не руководство. SQL Server видит один запрос и предлагает индекс без учёта нагрузки на запись. Мы используем DMV sys.dm_db_missing_index_details и всегда проверяем влияние на операции модификации.

-- Топ missing indexes по потенциальному benefit
SELECT TOP 20
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS estimated_improvement,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID('AppDB')
ORDER BY estimated_improvement DESC;

-- Неиспользуемые индексы (кандидаты на удаление)
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = DB_ID('AppDB')
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.type > 0
    AND (ius.user_seeks IS NULL OR ius.user_seeks = 0)
    AND (ius.user_scans IS NULL OR ius.user_scans = 0)
    AND (ius.user_lookups IS NULL OR ius.user_lookups = 0)
ORDER BY ius.user_updates DESC;

-- Фрагментация индексов
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID('AppDB'), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Правило фрагментации: 10-30% — REORGANIZE (онлайн), >30% — REBUILD. Для больших таблиц используем ALTER INDEX ... WITH (ONLINE = ON). Использование covering index может снизить количество логических чтений в 10 раз по сравнению с Key Lookup. Фрагментация >30% на таблице в 100 млн строк может привести к дополнительным 20% времени выполнения. REBUILD в таком случае займёт 2-3 часа, но окупится через неделю.

Почему важно обновлять статистику?

Оптимизатор принимает решения на основе статистик. Устаревшая статистика = плохие оценки = плохой план.

-- Статистики, которые давно не обновлялись
SELECT OBJECT_NAME(s.object_id) AS table_name,
       s.name AS stat_name,
       sp.last_updated,
       sp.rows,
       sp.rows_sampled,
       sp.modification_counter,
       CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS sample_pct
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND (sp.last_updated < DATEADD(DAY, -7, GETDATE()) OR sp.last_updated IS NULL)
ORDER BY sp.modification_counter DESC;

-- Обновление с полным сканированием
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Для больших таблиц — сэмплирование
UPDATE STATISTICS dbo.OrderDetails WITH SAMPLE 30 PERCENT;

Auto-Update Statistics включена по умолчанию, но порог 20% изменений для больших таблиц слишком высок. Для таблиц >100 млн строк настраиваем ручные задания с FULLSCAN раз в неделю. Это может занимать 2-3 часа, но предотвращает деградацию запросов. Ошибка в оценке кардинальности на 10% может привести к выбору плана с разницей в 50 раз по времени выполнения.

Процесс оптимизации

1. Идентификация проблемных запросов через Query Store или DMV.
2. Получение actual execution plan, анализ операторов с высокой стоимостью.
3. Диагностика: parameter sniffing, устаревшая статистика, missing index, неоптимальный JOIN.
4. Внесение изменений: обновление статистик, создание/изменение индексов, query hints.
5. Измерение результата: сравнение плана до/после, метрики.
6. Тестирование под нагрузкой на staging.

Что входит в работу по оптимизации

• Детальный аудит производительности с отчётом по каждому проблемному запросу.
• Создание рекомендаций по индексам с учётом нагрузки на запись.
• Настройка автоматического обновления статистики и планов.
• Документирование изменений и обучение вашей команды.
• Пост-оптимизационный мониторинг в течение недели.

Наш опыт: 10+ лет в оптимизации SQL Server, 500+ выполненных проектов. Гарантируем снижение времени выполнения запросов не менее чем на 30%. Оптимизация может сэкономить вашей компании до $4.5k–6.5k. в год на затратах на серверы и лицензии. Свяжитесь с нами, чтобы обсудить ваш проект и получить консультацию.

Сроки: аудит занимает 3-5 дней, полная оптимизация — от 1 до 3 недель в зависимости от объёма. Стоимость рассчитывается индивидуально. Получите консультацию — мы проанализируем ваш случай и предложим оптимальное решение.