Отфильтрованное состояние индекса игнорируется оптимизатором

Предположим, у меня работает веб-сайт, на котором показаны забавные фотографии кошек. У меня есть таблица под названием CatPictures со столбцами Filename, Awesomeness и DeletionDate и следующий индекс:

create nonclustered index CatsByAwesomeness
on CatPictures (Awesomeness) 
include (Filename)
where DeletionDate is null

Мой главный запрос:

select Filename from CatPictures where DeletionDate is null and Awesomeness > 10

Я, как человек, знаю, что указанный выше индекс - все, что требуется SQL Server, потому что условие фильтра индекса уже обеспечивает DeletionDate is null часть.

SQL Server, однако, не знает этого; план выполнения моего запроса не будет использовать мой индекс:

Даже если добавить подсказку индекса, он будет по-прежнему явно проверять DeletionDate, просматривая фактические данные таблицы:

(и, кроме того, жалуйтесь на отсутствующий индекс, который будет включать DeletionDate).

Конечно, я мог бы

include (Filename, DeletionDate)

и он будет работать:

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

Есть ли способ заставить SQL Server знать, что условие фильтра уже выполняет проверку DeletionDate?

Ответ 1

Нет, не в настоящее время.

См. этот элемент подключения. Он закрыт, как не будет исправлен. (Или этот для конкретного случая IS NULL)

Элемент подключения обеспечивает обходное решение, показанное ниже.

Отправлено RichardB CFCU 29/09/2011 в 9:15

Обходной путь заключается в INCLUDE столбце, который фильтруется.

Пример:
CREATE NONCLUSTERED INDEX [idx_FilteredKey1] ON [dbo].[TABLE] 
(
    [TABLE_ID] ASC,
    [TABLE_ID2] ASC
)
INCLUDE ( [REMOVAL_TIMESTAMP]) --explicitly include the column here
WHERE ([REMOVAL_TIMESTAMP] IS NULL)

Ответ 2

Есть ли способ заставить SQL Server знать, что условие фильтра уже выполнив задачу проверки DeletionDate?

Нет.

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

Есть несколько веских причин, по которым я могу понять, как это работает.

Что улучшается:

Хранение. Индекс содержит только ключи, соответствующие условию фильтрации
Производительность. Вышесказанное из вышесказанного. Меньше писать и меньше страниц = более быстрое извлечение

Что он не делает:

Изменить механизм запроса в корне

Объединяя их, учитывая, что SQL Server - сильно конвейерный, многопроцессорный parallelism способный зверь, мы получаем следующее поведение при обслуживании запроса:

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

Потока условия в отношении отфильтрованного индекса в "ядро" оптимизатора запросов я подозреваю, что это будет намного больше, чем оставить его на шаге 1.

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

Ответ 3

Просто обнаружил, что "разрыв в функциональности", действительно печально, что отфильтрованные индексы игнорируются оптимизатором. Я думаю, что я попытаюсь использовать индексированные представления для этого, взгляните на эту статью

http://www.sqlperformance.com/2013/04/t-sql-queries/optimizer-limitations-with-filtered-indexes