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

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

Запит виконувався 200 мс, потім почав виконуватися 45 секунд. Даних більше не стало, індекси ті ж, код не змінювався. Це називається plan regression — оптимізатор вибрав інший план виконання через застарілу статистику або параметричний sniffing. Така ситуація зустрічається у 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%. Зв'яжіться з нами, щоб обговорити ваш проєкт та отримати консультацію.

Терміни: аудит займає 3-5 днів, повна оптимізація — від 1 до 3 тижнів залежно від обсягу. Вартість розраховується індивідуально. Отримайте консультацію — ми проаналізуємо ваш випадок і запропонуємо оптимальне рішення.