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

Автор: Luca Biondi, SQL Server Performance Tuning: How Filtered Indexes Drastically Improve Query Performance

Сегодня мы погружаемся в мощную технику настройки производительности SQL Server, которая может кардинально сократить логические чтения, оптимизировать планы выполнения и значительно улучшить производительность запросов в реальных производственных средах.

Если вы работаете с Microsoft SQL Server и боретесь с медленными запросами, высокими логическими чтениями или неэффективными планами выполнения, эта продвинутая техника настройки производительности SQL Server может кардинально улучшить производительность запросов.

В этой статье мы проанализируем, как фильтрованные индексы в SQL Server могут уменьшить ввод-вывод, оптимизировать планы выполнения и значительно повысить производительность OLTP.

Что такое фильтрованный индекс в SQL Server?

Фильтрованный индекс — это некластеризованный индекс, который включает только подмножество строк, определяемое предложением WHERE.

В отличие от традиционных индексов, которые включают все строки таблицы, фильтрованные индексы:

• Уменьшают размер индекса
• Повышают точность статистики
• Сокращают логические чтения
• Улучшают оценку кардинальности
• Оптимизируют планы выполнения SQL Server

Это делает их чрезвычайно мощными в сценариях настройки производительности SQL Server.

Реальная проблема производительности SQL Server

Рассмотрим большую таблицу Orders с 10 миллионами строк:

• 95% → Status = 'Closed'
• 5% → Status = 'Open'

Ваше приложение постоянно выполняет этот запрос:

SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE Status = 'Open'
AND OrderDate >= '2026-01-01';

Проблемы производительности

• Высокие логические чтения
• Большие сканирования некластеризованного индекса
• Неоптимальные планы выполнения
• Плохая оценка кардинальности из-за перекошенных данных

Типичные показатели производительности:

Логические чтения: 125 000
Время выполнения: 2.8 секунды

Как улучшить производительность запросов SQL Server с помощью фильтрованного индекса

Вместо индексирования всей таблицы создайте фильтрованный индекс, нацеленный только на активные строки:

CREATE NONCLUSTERED INDEX IX_Orders_Open
ON dbo.Orders (OrderDate)
INCLUDE (OrderID, CustomerID)
WHERE Status = 'Open';

Почему это улучшает производительность SQL Server

• Индекс содержит только 5% строк
• Статистика строится только для релевантных данных
• Не требуется поиск по ключу (покрывающий индекс)
• Значительно меньше страниц читается с диска

Сравнение планов выполнения: До и После

До фильтрованного индекса

• Поиск по индексу + поиск по ключу
• Высокие логические чтения
• Большее распределение памяти

После фильтрованного индекса

• Чистый поиск по индексу
• Нет поиска по ключу
• Точная оценка кардинальности
• Более низкое использование процессора

Новые показатели производительности:

Логические чтения: 4 200
Время выполнения: 0.15 секунды

Это сокращение логических чтений на 96% — огромное улучшение производительности SQL Server.

Воспроизводимый тестовый скрипт SQL Server

CREATE TABLE dbo.Orders
(
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Status VARCHAR(10)
);

WITH Numbers AS (
    SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.objects a CROSS JOIN sys.objects b
)
INSERT INTO dbo.Orders (CustomerID, OrderDate, Status)
SELECT 
    ABS(CHECKSUM(NEWID())) % 10000,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
    CASE 
        WHEN n % 20 = 0 THEN 'Open'
        ELSE 'Closed'
    END
FROM Numbers;

Включите подробные показатели производительности:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Когда использовать фильтрованные индексы для настройки производительности SQL Server

Фильтрованные индексы идеальны, когда:

• У вас перекошенное распределение данных
• Вы используете шаблоны "мягкого удаления" (IsDeleted = 0)
• Вы часто фильтруете по столбцам статуса
• У вас есть столбцы, допускающие NULL, с большим количеством NULL-значений
• Вы хотите сократить логические чтения в OLTP-нагрузках

Важные соображения для производственных сред

• Предикат фильтра должен соответствовать предикату запроса
• Sniffing параметров может препятствовать использованию индекса
• Регулярно отслеживайте использование индексов
• Избегайте создания избыточных пересекающихся индексов

SELECT *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID();

Продвинутый совет по производительности SQL Server: Sniffing параметров

Если ваша хранимая процедура использует:

WHERE Status = @Status

SQL Server может не всегда выбирать фильтрованный индекс.

Возможные решения включают:

• OPTION (RECOMPILE)
• Отдельные процедуры для часто используемых значений
• Оптимизированный динамический SQL

Заключительные мысли об оптимизации производительности SQL Server

Фильтрованные индексы — одна из самых мощных и недоиспользуемых техник настройки производительности SQL Server.

Вместо добавления большего количества индексов добавляйте более умные индексы.

Нацеливаясь только на данные, которые действительно используют ваши запросы, вы сокращаете ввод-вывод, улучшаете планы выполнения и кардинально повышаете производительность SQL Server.