Почему широкий кластерный индекс обходится дороже, чем вы думаете

Автор: Steve Stedman, Why a Wide Clustered Index Costs More Than You Think

Ночное задание обслуживания индексов раньше завершалось до прихода сотрудников. Теперь оно выполняется долго, и никто не добавил столько строк, чтобы объяснить лишний час. Где-то в этой базе данных широкий кластерный индекс тихо делает каждое перестроение дороже, чем оно должно быть.

Почему широкий кластерный индекс — это проблема в SQL Server? Широкий кластерный индекс обходится дорого, потому что SQL Server копирует ключ кластеризации в каждую строку каждого некластерного индекса таблицы, а не только в саму базовую таблицу. Реальная стоимость — это ширина ключа, умноженная на количество строк и на то, сколько некластерных индексов его несут, а не просто количество байт.

Инстинкт подсказывает открыть sys.indexes, отсортировать по длине ключа и обвинить тот ключ кластеризации, который шире всех. Одно это число лжёт. Широкий ключ в маленькой таблице без некластерных индексов почти ничего не  стоит. 

Реальная стоимость широкого кластерного индекса

Переносимый вес (Carried Weight), а не ширина ключа, определяет порядок.

Big Clustered Indexes — один из отчётов в Database Health Monitor. Он выполняется на серверах, и требуется около минуты, чтобы открыть этот же экран.

Переносимый вес — это ширина ключа × количество строк × количество некластерных индексов, и это полностью меняет картину. Ключ в сто байт в справочной таблице с двумя тысячами строк — это шум. Ключ кластеризации типа uniqueidentifier в шестнадцать байт в таблице со ста миллионами строк и девятью некластерными индексами под ней — это не шум, потому что SQL Server записывает этот ключ в каждый из этих девяти индексов, в каждую отдельную строку. Одна лишь ширина не может отличить одно от другого. Переносимый вес может.

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

Что на самом деле показывает таблица

Откройте таблицу, и четыре столбца значат больше остальных.

Столбец	Что он говорит вам
Переносимый вес (Carried Weight)	Ширина ключа, умноженная на количество строк и на количество некластерных индексов, — сам рейтинг, изображённый в виде полосы.
Ширина ключа (Key Width)	Сколько байт занимает сам ключ кластеризации.
Количество некластерных индексов (NC Indexes)	Сколько некластерных индексов копируют этот ключ — легко упустить, и это умножает всё остальное в строке.
Вердикт (Verdict)	Стоит ли вообще беспокоиться об этой ширине.

Отчёт по умолчанию применяет фильтр шума не просто так. Широкий ключ в таблице с тысячей строк — это не находка, достойная вашего времени, поэтому повышение минимального количества строк кнопками размера на панели инструментов — или сужение до Top 25 по переносимому весу — оставляет в списке только таблицы, которые действительно стоит перестраивать.

Три формы повторяются снова и снова:

• uniqueidentifier во главе большой, густо проиндексированной таблицы — классический случай и самый дорогой.
• Четыре или пять столбцов, сшитых вместе в один составной ключ, обычно выбранных ради уникальности, а не ради того, как таблица запрашивается.
• Широкий ключ в таблице, которую больше никто не индексировал — технически широкий, но переносимый вес остаётся низким, потому что умножать не на что.

Что с этим делать

Сначала читайте переносимый вес, а не ширину ключа — это число, которое действительно отражает стоимость. Затем посмотрите, из чего состоит ключ: uniqueidentifier или составной ключ из четырёх-пяти столбцов объясняет большую часть ширины, которую вы найдёте. Проверьте, сколько некластерных индексов его копируют, потому что это количество и есть настоящий множитель. Более узкий суррогатный ключ — одно из исправлений, но замена ключа кластеризации тянет за собой перестроение базовой таблицы и каждого некластерного индекса поверх неё, так что взвешивайте эту стоимость честно. Часто более дешёвый ход находится по другую сторону уравнения: удаление неиспользуемых индексов, несущих вес, вместо того чтобы трогать ключ, который его создал.

Что проверить на своём сервере

• Найдите свои самые широкие ключи кластеризации и проверьте, какие из них находятся в действительно больших таблицах.
• Посчитайте некластерные индексы в каждой из этих таблиц, поскольку это число напрямую умножает стоимость.
• Проверьте, является ли широкий ключ uniqueidentifier или составным из нескольких столбцов — две обычные причины.
• Поищите неиспользуемые или дублирующие некластерные индексы у худших виновников, прежде чем рассматривать более узкий ключ.