Индексирование таблиц и хранимые процедуры

Для лекции используйте файл  Test_Sniffing.sql (необходимо распаковать).

Оптимизация запросов с помощью индексов и управление планами выполнения

Подготовка тестовой базы и таблиц

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

CustomerCategory – категории покупателей:
• CustomerCategoryID CHAR(1) PRIMARY KEY (значения 'A' и 'B');
• CategoryDescription – текстовое описание.

Customers – покупатели:
• CustomerID INT IDENTITY(1,1) PRIMARY KEY (автоинкремент);
• CustomerName NOT NULL;
• CustomerAddress NOT NULL;
• State – двухсимвольный код штата;
• CustomerCategoryID CHAR(1) FOREIGN KEY REFERENCES CustomerCategory(CustomerCategoryID);
• LastBuyDate – дата последней покупки.

Первичный ключ автоматически создаёт кластеризованный индекс (clustered index).

Индексы: назначение и виды

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

• Кластеризованный индекс (clustered index) – определяет физический порядок строк; создаётся автоматически для первичного ключа. Хранит сами данные.
• Некластеризованный индекс (nonclustered index) – создаётся вручную на любых полях; хранит значения ключа и ссылки на строки данных.

На таблице Customers создаётся некластеризованный индекс по столбцу CustomerCategoryID:

sql
CREATE INDEX IX_Customers_Category ON Customers(CustomerCategoryID);

Этот индекс позволит быстро находить всех покупателей конкретной категории без полного перебора таблицы.

Компромисс: скорость чтения против накладных расходов на изменение

Преимущества индексов:
• выборка (SELECT) выполняется значительно быстрее.
Недостатки:
• индексы потребляют много памяти (иногда больше, чем сама таблица);
• любая вставка, обновление или удаление строки требует перестроения индексов, что замедляет операции изменения данных.

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

Пример с перекосом данных

В таблицу CustomerCategory вставляются две строки: категория 'A' («Первая категория») и категория 'B' («Вторая категория»).

В таблицу Customers добавляется один покупатель категории 'B' и 15 000 одинаковых покупателей категории 'A'. Таким образом, 15 000 клиентов принадлежат категории A, и только один – категории B.

Планы выполнения для разных значений фильтра

Выполняются два запроса (перед каждым очищается кэш и оперативная память):

sql
-- Запрос 1: все покупатели категории A
SELECT c.CustomerName, c.LastBuyDate, cat.CategoryDescription
FROM Customers c
INNER JOIN CustomerCategory cat ON c.CustomerCategoryID = cat.CustomerCategoryID
WHERE c.CustomerCategoryID = 'A';

-- Запрос 2: все покупатели категории B
SELECT c.CustomerName, c.LastBuyDate, cat.CategoryDescription
FROM Customers c
INNER JOIN CustomerCategory cat ON c.CustomerCategoryID = cat.CustomerCategoryID
WHERE c.CustomerCategoryID = 'B';

План для A:
Сканирование кластеризованного индекса таблицы Customers возвращает все строки (оба запрошенных поля). Кластеризованный индекс таблицы CustomerCategory используется для поиска категории 'A'. Затем выполняется соединение вложенными циклами (Nested Loops Join). Фактическая фильтрация происходит уже при соединении. Индекс по CustomerCategoryID не используется, потому что значение 'A' охватывает почти всю таблицу и оптимизатор выбирает полное сканирование.

План для B:
Здесь активируется некластеризованный индекс IX_Customers_Category. Он мгновенно находит единственный CustomerID со значением 'B'. Затем по кластеризованному индексу извлекаются остальные поля (CustomerName, LastBuyDate) и происходит соединение с таблицей категорий. Такой план эффективен именно потому, что целевое множество очень мало по сравнению с общим объёмом таблицы.

Parameter sniffing в хранимых процедурах

Создаётся хранимая процедура без опции перекомпиляции:

sql
CREATE PROCEDURE TestSniffing
@CustomerCategoryID CHAR(1)
AS
BEGIN
SELECT c.CustomerName, c.LastBuyDate, cat.CategoryDescription
FROM Customers c
INNER JOIN CustomerCategory cat ON c.CustomerCategoryID = cat.CustomerCategoryID
WHERE c.CustomerCategoryID = @CustomerCategoryID;
END;

Сначала вызывается процедура с параметром 'A', затем – с параметром 'B'. Без очистки кэша между вызовами оба выполнения используют план, скомпилированный для 'A' (сканирование кластеризованного индекса). Причина – parameter sniffing: SQL Server при первом запуске строит план под переданное значение и кэширует его. Последующие вызовы с другими значениями применяют тот же план, даже если он неоптимален. Очистка кэша перед запуском восстанавливает индивидуальные планы для каждого значения.

Меняя порядок запуска (сначала 'B', затем 'A'), можно наблюдать обратную картину: план для 'B' (с некластеризованным индексом) будет использован и для 'A'.

Управление компиляцией: WITH RECOMPILE

Создаётся новая процедура с опцией WITH RECOMPILE:

sql
CREATE PROCEDURE TestSniffingRecompile
@CustomerCategoryID CHAR(1)
WITH RECOMPILE
AS
BEGIN
SELECT c.CustomerName, c.LastBuyDate, cat.CategoryDescription
FROM Customers c
INNER JOIN CustomerCategory cat ON c.CustomerCategoryID = cat.CustomerCategoryID
WHERE c.CustomerCategoryID = @CustomerCategoryID;
END;

При каждом вызове процедура перекомпилируется, создавая план, оптимальный для конкретного значения параметра. Теперь последовательность 'A' → 'B' даёт для 'B' некластеризованный индекс, а обратная последовательность восстанавливает кластеризованное сканирование для 'A'. План больше не «прилипает».

Опция OPTION (RECOMPILE) на уровне запроса

Можно управлять перекомпиляцией не на уровне всей процедуры, а внутри отдельного запроса:

sql
SELECT ...
WHERE c.CustomerCategoryID = @CustomerCategoryID
OPTION (RECOMPILE);

Результат аналогичен WITH RECOMPILE, но решение о перекомпиляции принимается точечно. Оптимизатор заново строит план для этого запроса, ориентируясь на текущее значение параметра.

Влияние внутренней переменной

Создаётся процедура, в которой значение параметра присваивается локальной переменной:

sql
CREATE PROCEDURE TestSniffingLocalVar
@CustomerCategoryID CHAR(1)
AS
BEGIN
DECLARE @LocalCategoryID CHAR(1);
SET @LocalCategoryID = @CustomerCategoryID;

SELECT ...
WHERE c.CustomerCategoryID = @LocalCategoryID;
END;

При таком подходе SQL Server не знает конкретного значения переменной на этапе компиляции и использует усреднённую статистику. В результате план всегда остаётся одинаковым (кластеризованное сканирование), независимо от переданного параметра, и оптимизация для редких значений не срабатывает. Внутренняя переменная полностью нивелирует parameter sniffing, но ценой потери потенциального выигрыша.

Итоги и практические рекомендации

SQL Server предоставляет встроенные подсказки о недостающих индексах (missing index), вычисляя ожидаемый прирост производительности. Однако задача построения индексов лежит на администраторе базы данных. Бизнес-аналитику достаточно понимать, что один и тот же запрос может выполняться с разной скоростью в зависимости от индексов и параметризации. Осознание феноменов parameter sniffing, перекомпиляции и влияния локальных переменных помогает интерпретировать непредсказуемое поведение отчётов и взаимодействовать со специалистами по оптимизации.

Краткие итоги

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

Главная инженерная мысль – индексы должны создаваться адресно под самые частые запросы, а не «на всякий случай». Когда распределение значений в столбце неравномерно, важность индекса возрастает для фильтрации редких ключей. Это наглядно проявляется в ситуации с перекосом данных: если почти все строки принадлежат одной категории, полное сканирование для неё эффективнее, а для миноритарной категории некластеризованный индекс резко сокращает объём обрабатываемых данных. Понимание этой асимметрии критично для настройки производительности.

Не менее важны тонкости параметризации. Хранимые процедуры без управления компиляцией подвержены parameter sniffing – явлению, когда план, созданный для первого фактического значения, фиксируется и применяется ко всем последующим вызовам. При изменении характера данных это приводит к деградации скорости. Инструменты WITH RECOMPILE и OPTION (RECOMPILE) дают контроль над этим поведением, инициируя перекомпиляцию под каждое конкретное значение, восстанавливая оптимальность планов. Однако здесь возникает обратная сторона – частые перекомпиляции создают дополнительную нагрузку на процессор, что тоже требует взвешенного подхода.

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

Практическое применение этих знаний выходит за рамки административных задач. Аналитик, сталкиваясь с нестабильным временем выполнения отчётов, может выявить причину в параметризации или отсутствии нужного индекса. Понимание рекомендаций missing index и умение читать планы запросов помогают формулировать требования к администраторам более предметно. В конечном счёте осознание внутренних механизмов работы SQL Server превращает интуитивные догадки о производительности в управляемый и прогнозируемый процесс настройки.