Какие побочные эффекты возникают при добавлении в индекс большого количества столбцов

Что проверяют

Понимаете ли вы, что индекс это отдельная структура со своей ценой, и чем ширина ключа отличается от покрытия запроса. Следом обычно спрашивают, чем ключевые колонки отличаются от INCLUDE и в каком порядке ставить колонки в составном индексе.

Как отвечать

Для C# стека чаще всего речь о SQL Server, так что опирайтесь на него:

• медленнее запись: каждый INSERT и DELETE пишет в индекс, а UPDATE любой его колонки меняет и запись индекса. Чем больше колонок, тем чаще обновление задевает индекс и тем больше журнал транзакций;
• больше места и памяти: широкая запись означает меньше строк на странице в 8 КБ, больше страниц и уровней дерева, больше чтений и давление на buffer pool. Индекс на половину колонок таблицы по сути её копия;
• фрагментация и обслуживание: широкие ключи чаще дают page split, а REBUILD и обновление статистики идут дольше;
• лимиты: ключ некластерного индекса ограничен 1700 байтами, кластерного 900, а nvarchar(max) ключевой колонкой быть не может;
• широкий кластерный ключ: он копируется в каждый некластерный индекс как указатель на строку и раздувает их все;
• мало пользы для поиска: составной индекс работает слева направо, и если запрос не фильтрует по первой колонке, остальные не помогут. Гистограмма статистики строится только по первой колонке.

Правильный ответ на желание «покрыть запрос»: в ключ класть колонки из WHERE, JOIN и ORDER BY, а те, что нужны только в SELECT, выносить в INCLUDE. Они лежат лишь на листовом уровне и не участвуют в сортировке. В EF Core для этого есть HasIndex(...).IncludeProperties(...).

Где спотыкаются

Путают ключевые и включённые колонки и считают INCLUDE бесплатным. Он тоже увеличивает индекс и замедляет запись, просто меньше.

Не думают о порядке колонок: ставят первой малоселективную колонку или ту, по которой фильтруют редко.

Добавляют широкий индекс под один медленный запрос, не посмотрев план и существующие индексы. В итоге появляются дубли, пересекающиеся на три четверти.