Фильтрованные индексы SQL Server для очередей задач

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

Значение имеет размер активного набора. Пятьдесят миллионов строк с двумястами ожидающими задачами принципиально отличаются от двадцати миллионов ожидающих после аварии. Проверяйте обычную работу и восстановление после простоя. Хороший результат на почти пустой очереди не предсказывает поведение плана при большом накопленном отставании.

Точно выразить условие отбора

В примере числовой ноль обозначает ожидание, единица - завершение. Индекс хранит только ожидающие задачи, упорядоченные по времени создания и идентификатору. CustomerId включен для результата, но не участвует в поиске и сортировке. Ожидаемые записи - 2 и 3. Небольшой набор демонстрирует корректность, а не достоверное ускорение.

CREATE TABLE #Work
(
    WorkId bigint NOT NULL PRIMARY KEY,
    Status tinyint NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    CustomerId int NOT NULL
);
INSERT #Work VALUES
(1,1,'2024-01-01',10),(2,0,'2024-01-02',20),
(3,0,'2024-01-03',10),(4,1,'2024-01-04',30);

CREATE INDEX IX_Work_Pending
ON #Work(CreatedAt, WorkId)
INCLUDE(CustomerId)
WHERE Status = 0;

SELECT TOP (20) WorkId, CreatedAt, CustomerId
FROM #Work
WHERE Status = 0
ORDER BY CreatedAt, WorkId;

SELECT i.name, p.row_count, p.used_page_count
FROM tempdb.sys.indexes AS i
JOIN tempdb.sys.dm_db_partition_stats AS p
  ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.object_id=OBJECT_ID('tempdb..#Work');

DROP TABLE #Work;

Для реальной таблицы сравните количество страниц индекса, логические чтения и прочитанные строки при нормальной и максимальной глубине очереди. Индекс только по статусу может оставить сортировку или многочисленные дополнительные обращения к таблице. Столбцы порядка лучше расположить в ключе, а набор включенных ограничить. Большой JSON-документ обычно можно получить отдельно после выбора нескольких идентификаторов.

Важны параметры SET. Для работы с фильтрованными индексами проверьте включенные ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL, ARITHABORT и выключенный NUMERIC_ROUNDABORT. Если запись работает у администратора, но падает из приложения, сравните параметры именно прикладного подключения.

Почему подходящий индекс иногда не используется

Оптимизатор должен доказать, что индекс содержит все возможные строки результата. Повторно используемый запрос Status = @Status обычно не может полагаться на индекс только для Status = 0. Тот же план позднее может потребоваться для завершенных записей. Успешный одиночный запуск с нулем не обеспечивает правильность для других значений.

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

Фильтрованная статистика описывает подмножество и может улучшить оценки. При этом небольшой, но быстро меняющийся активный набор требует наблюдения. Сопоставляйте оцененные и фактические строки, учитывайте частоту переходов состояний. Статистика всей таблицы не обязана хорошо описывать ожидающие задачи. Для условия IS NULL дополнительно проверьте, требуется ли включить фильтруемый столбец для нужного способа доступа.

Не путать быстрый поиск с выдачей задачи одному обработчику

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

Показанный SELECT ничего не резервирует. Два обработчика могут получить одинаковые идентификаторы до изменения статуса. Производственная очередь требует транзакционной операции получения, указания владельца или срока аренды, повторных попыток и правила восстановления после падения исполнителя. Подсказки READPAST и блокировки обновления зависят от уровня изоляции, поэтому добавлять их механически неправильно.

Например, если обработчик резервирует задачу, вызывает внешний сервис и падает до фиксации результата, индекс не определяет, нужно ли повторять внешний вызов. Здесь важны идемпотентность операции и возможность проверить ее исход. Быстрая выдача одной задачи двум обработчикам лишь ускорит появление дубликатов, если эта часть протокола не продумана.

Проверяйте накопление во время аварии, постоянно ошибочные задачи в начале очереди и скорость выхода записей из активного набора. Маленький индекс не компенсирует обработчик, который никогда не завершает работу. Он также не заменяет политику удаления старой истории.

Практический критерий - очередь должна сокращаться, не вытесняя обычные запросы из ресурсов базы. Хороший фильтрованный индекс делает поиск нужного подмножества дешевым, оставляя явными правила резервирования, повторной обработки и хранения данных.

Техническая документация: Microsoft Learn: Filtered indexes · Microsoft Learn: Index design.