Возможные причины, почему запрос «ломается» на больших объёмах данных:
- Отсутствуют нужные индексы или есть не те индексы (недостающие покрывающие/композитные индексы, неправильный порядок ключей).
- Актуальность статистики (устаревшие/неполные статистики → неверная кардинальность).
- Плохой план выполнения (неподходящий алгоритм джойна: Nested Loops вместо Hash/Merge или наоборот).
- Parameter sniffing / план в кеше не подходит под текущие параметры.
- Селективность и skew данных (разрежённость/горячие значения → плохая оценка).
- Неприменимые индексы: функции или выражения на столбцах (не SARGable), неявные преобразования типов, колляции.
- OR-предикаты, LIKE с ведущим «%», IN с большим подзапросом, correlated subquery — приводят к сканам/многим повторным операциям.
- Фрагментация индексов, недостаток памяти → spills в tempdb, сортировки/агрегаты дорогие.
- План кэширования/проверки версий СУБД (низкая точность CE в старых версиях).
Процесс оптимизации (пошагово):
$1$ Сбор базовой информации: включить профиль/trace, выполнить SET STATISTICS IO, TIME; снять реальный план (live execution plan) и план из кэша (cached plan).
$2$ Выделить самые дорогие операторы (Seq Scan, Key Lookup, Sort, Hash Join) по времени/IO.
$3$ Проверить недостающие индексы через DMVs (например, sys.dm_db_missing_index*), посмотреть существующие индексы и их покрытие.
$4$ Проверить статистики: даты обновления, гистограммы; при необходимости UPDATE STATISTICS WITH FULLSCAN или CREATE/UPDATE STATISTICS.
$5$ Устранить причины не-SARGable (снять функции с колонки, приведения типов), переписать запросы (см. примеры ниже).
$6$ Протестировать новые индексы / реорганизации на стейдже, сравнить планы и IO/TIME; измерить под разными параметрами (учесть parameter sniffing).
$7$ При необходимости: добавить filtered index, include-колонки для покрытия, использовать временные таблицы/индексированные временные таблицы для промежуточных результатов, переписать/декомпозировать сложный запрос.
$8$ Деплой и мониторинг: наблюдать за планами в проде, регулярное обновление статистик и реорганизация индексoв.
Примеры реорганизации запроса (типичные шаблоны):
1) IN -> EXISTS (избегает большого списка, лучше для коррелированных подзапросов)
Исходный:
SELECT o.*
FROM Orders o
WHERE o.CustomerID IN (SELECT c.ID FROM Customers c WHERE c.Status = 'Active');
Переписать:
SELECT o.*
FROM Orders o
WHERE EXISTS (SELECT $1$ FROM Customers c WHERE c.ID = o.CustomerID AND c.Status = 'Active');
2) Убрать функции с колонки (делать SARGable)
Плохо:
WHERE CONVERT(varchar, DateCol, $112$) = '20250101'
Правильно:
WHERE DateCol >= '2025-01-01' AND DateCol < '2025-01-02'
3) OR → UNION ALL / индексируемые ветки (если OR мешает использованию индекса)
Плохо:
WHERE ColA = 'X' OR ColB = 'Y'
Вариант:
SELECT ... FROM T WHERE ColA = 'X'
UNION ALL
SELECT ... FROM T WHERE ColB = 'Y' AND ColA 'X' -- убрать дубликаты по необходимости
4) Коррелированный подзапрос → JOIN / APPLY
Плохо (много раз выполняется подзапрос):
SELECT t.*, (SELECT MAX(d.Date) FROM Details d WHERE d.TId = t.Id) AS LastDate
FROM T
Лучше (один проход):
SELECT t.*, d.LastDate
FROM T
LEFT JOIN (
SELECT TId, MAX(Date) AS LastDate
FROM Details
GROUP BY TId
) d ON d.TId = t.Id;
5) Покрывающий индекс (пример)
Если запрос читает только (CustomerID, OrderDate, Total):
CREATE INDEX IX_Orders_CustDate ON Orders(CustomerID, OrderDate) INCLUDE (TotalAmount);
6) Большие сортировки/топы — использовать индексированную поддержку ORDER BY/TOP:
SELECT TOP $100$ ... FROM Orders ORDER BY OrderDate DESC
— создать индекс ON Orders(OrderDate DESC, ключи нужные для выборки) чтобы избежать сортировки.
7) Parameter sniffing обход:
Если план плох из-за параметров, можно:
- OPTION (RECOMPILE) для конкретного запроса (цена: перекомпиляция),
- или OPTION (OPTIMIZE FOR (@p = UNKNOWN)),
- или использовать параметры с привязкой/локальной переменной внутри процедуры.
Диагностические команды и DMVs (коротко):
- sys.dm_exec_query_stats + sys.dm_exec_sql_text + sys.dm_exec_query_plan — найти тяжёлые запросы.
- sys.dm_db_missing_index_details — подсказки по индексам.
- sys.dm_exec_procedure_stats, sys.dm_exec_query_plan — посмотреть план в кеше.
- DBCC SHOW_STATISTICS(table, index) — посмотреть гистограмму.
Краткие рекомендации при внедрении:
- Всегда тестировать на репрезентативных данных (или сгенерировать похожий объём).
- Минимизировать изменения в проде — сначала индекс/статистика/переписывание запроса на тесте.
- Измерять по IO и по времени; смотреть план и число строк (est vs actual) — большое расхождение укажет на проблему статистики/кардинальности.
Если нужно, могу разобрать конкретный ваш запрос и дать конкретные варианты индексов и переписывания.