Индексы и планы выполнения SQL Server: как это устроено

Большинство медленных запросов SQL Server медленны по одной из трёх причин: нет индекса, по которому запрос может сделать seek; индекс есть, но запрос написан так, что использовать его нельзя; или индекс быстро находит строки, а потом достаёт каждый столбец из таблицы по одной строке за раз. План выполнения за несколько секунд скажет, какая из трёх, если знать, куда смотреть. Это руководство объясняет, что такое индексы SQL Server на самом деле, чем отличаются кластерные и некластерные индексы, зачем нужны включённые столбцы и как читать план справа налево, пока дорогой оператор не перестанет прятаться.

Примеры используют SQL Server 2022, и всё здесь относится к редакции Express - все типы индексов, включая фильтрованные и columnstore, доступны во всех редакциях начиная с SQL Server 2016 Service Pack 1. Чего в Express нет, так это онлайн-перестроения, а на базе с потолком в 10 ГБ оно значит меньше, чем принято думать.

Как SQL Server хранит таблицу#

Индекс SQL Server - это B-дерево: корневая страница, несколько промежуточных уровней и листовые страницы внизу, каждая страница по 8 КБ. Поиск ключа идёт от корня вниз и затрагивает по одной странице на уровень. У таблицы в десять миллионов строк индекс обычно имеет три-четыре уровня в глубину, так что поиск одной строки стоит трёх-четырёх чтений страниц вместо чтения всей таблицы.

Таблицы бывают двух видов:

• Кластерная таблица имеет кластерный индекс, и его листовой уровень - это и есть таблица. Строки хранятся в порядке кластерного ключа, внутри индекса. Кластерный индекс у таблицы может быть только один, потому что физически упорядочить строки можно только одним способом.
• Куча (heap) не имеет кластерного индекса. Строки лежат там, где нашлось место, и каждая адресуется физическим идентификатором строки (файл, страница, слот).

Объявление PRIMARY KEY по умолчанию создаёт на нём кластерный индекс, если у таблицы ещё нет кластерного индекса и вы не написали PRIMARY KEY NONCLUSTERED. Из-за этого значения по умолчанию почти каждая таблица в базе приложения кластеризована по столбцу IDENTITY типа int или bigint, и для большинства таблиц это хороший выбор: ключ узкий, уникальный, никогда не меняется и всегда растёт, так что новые строки попадают на последнюю страницу, а не разбивают страницы посередине.

Кучи разумны для промежуточных таблиц, которые загружают и потом один раз читают. Для всего, что обновляется, у них давняя проблема: когда обновлённая строка больше не помещается на свою страницу, SQL Server переносит её и оставляет на старом месте указатель переадресации, а чтения потом ходят по этим указателям. Давайте таблицам приложения кластерный индекс.

Некластерные индексы и включённые столбцы#

Некластерный индекс - это отдельное B-дерево, в котором хранятся его ключевые столбцы и указатель обратно на строку. В кластерной таблице этот указатель - кластерный ключ, в куче - идентификатор строки. У таблицы может быть до 999 некластерных индексов, что примерно на 990 больше, чем должно быть у любой таблицы.

CREATE TABLE dbo.Orders (    OrderId     int IDENTITY(1,1) NOT NULL PRIMARY KEY,   -- clustered by default    CustomerId  int           NOT NULL,    Status      tinyint       NOT NULL,    CreatedAt   datetime2(0)  NOT NULL,    Total       decimal(12,2) NOT NULL,    Notes       nvarchar(max) NULL);CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_CreatedAt    ON dbo.Orders (CustomerId, CreatedAt)    INCLUDE (Status, Total);

Ключевые столбцы, CustomerId и CreatedAt, отсортированы, и по ним можно искать. Столбцы из INCLUDE хранятся только на листовом уровне: по ним нельзя искать или сортировать, но запрос, которому они нужны, может прочитать их прямо из индекса, не возвращаясь к таблице. Индекс, содержащий все столбцы, которых касается запрос, называется покрывающим, и это самая полезная форма во всей настройке SQL Server.

Порядок столбцов в ключе важен, и правило простое: сначала равенство, потом диапазон или сортировка. Этот индекс обслуживает все такие запросы:

-- Seek on CustomerId, rows already in CreatedAt order: no sort neededSELECT OrderId, CreatedAt, TotalFROM dbo.OrdersWHERE CustomerId = 42ORDER BY CreatedAt DESC;-- Seek on CustomerId, then a range on CreatedAtSELECT OrderId, Status, TotalFROM dbo.OrdersWHERE CustomerId = 42  AND CreatedAt >= '2026-09-01';

Отдельно WHERE CreatedAt >= '2026-09-01' он не помогает, потому что индекс отсортирован сначала по клиенту, а у каждого клиента есть строки в сентябре. Такому запросу нужен индекс, который начинается с CreatedAt.

Несколько ограничений, которые стоит знать: ключ индекса может занимать не больше 900 байт для кластерного индекса и 1700 байт для некластерного, и в нём не больше 32 ключевых столбцов. Столбцы nvarchar(max) и varbinary(max) вообще не могут быть ключевыми, хотя их можно включить - обычно это плохая идея, потому что большие значения копируются в индекс.

Фильтрованные индексы и columnstore#

У фильтрованного индекса есть условие WHERE, и он индексирует только подходящие строки. Это правильный инструмент, когда запросы раз за разом просят небольшой, чётко очерченный срез большой таблицы:

CREATE NONCLUSTERED INDEX IX_Orders_Pending    ON dbo.Orders (CreatedAt)    INCLUDE (CustomerId, Total)    WHERE Status = 0;

Если в ожидании находятся 2 процента заказов, этот индекс занимает 2 процента от размера полного и намного дешевле в обслуживании. Подвох в том, что оптимизатор использует его, только когда может доказать, что предикат запроса попадает внутрь фильтра. Запрос с WHERE Status = @status и параметром обычно не подойдёт, потому что план должен работать для любого значения. Фильтрованные индексы подходят запросам с литеральными значениями или с OPTION (RECOMPILE). Кроме того, это стандартный способ обеспечить уникальность в столбце, допускающем NULL, разрешив при этом несколько NULL: уникальный индекс WHERE Email IS NOT NULL.

Индекс columnstore хранит данные по столбцам, а не по строкам, в сжатом виде, и обрабатывает их пакетами. Он прекрасно агрегирует миллионы строк - отчёты, дашборды, аналитика по таблицам событий - и плохо достаёт отдельные строки. В Express он работает, но память для columnstore ограничена 352 МБ на экземпляр, так что он подходит для скромных аналитических таблиц, а не для превращения Express в хранилище данных.

Как получить план выполнения#

План выполнения - это дерево операторов, которое SQL Server выбрал для выполнения запроса. Посмотреть можно на два вида:

• Предполагаемый план - то, что оптимизатор собирается сделать, без выполнения запроса. В SSMS это Ctrl+L.
• Фактический план - тот же план плюс то, что произошло на самом деле: фактическое число строк, количество выполнений каждого оператора, выделение памяти, предупреждения. В SSMS включите «Include Actual Execution Plan» через Ctrl+M, затем выполните запрос.

Всегда предпочитайте фактический план, если можете позволить себе выполнить запрос. Самое важное число в любом плане - разрыв между оценочным и фактическим числом строк, а в предполагаемом плане его нет.

Дополняйте план статистикой ввода-вывода: она показывает объём проделанной работы в единицах, которые можно сравнивать между версиями запроса:

SET STATISTICS IO, TIME ON;SELECT OrderId, CreatedAt, TotalFROM dbo.OrdersWHERE CustomerId = 42ORDER BY CreatedAt DESC;

Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, ... SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 1 ms.

Логические чтения - это страницы по 8 КБ, прочитанные из памяти. Именно это число нужно снижать: исправление, которое переводит запрос с 48 000 логических чтений на 12, сработало, что бы ни показывало время выполнения на прогретом кэше. Вне SSMS SET STATISTICS XML ON возвращает фактический план в виде XML, который любой инструмент может сохранить и открыть позже.

Чтение плана: операторы, которые важны#

Графический план читается справа налево и сверху вниз: данные начинаются в операторах справа, текут влево по стрелкам, толщина которых отражает число строк, и заканчиваются в SELECT слева. У каждого оператора указан процент стоимости, и это оценка даже в фактическом плане - полезно, чтобы понять, куда смотреть, но не доказательство.

Чаще всего вы будете видеть такую картину: Index Seek питает соединение Nested Loops, под которым находится Key Lookup. Seek нашёл строки, а lookup для каждой из них вернулся к кластерному индексу за столбцом, которого в индексе не было. Наведите курсор на Key Lookup, и во всплывающей подсказке этот столбец будет в разделе «Output List». Добавьте его в список INCLUDE индекса, и lookup исчезнет.

Затем проверьте оценки. Наведите курсор на крайний правый оператор дорогой ветки и сравните «Estimated Number of Rows» с «Actual Number of Rows». Оценка в 1 при фактических 200 000 означает, что оптимизатор выбрал план для другой задачи - вложенный цикл там, где должно было быть хеш-соединение, слишком маленькое выделение памяти, из-за которого сортировка сбрасывается на диск. Причина почти всегда в устаревшей статистике, в предикате, который оптимизатор не может оценить, или в значении параметра, сильно отличающемся от того, под которое компилировался план.

И наконец, читайте жёлтые треугольники предупреждений. Самые частые - «Type conversion in expression may affect cardinality estimate», сброс в tempdb на Sort или Hash Match и зелёная подсказка о недостающем индексе над планом.

Почему запрос игнорирует ваш индекс#

Индекс полезен, только если предикат sargable - то есть написан так, что движок может превратить его в диапазон для seek. Вот такие - нет:

-- A function on the column: every row must be computed firstWHERE YEAR(CreatedAt) = 2026-- Rewrite as a rangeWHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01'-- A leading wildcard: no starting point in the sorted indexWHERE Email LIKE '%@example.com'-- Arithmetic on the columnWHERE Total * 1.2 > 100-- Move it to the other sideWHERE Total > 100 / 1.2

Самый коварный случай - неявное преобразование. Если Email имеет тип varchar, а приложение передаёт параметр как nvarchar - а большинство драйверов по умолчанию так и делают со строками, - SQL Server должен преобразовать одну из сторон. У nvarchar приоритет выше, поэтому преобразуется столбец, и план показывает CONVERT_IMPLICIT на столбце и scan там, где вы ждали seek. С Windows-collation оптимизатор иногда всё же может сделать seek по вычисленному диапазону, но со старыми collation SQL_ он сканирует. Приводите тип параметра в драйвере к типу столбца; почему collation меняет результат, объясняет статья collation и Unicode в SQL Server.

Другая частая причина - индексу понадобилось бы слишком много key lookup. Если запрос возвращает большую долю таблицы, seek с последующим lookup каждой строки медленнее сканирования, и оптимизатор прав, выбирая scan. Исправление - покрывающий индекс, а не подсказка индекса.

Подсказки о недостающих индексах и обслуживание индексов#

При оптимизации запросов SQL Server записывает индексы, которые ему хотелось бы иметь. Эти предложения - отправная точка, а не список дел: они игнорируют тонкости порядка столбцов, предлагают почти дубликаты друг друга и никогда не учитывают стоимость обслуживания индекса при записи:

SELECT TOP (20)    d.statement AS table_name,    d.equality_columns, d.inequality_columns, d.included_columns,    s.user_seeks, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dJOIN sys.dm_db_missing_index_groups AS g ON g.index_handle = d.index_handleJOIN sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handleORDER BY s.user_seeks * s.avg_user_impact DESC;

Обратный вопрос - какие индексы никогда не используются - решает sys.dm_db_index_usage_stats. Индекс с нулём seek, scan и lookup, но с тысячами обновлений с последнего перезапуска, тратит ваши записи и место впустую. Оба представления сбрасываются при перезапуске сервера, так что судите о них после показательного периода трафика.

Что касается фрагментации, честная позиция для небольшой базы на NVMe такова: она значит намного меньше, чем раньше. Фрагментация вредила, когда вращающиеся диски платили за каждое чтение не по порядку. Важной остаётся плотность страниц - полупустые страницы после массовых удалений означают больше страниц для чтения и больше занятой памяти. Проверяйте её через sys.dm_db_index_physical_stats и перестраивайте только то, что сильно пострадало:

ALTER INDEX IX_Orders_CustomerId_CreatedAt ON dbo.Orders REBUILD;-- or the lighter, always-online optionALTER INDEX IX_Orders_CustomerId_CreatedAt ON dbo.Orders REORGANIZE;

REBUILD WITH (ONLINE = ON) - возможность Enterprise, так что в Express перестроение блокирует таблицу на всё своё время; на таблице в несколько миллионов строк это секунды, но запланируйте его на тихий час. Статистика важнее фрагментации: UPDATE STATISTICS dbo.Orders WITH FULLSCAN; после крупной загрузки данных исправляет больше плохих планов, чем любое перестроение. Перестроение заодно обновляет статистику этого индекса, а реорганизация - нет.

Каждый индекс чего-то стоит при каждой вставке, обновлении и удалении, и в Express он засчитывается в лимит данных 10 ГБ. Пять хорошо подобранных покрывающих индексов на нагруженной таблице лучше пятнадцати одностолбцовых. Как вообще найти запросы, заслуживающие индекса, показывает статья Query Store и медленные запросы в SQL Server, а общий метод из статьи EXPLAIN ANALYZE и медленные запросы переносится из PostgreSQL почти без изменений.

FAQ#

Нужен ли индекс на каждом столбце внешнего ключа?

Обычно да. SQL Server не создаёт индекс для внешнего ключа автоматически, а без него соединения по этому столбцу сканируют таблицу, и удаление родительской строки сканирует дочернюю таблицу в поисках ссылок. Исключения - крошечные справочные таблицы и столбцы, по которым никто никогда не соединяет и не фильтрует.

Чем index seek отличается от index scan?

Seek идёт по B-дереву прямо к строкам, подходящим под ключ или диапазон. Scan читает каждую страницу индекса или таблицы от одного конца до другого. Scan таблицы на 50 строк - это нормально; scan таблицы на 5 миллионов строк ради десяти строк - это то, что нужно исправлять.

Использует ли SQL Server в одном запросе больше одного индекса на таблицу?

Иногда. Он умеет пересекать два некластерных индекса, но выбирает это редко, и результат почти всегда медленнее одного составного индекса, покрывающего предикат. Проектируйте составные индексы под реальные запросы, а не надейтесь на пересечение.

Почему запрос быстрый в SSMS и медленный из приложения?

Обычно из-за другого кэшированного плана. SSMS и большинство драйверов используют разные параметры SET, особенно ARITHABORT, поэтому получают отдельные записи в кэше планов, каждая скомпилирована под свои значения параметров. Сравните оба плана и проверьте типы параметров на неявное преобразование.

Сколько индексов - это слишком много?

Конкретного числа нет, но симптом понятен: вставки и обновления становятся медленнее, а статистика использования показывает индексы, в которые постоянно пишут и из которых никогда не читают. На таблице с интенсивной записью пять-шесть хорошо спроектированных индексов - это много. На отчётной таблице, которую в основном читают, можно и больше.