Перейти к содержимому
Подготовка к собеседованию

Индексы и планы запросов

Как SQL Server находит строки: устройство индексов, почему условие не попадает в индекс, как читать план выполнения и откуда берётся «быстро в SSMS, медленно из приложения». Примеры — на T-SQL; как то же устроено в PostgreSQL, разобрано в теме PostgreSQL против SQL Server.

Перед этой темой: SQL и T-SQL.

Junior+

Что такое индекс и чем кластерный индекс отличается от некластерного?

Заголовок раздела «Что такое индекс и чем кластерный индекс отличается от некластерного?»
Ответ

Что это

Индекс похож на алфавитный указатель в книге: значения ключа хранятся отсортированными, и сервер находит нужное за несколько шагов по дереву. Такой поиск называется seek, а чтение всей таблицы подряд — scan.

Чем отличаются

Кластерный Некластерный
Что в листьях сами строки таблицы ключ и ссылка на строку
Сколько на таблицу один много
Чем создаётся по умолчанию PRIMARY KEY UNIQUE, CREATE INDEX

Частая ошибка

Создавать индекс на каждую колонку «на всякий случай». Каждый индекс замедляет INSERT, UPDATE и DELETE: сервер обновляет его вместе с таблицей.

Как устроено

  • Индекс хранится в страницах по 8 КБ, собранных в B+-дерево: корень, промежуточные уровни, листья. Даже в таблице на миллионы строк до нужного листа 3–4 чтения.
  • В листьях кластерного индекса лежат строки целиком, поэтому таблица хранится в порядке его ключа.
  • В листьях некластерного — ключ и указатель на строку. Остальные колонки сервер дочитывает из таблицы по указателю.
  • Куча — таблица без кластерного индекса. Строки лежат без порядка, указатель на строку — RID: файл, страница, слот.

Когда что выбирать

Ситуация Решение
Почти любая OLTP-таблица кластерный индекс по узкому возрастающему ключу
Поиск и соединения по другим колонкам некластерные индексы под эти запросы
Промежуточная таблица для загрузки куча допустима

Ловушки

  • Цена записи. INSERT и DELETE меняют все индексы, UPDATE — те, где есть изменённые колонки.
  • Куча и UPDATE. Выросшая строка переезжает на другую страницу, а на старом месте остаётся forwarding pointer — лишнее чтение при каждом обращении.
  • Неуникальный кластерный ключ. Сервер добавляет к нему скрытый 4-байтовый uniqueifier.

Под капотом

  • Ключ кластеризации — указатель на строку во всех некластерных индексах. В неуникальном некластерном индексе он дописывается в конец ключа, в уникальном — в листья как неключевые колонки.
  • Отсюда требования к нему: узкий (копируется в каждый индекс), уникальный (иначе uniqueifier), неизменяемый (смена ключа переписывает строку во всех индексах), возрастающий (вставки идут в конец).
  • Случайный GUID из NEWID() вставляет строки в середину индекса. Страницы расщепляются (page split), заполняются наполовину, растут журнал и объём.

Цена

  • Каждый индекс — копия части данных: место на диске, в буферном пуле и в бэкапах.
  • Каждая модификация пишет в журнал изменения всех затронутых индексов и блокирует их строки.
  • Возрастающий ключ при множестве параллельных вставок упирается в последнюю страницу (last page insert contention). С SQL Server 2019 для этого есть опция индекса OPTIMIZE_FOR_SEQUENTIAL_KEY.

Компромиссы

  • IDENTITY — узкий возрастающий ключ без смысла для бизнеса. GUID удобно генерировать на клиенте, но он широкий: его делают уникальным некластерным ключом, а кластеризуют по IDENTITY, или берут NEWSEQUENTIALID().
  • Кластеризация по дате документа ускоряет отчёты по периодам, но неуникальный ключ получит uniqueifier.

Как проверить в проде

  • sys.dm_db_index_usage_stats: индексы с большим user_updates и почти нулевыми user_seeks, user_scans, user_lookups — кандидаты на удаление. Счётчики обнуляются при перезапуске сервера.
  • sys.dm_db_index_physical_stats показывает число страниц и их заполненность.

Итог: ключ кластеризации влияет на все индексы таблицы, а каждый лишний индекс — налог на каждую запись.

T-SQL
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderId    int IDENTITY PRIMARY KEY,  -- PRIMARY KEY: кластерный индекс по умолчанию
    CustomerId int           NOT NULL,
    OrderDate  date          NOT NULL,
    Amount     decimal(12,2) NOT NULL
);


INSERT dbo.Orders (CustomerId, OrderDate, Amount)
SELECT TOP (100000) n % 5000, DATEADD(day, n % 730, '20240101'), n % 1000
FROM (SELECT CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS int) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;


-- Некластерный индекс: ключ CustomerId, к нему добавлен OrderId — ключ кластеризации
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);


SET STATISTICS IO ON;
SELECT OrderId FROM dbo.Orders WHERE CustomerId = 42; -- Index Seek: несколько чтений
SELECT OrderId FROM dbo.Orders WHERE Amount = 42;     -- Clustered Index Scan: вся таблица
Источники
Middle+

Что такое покрывающий индекс, зачем INCLUDE и как выбрать порядок колонок в составном индексе?

Заголовок раздела «Что такое покрывающий индекс, зачем INCLUDE и как выбрать порядок колонок в составном индексе?»
Ответ

Что это

Покрывающий индекс — индекс, в котором есть все колонки запроса: из WHERE, JOIN, ORDER BY и SELECT. Сервер читает только его и не ходит в таблицу.

Как пользоваться

  1. Колонки, по которым ищут и сортируют, — в ключ индекса.
  2. Колонки, которые только выводят, — в INCLUDE.
  3. Пример: CREATE INDEX IX_Orders_Customer ON dbo.Orders (CustomerId, OrderDate) INCLUDE (Amount).

Частая ошибка

Писать SELECT *. Такой запрос покрывает только кластерный индекс, и любому некластерному придётся дочитывать строки из таблицы.

Как устроено

  • Без покрытия план — Index Seek по некластерному индексу и Key Lookup на каждую найденную строку, соединённые Nested Loops.
  • 10 строк — дёшево, 50 000 — это 50 000 случайных поисков, и оптимизатор выберет scan таблицы. Порог (tipping point) часто меньше нескольких процентов строк.
  • Включённые колонки лежат только на листовом уровне. Они не сортируются, не участвуют в поиске и не входят в лимит размера ключа.

Порядок колонок ключа

  1. Поиск идёт только по префиксу ключа: индекс (A, B) ищет по A или по A и B, но не по одному B.
  2. Сначала колонки с равенством, затем колонка с диапазоном. В индексе (OrderDate, CustomerId) условие CustomerId = @c AND OrderDate >= @d прочитает весь диапазон дат и проверит CustomerId у каждой строки.
  3. Среди колонок с равенством Microsoft советует первой ставить более селективную — ту, что отбирает меньше строк.

Ловушки

Предложения missing index в плане не задают порядок колонок и часто дублируют существующие индексы.

Правило

В ключ — равенства, затем диапазон или сортировка; колонки только для вывода — в INCLUDE.

Под капотом

  • У Index Seek в плане два свойства: Seek Predicates сужают диапазон чтения по B-дереву, Predicate — остаточная проверка каждой прочитанной строки. Хороший индекс переносит максимум условия в Seek Predicates.
  • Гистограмма статистики строится только по первой колонке ключа, для остальных есть лишь средняя плотность префиксов. Ведущая колонка влияет и на точность оценок.
  • Индекс в порядке ORDER BY или GROUP BY убирает Sort вместе с памятью под сортировку, а TOP (n) останавливается после n строк.

Цена

  • Широкий INCLUDE почти дублирует таблицу и удваивает объём записи.
  • UPDATE включённой колонки обновляет и индекс.

Компромиссы

  • Один индекс под несколько запросов лучше пяти похожих: (CustomerId), (CustomerId, OrderDate) и (CustomerId) INCLUDE (Amount) сводятся к (CustomerId, OrderDate) INCLUDE (Amount).
  • sys.dm_db_missing_index_details делит колонки на equality, inequality и included, но не учитывает существующие индексы и цену записи.

Как проверить в проде

  • Key Lookup с большим Actual Number of Rows в фактическом плане — кандидат на покрытие.
  • user_lookups в sys.dm_db_index_usage_stats показывает, сколько lookup-ов приходится на кластерный индекс.

Итог: индекс проектируют под набор запросов: префикс ключа — для поиска, порядок — для сортировки, INCLUDE — для покрытия, и каждую колонку оплачивает запись.

T-SQL
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderId    int IDENTITY PRIMARY KEY,
    CustomerId int           NOT NULL,
    OrderDate  date          NOT NULL,
    Amount     decimal(12,2) NOT NULL
);
INSERT dbo.Orders (CustomerId, OrderDate, Amount)
SELECT TOP (100000) n % 5000, DATEADD(day, n % 730, '20240101'), n % 1000
FROM (SELECT CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS int) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;


-- Равенство (CustomerId) до диапазона (OrderDate); Amount только выводится
CREATE INDEX IX_Orders_Customer_Date
    ON dbo.Orders (CustomerId, OrderDate) INCLUDE (Amount);


SELECT OrderDate, Amount
FROM dbo.Orders
WHERE CustomerId = 42 AND OrderDate >= '20250101'
ORDER BY OrderDate;  -- Index Seek без Key Lookup и без Sort
-- Без INCLUDE (Amount) в плане был бы Key Lookup на каждую строку
Источники
Middle+

Что такое SARGable-условие и почему функции, LIKE '%x' и неявные преобразования типов ломают поиск по индексу?

Заголовок раздела «Что такое SARGable-условие и почему функции, LIKE '%x' и неявные преобразования типов ломают поиск по индексу?»
Ответ

Что это

SARGable-условие — такое, по которому сервер может искать в индексе, а не проверять каждую строку. Колонка стоит в нём без функций и вычислений.

Как пользоваться

Не SARGable SARGable
YEAR(OrderDate) = 2025 OrderDate >= '20250101' AND OrderDate < '20260101'
Price * 1.2 > 120 Price > 100
LEFT(Name, 2) = 'Jo' Name LIKE 'Jo%'
ISNULL(Discount, 0) = 0 (Discount = 0 OR Discount IS NULL)

Частая ошибка

LIKE '%son' с % в начале. Индекс отсортирован по первым символам, поэтому такой поиск всегда читает индекс целиком.

Как устроено

  1. Индекс отсортирован по значениям колонки, и seek возможен, если условие задаёт диапазон этих значений.
  2. YEAR(OrderDate) — уже другая величина. Оптимизатор не выводит её порядок из индекса и вычисляет функцию для каждой строки.
  3. Если типы сторон сравнения разные, SQL Server приводит тип с меньшим приоритетом к большему. Когда приводится колонка, в плане видно CONVERT_IMPLICIT над ней.

Классика из .NET

  • SqlParameter со строковым значением получает тип NVarChar, а AddWithValue берёт длину из самого значения.
  • varchar-колонка младше nvarchar и конвертируется в каждой строке: scan вместо seek.
  • Строки разной длины дают объявления nvarchar(5), nvarchar(6) и отдельный план на каждое.

Ловушки

  • Обратный случай безопасен: varchar-параметр против nvarchar-колонки конвертирует параметр, а не колонку.
  • varchar-колонка с кодами против int-параметра: int старше, конвертируется колонка — scan и ошибка на первом нечисловом значении.

Правило

Тип и длина параметра совпадают с колонкой: SqlDbType.VarChar и Size, в Dapper — DbString { IsAnsi = true }, в EF Core — маппинг IsUnicode(false).

Под капотом

  • Для seek оптимизатор строит границы диапазона в B-дереве. Всё, что не удалось превратить в границы, становится остаточным предикатом и проверяется у каждой прочитанной строки.
  • Часть выражений оптимизатор переписывает сам: CAST(CreatedAt AS date) = @d по datetime2 превращается в динамический диапазон (GetRangeThroughConvert).
  • Для nvarchar против varchar всё решает collation. При Windows-collation (Cyrillic_General_CI_AS, Latin1_General_CI_AS) порядок строк у обоих типов одинаков, и сервер ищет по вычисленному диапазону. При SQL-collation (SQL_Latin1_General_CP1_CI_AS) порядок разный, и остаётся scan.

Цена

Даже «спасённый» seek получает плохую оценку числа строк. В плане появляется предупреждение Type conversion in expression ... may affect "CardinalityEstimate", а за ним неверные соединения и выделение памяти.

Компромиссы

  • Переписать запрос дешевле всего: диапазон вместо YEAR(), явная проверка NULL вместо ISNULL.
  • Если выражение нужно как есть — вычисляемая колонка с индексом: оптимизатор сопоставит с ней такое же выражение в запросе.
  • LIKE '%x' B-деревом не лечится: полнотекстовый индекс, колонка с перевёрнутой строкой или внешний поиск.

Как проверить в проде

Ищите PlanAffectingConvert в XML планов из sys.dm_exec_query_stats и sys.dm_exec_query_plan, отсортированных по логическим чтениям.

Итог: SARGability решает, станет ли условие границей поиска или проверкой каждой строки, а неявные преобразования — самая частая невидимая причина scan в .NET-приложениях.

T-SQL
DROP TABLE IF EXISTS dbo.Products;
CREATE TABLE dbo.Products
(
    ProductId int IDENTITY PRIMARY KEY,
    Sku       varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CreatedAt datetime2   NOT NULL
);
INSERT dbo.Products (Sku, CreatedAt)
SELECT TOP (100000) CONCAT('SKU-', n), DATEADD(minute, n * 10, '20240101')
FROM (SELECT CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS int) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;
CREATE INDEX IX_Products_Sku ON dbo.Products (Sku);
CREATE INDEX IX_Products_CreatedAt ON dbo.Products (CreatedAt);


DECLARE @n nvarchar(20) = N'SKU-42', @v varchar(20) = 'SKU-42';
SELECT ProductId FROM dbo.Products WHERE Sku = @n; -- Index Scan: CONVERT_IMPLICIT над Sku
SELECT ProductId FROM dbo.Products WHERE Sku = @v; -- Index Seek


SELECT ProductId FROM dbo.Products WHERE YEAR(CreatedAt) = 2025; -- Index Scan
SELECT ProductId FROM dbo.Products
WHERE CreatedAt >= '20250101' AND CreatedAt < '20260101';        -- Index Seek
C#
Microsoft.Data.SqlClient
using System.Data;
using Microsoft.Data.SqlClient;


await using var connection = new SqlConnection(
    "Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true");
await connection.OpenAsync();
const string sql = "SELECT ProductId FROM dbo.Products WHERE Sku = @sku";


// Плохо: string → nvarchar(6), varchar-колонка конвертируется в каждой строке
await using var bad = new SqlCommand(sql, connection);
bad.Parameters.AddWithValue("@sku", "SKU-42");


// Хорошо: тип и длина как у колонки — seek и один план для любых значений
await using var good = new SqlCommand(sql, connection);
good.Parameters.Add("@sku", SqlDbType.VarChar, 20).Value = "SKU-42";


Console.WriteLine(await good.ExecuteScalarAsync());
Источники
Middle+

Как читать план выполнения: seek, scan и lookup, оценочный и фактический план, операторы соединения, предупреждения?

Заголовок раздела «Как читать план выполнения: seek, scan и lookup, оценочный и фактический план, операторы соединения, предупреждения?»
Ответ

Что это

План выполнения — схема, по которой SQL Server выполняет запрос. В SSMS Ctrl+L показывает оценочный план без выполнения, а Ctrl+M включает фактический для следующего запуска.

Основные операторы доступа

Оператор Что делает
Index Seek ищет по ключу индекса — дёшево
Index Scan, Clustered Index Scan, Table Scan читает индекс или таблицу целиком
Key Lookup, RID Lookup дочитывает колонки из таблицы для каждой строки

Как пользоваться

  1. Читайте справа налево: данные текут от таблиц к SELECT.
  2. Толщина стрелки — число строк.
  3. Жёлтый треугольник — предупреждение: наведите курсор на оператор.

Частая ошибка

Считать любой scan плохим. Маленькую таблицу или почти все строки scan прочитает быстрее, чем тысячи seek.

Как устроено

  • Оптимизатор оценивает число строк каждого оператора по статистике и выбирает самый дешёвый из рассмотренных планов.
  • Оценочный план — результат компиляции. Фактический добавляет Actual Number of Rows, Number of Executions, время и чтения по операторам.
  • Главный сигнал — Estimated против Actual Rows. Ошибка на порядок означает устаревшую статистику, несаргабельное условие или план, скомпилированный под другие значения параметров.

Когда что выбирать

Соединение Когда хорош Цена
Nested Loops внешний вход мал, у внутреннего есть индекс поиск во внутреннем на каждую внешнюю строку
Hash Match большие несортированные входы память под хеш-таблицу, ждёт построения таблицы
Merge Join оба входа отсортированы по ключу Sort, если готового порядка нет

Ловушки

  • Проценты стоимости — оценка даже в фактическом плане.
  • На внутренней стороне Nested Loops оценка строк дана на одно выполнение, а факт — сумма за все. Делите факт на Number of Executions.

Правило

Сначала операторы с наибольшим расхождением оценки и факта и с наибольшим числом чтений, потом предупреждения.

Под капотом

  • Управление идёт слева направо: корневой оператор запрашивает строки у дочерних, а данные текут справа налево. Поэтому TOP слева может остановить scan справа после первых строк.
  • Sort и Hash Match получают выделение памяти до старта, по оценке строк. Недооценка ведёт к сбросу в tempdb, переоценка — к ожиданию памяти другими запросами (RESOURCE_SEMAPHORE).
  • С SQL Server 2017 в пакетном режиме (batch mode) есть Adaptive Join: выбор между Hash и Nested Loops по фактическому числу строк.

Предупреждения, которые стоит знать

  • PlanAffectingConvert — неявное преобразование мешает seek или оценке.
  • Spill to tempdb — не хватило выделенной памяти.
  • No Join Predicate — пропущено условие соединения.
  • Columns With No Statistics, Excessive Grant и подсказка Missing Index.

Как проверить в проде

  • План из кеша (sys.dm_exec_query_plan) — оценочный, без фактических строк.
  • С SQL Server 2019 lightweight profiling включён по умолчанию. С LAST_QUERY_PLAN_STATS = ON последний фактический план отдаёт sys.dm_exec_query_plan_stats.
  • Для идущего запроса — sys.dm_exec_query_statistics_xml или Live Query Statistics.

Итог: читайте фактический план как историю оценок: где оценка разошлась с фактом, там неверные соединения, память и порядок операций.

T-SQL
DROP TABLE IF EXISTS dbo.OrderLines, dbo.Orders;
CREATE TABLE dbo.Orders (OrderId int PRIMARY KEY, CustomerId int NOT NULL);
CREATE TABLE dbo.OrderLines
(
    OrderId int NOT NULL, LineNum int NOT NULL, Qty int NOT NULL,
    PRIMARY KEY (OrderId, LineNum)
);
INSERT dbo.Orders (OrderId, CustomerId)
SELECT TOP (50000) n, n % 1000
FROM (SELECT CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS int) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;
INSERT dbo.OrderLines (OrderId, LineNum, Qty)
SELECT OrderId, 1, CustomerId % 7 FROM dbo.Orders;


SET STATISTICS IO, TIME ON;  -- в SSMS включите фактический план: Ctrl+M
-- Подсказки — только для эксперимента: один запрос, три алгоритма соединения
SELECT SUM(l.Qty) FROM dbo.Orders AS o JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
OPTION (LOOP JOIN);   -- 50 000 seek во внутренний индекс: больше всего чтений
SELECT SUM(l.Qty) FROM dbo.Orders AS o JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
OPTION (MERGE JOIN);  -- два упорядоченных scan, без сортировки
SELECT SUM(l.Qty) FROM dbo.Orders AS o JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
OPTION (HASH JOIN);   -- хеш-таблица в памяти: Memory Grant в свойствах SELECT
Источники
Senior+

Что такое parameter sniffing и кеш планов, как диагностировать и лечить проблему?

Заголовок раздела «Что такое parameter sniffing и кеш планов, как диагностировать и лечить проблему?»
Ответ

Что это

  • Кеш планов — память, где SQL Server хранит скомпилированные планы, чтобы не компилировать запрос заново.
  • Parameter sniffing — при первой компиляции оптимизатор «подсматривает» значения параметров и строит план под них.

Почему это проблема

У клиента A пять заказов, у клиента B — полмиллиона. Если первым пришёл A, в кеше окажется план с поиском по индексу и Key Lookup. Для B этот план сделает полмиллиона lookup, хотя быстрее было бы прочитать таблицу целиком.

Частая ошибка

Лечить проблему перезапуском сервера или DBCC FREEPROCCACHE без параметров. Это сбросит все планы, а плохой вернётся при следующем «неудачном» первом вызове.

Как устроено

  1. ADO.NET и EF Core отправляют параметризованный запрос через sp_executesql. Текст запроса вместе с объявлениями параметров — часть ключа кеша.
  2. При первом вызове оптимизатор строит план по гистограмме для переданных значений и кладёт его в кеш.
  3. Вызовы с тем же текстом, типами параметров и SET-параметрами сессии берут готовый план.
  4. После обновления статистики или вытеснения из кеша план компилируется заново — под значения того вызова, что придёт первым.

Когда что выбирать

Средство Когда
OPTION (RECOMPILE) запрос редкий и тяжёлый, компиляция дешевле плохого плана
OPTIMIZE FOR (@p = ...) есть типичное значение, план под него годится всем
OPTIMIZE FOR UNKNOWN нужен средний план по плотности, а не по первому значению
Форсирование плана код менять нельзя, а хороший план уже был

Ловушки

  • Локальная переменная вместо параметра (DECLARE @c int = @CustomerId) действует как OPTIMIZE FOR UNKNOWN: план строится по средней плотности.
  • В SSMS по умолчанию ARITHABORT ON, а в ADO.NET — OFF. Это разные записи в кеше, поэтому в SSMS запрос компилируется заново под ваши значения и «летает».

Под капотом

  • В XML фактического плана у каждого параметра есть ParameterCompiledValue и ParameterRuntimeValue. Их расхождение — прямое доказательство.
  • Несколько query_plan_hash на один query_hash и большой разброс min_elapsed_time и max_elapsed_time в sys.dm_exec_query_stats — признак чувствительности к параметрам.
  • Query Store появился в SQL Server 2016 и включён по умолчанию для новых баз с 2022. Он хранит планы и их статистику выполнения, регрессию видно в отчёте Regressed Queries.

Компромиссы

Средство Цена
RECOMPILE CPU на компиляцию при каждом вызове
sp_query_store_force_plan план фиксирован при любом росте данных
Подсказки Query Store, 2022+ RECOMPILE или OPTIMIZE FOR без правки кода, удобно для запросов EF
PSP optimization, 2022+, уровень 160 несколько планов только для предикатов-равенств, больше записей в кеше

SQL Server 2025 на уровне совместимости 170 добавил optional parameter plan optimization: для шаблона col = @p OR @p IS NULL — отдельные планы для NULL и не-NULL.

Как проверить в проде

  • Query Store: запрос с несколькими планами и разным временем. Хороший план закрепляют, затем следят за регрессиями.
  • Воспроизводят тот же sp_executesql с тем же объявлением параметров и SET ARITHABORT OFF. Запрос из трассировки с подставленными литералами — уже другой запрос: литерал даёт свой план, локальная переменная — план по плотности.

Итог: sniffing лечат точечно и измеримо: доказать по ParameterCompiledValue, выбрать между перекомпиляцией, фиксацией плана и PSP, проверить остальные значения.

T-SQL
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderId    int IDENTITY PRIMARY KEY,
    CustomerId int           NOT NULL,
    Amount     decimal(12,2) NOT NULL
);
-- Перекос: у клиента 1 — 100 000 заказов, у остальных — по одному
INSERT dbo.Orders (CustomerId, Amount)
SELECT TOP (100000) 1, 10 FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
INSERT dbo.Orders (CustomerId, Amount)
SELECT TOP (1000) 1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 10 FROM sys.all_objects;
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);


-- Так запрос отправляют ADO.NET и EF Core
DECLARE @sql nvarchar(200) = N'SELECT SUM(Amount) FROM dbo.Orders WHERE CustomerId = @c';
EXEC sp_executesql @sql, N'@c int', @c = 2;  -- план: Seek + Key Lookup, попадает в кеш
EXEC sp_executesql @sql, N'@c int', @c = 1;  -- тот же план: 100 000 Key Lookup
-- На уровне совместимости 160+ PSP optimization может построить второй план


-- Без правки приложения (SQL Server 2022+): найти query_id в Query Store и добавить подсказку
-- EXEC sys.sp_query_store_set_hints @query_id = 42, @query_hints = N'OPTION (RECOMPILE)';
Источники
Senior+

Статистика, фрагментация и обслуживание индексов: что важно и когда нужны filtered и columnstore индексы?

Заголовок раздела «Статистика, фрагментация и обслуживание индексов: что важно и когда нужны filtered и columnstore индексы?»
Ответ

Что это

  • Статистика — объект с гистограммой значений колонки: сколько строк приходится на каждый диапазон. По ней оптимизатор решает, seek или scan.
  • Фрагментация — логический порядок страниц индекса расходится с физическим, а страницы заполнены не до конца.
  • Фильтрованный индекс — CREATE INDEX ... WHERE Status = 0: в нём только подходящие строки.
  • Columnstore — хранит каждую колонку отдельно и сжато, поэтому агрегаты по миллионам строк читают мало данных.

Как пользоваться

  • Автообновление статистики (AUTO_UPDATE_STATISTICS) не отключают.
  • После массовой загрузки статистику обновляют вручную: UPDATE STATISTICS dbo.Orders.

Частая ошибка

Ночной REBUILD всех индексов «для профилактики». Он нагружает диск и журнал, а пользу часто даёт только обновлённая статистика.

Как устроено

  • Гистограмма — до 200 шагов по первой колонке статистики, плюс плотность для префиксов колонок.
  • На уровне совместимости 130+ порог автообновления — MIN(500 + 0.2 * n, SQRT(1000 * n)) изменений. Для 2 млн строк это около 45 000, а не 400 000.
  • С AUTO_UPDATE_STATISTICS_ASYNC запрос не ждёт обновления: он компилируется со старой статистикой, а новая считается в фоне.

Обслуживание

Операция Что делает
UPDATE STATISTICS пересчитывает статистику, дёшево
REORGANIZE уплотняет листья, всегда онлайн, статистику не трогает
REBUILD пересоздаёт индекс и статистику с полным сканированием

Когда что выбирать

  • Filtered — малая горячая часть таблицы (очередь, IsDeleted = 0) и уникальность среди не-NULL значений.
  • Nonclustered columnstore — аналитика прямо по OLTP-таблице, clustered columnstore — таблицы фактов хранилища.

Ловушки

  • Индекс WHERE Status = 0 не подойдёт запросу WHERE Status = @s: закешированный план обязан работать при любом значении. Нужен литерал или OPTION (RECOMPILE).
  • Изменение таблицы с фильтрованным индексом падает при QUOTED_IDENTIFIER OFF — так по умолчанию в sqlcmd и в процедурах, созданных с этой настройкой.

Под капотом

  • REBUILD обновляет статистику индекса как FULLSCAN, и зависящие от неё планы перекомпилируются. Microsoft прямо пишет, что эффект «после перестроения» часто даёт именно статистика.
  • Фрагментация мешает, когда последовательное чтение заметно быстрее случайного, — на HDD. На SSD и для данных в памяти важнее плотность страниц: avg_page_space_used_in_percent в sys.dm_db_index_physical_stats.
  • Columnstore делит таблицу на группы до 1 048 576 строк (rowgroup) и сжимает каждую колонку в сегмент с min и max. Запрос пропускает сегменты вне условия, а пакетный режим обрабатывает строки пачками.
  • Мелкие вставки попадают в delta store — обычное B-дерево; удаление в сжатых группах только помечает строки.

Цена

  • REBUILD пишет в журнал и требует места под копию индекса. ONLINE = ON есть только в Enterprise, возобновляемое перестроение (RESUMABLE = ON) — с SQL Server 2017.
  • UPDATE в columnstore — это пометка удаления плюс вставка в delta store. Для поиска отдельных строк поверх clustered columnstore добавляют B-tree индекс.

Компромиссы

  • Ordered columnstore (ORDER (...)): clustered — с SQL Server 2022, nonclustered — с 2025. Пропуск сегментов лучше, загрузка медленнее.
  • Пакетный режим без columnstore — batch mode on rowstore, SQL Server 2019 на уровне совместимости 150.

Как проверить в проде

  • sys.dm_db_stats_properties: last_updated, rows_sampled, modification_counter.
  • Эффект обслуживания измеряют в Query Store до и после, а не по проценту фрагментации.

Итог: статистику обслуживают регулярно, индексы — по измерениям, а filtered и columnstore выигрывают только на своём профиле данных.

T-SQL
DROP TABLE IF EXISTS dbo.Tasks;
CREATE TABLE dbo.Tasks
(
    TaskId    int IDENTITY PRIMARY KEY,
    Status    tinyint   NOT NULL,  -- 0 — новая, 1 — обработана
    CreatedAt datetime2 NOT NULL DEFAULT SYSUTCDATETIME()
);
INSERT dbo.Tasks (Status)
SELECT TOP (100000) IIF(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 1000 = 0, 0, 1)
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;


-- Фильтрованный индекс: 100 новых задач из 100 000
CREATE INDEX IX_Tasks_New ON dbo.Tasks (CreatedAt) WHERE Status = 0;
SELECT TOP (10) TaskId FROM dbo.Tasks WHERE Status = 0 ORDER BY CreatedAt; -- Seek по IX_Tasks_New


-- Когда обновлялась статистика и сколько изменений накопилось
SELECT s.name, sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.Tasks');


-- Плотность страниц и фрагментация
SELECT index_id, avg_page_space_used_in_percent, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Tasks'), NULL, NULL, 'SAMPLED');
Источники