Почему оптимизатор запросов полностью игнорирует индексированные индексы просмотра?

Запуск в 2012 году Developer Edition безцелевой запрос стоит примерно в 8 раз больше, чем намеченный запрос

В то время как коэффициент 8 может показаться много вашим примером, данные довольно малы, а затраты на выбор непосредственно из базовых таблиц составляют 0.0267122 против 0.003293 для сметной стоимости с точки зрения.

Пол Уайт объясняет в своем ответе, что автоматическое индексированное сопоставление взглядов даже не будет рассмотрено, если сначала найти достаточно низкий план.

Искусственно увеличивая затраты на все задействованные таблицы

 UPDATE STATISTICS Classificator1 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 UPDATE STATISTICS Classificator2 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 UPDATE STATISTICS Classificator3 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 UPDATE STATISTICS Classificator4 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 UPDATE STATISTICS Classificator5 WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 UPDATE STATISTICS Entity WITH ROWCOUNT = 60000000, PAGECOUNT = 10000000 

Увеличивает стоимость плана базовой таблицы до 29122.6

Теперь вы должны увидеть совпадение вида (в выпусках Enterprise / Developer / Evaluation), если вы явно не намекаете иначе.

 SELECT * FROM EntityView; SELECT * FROM EntityView OPTION (EXPAND VIEWS) 

tl; dr ответ: если вы не укажете NOEXPAND, оптимизатор запросов не имеет представления, что вы отправляете простой выбор из представления. Это должно было бы соответствовать расширению вашего запроса (что все, что он видит) с некоторым индексом просмотра. Наверное, не стоит беспокоиться, когда это пятиступенчатое соединение с кучей отливок.

Сравнение индексов с запросом является сложной проблемой, и я считаю, что ваше представление слишком сложно, чтобы механизм запросов соответствовал индексу. Рассмотрим этот один из ваших запросов:

 SELECT ID, Code1 FROM EntityView Where Code1 > 'NR%'; 

Для вас очевидно, что это может использовать индекс просмотра, но это не тот запрос, который видит механизм запросов. Представления автоматически расширяются, если вы не укажете NOEXPAND, так что это то, что касается механизма запроса:

 SELECT ID, Code1 FROM ( SELECT e.ID, 'NR'+CAST(c1.CODE as nvarchar(11)) as Code1, 'NR'+CAST(c2.CODE as nvarchar(11)) as Code2, 'NR'+CAST(c3.CODE as nvarchar(11)) as Code3, 'NR'+CAST(c4.CODE as nvarchar(11)) as Code4, 'NR'+CAST(c5.CODE as nvarchar(11)) as Code5 FROM dbo.Entity e inner join dbo.Classificator1 c1 on e.ID = c1.ID inner join dbo.Classificator2 c2 on e.ID = c2.ID inner join dbo.Classificator3 c3 on e.ID = c3.ID inner join dbo.Classificator4 c4 on e.ID = c4.ID inner join dbo.Classificator5 c5 on e.ID = c5.ID; ) AS V; 

Механизм запросов видит этот сложный запрос и содержит информацию (но, вероятно, не SQL-определения), которые описывают индексы представления, которые были определены. Учитывая, что этот запрос и индексы представлений имеют множественные объединения и отливки, сопоставление является трудным заданием.

Имейте в виду, что вы знаете, что соединения и совпадения идентичны в этом запросе и индексы представления, но процессор запросов этого не знает. Он обрабатывает этот запрос так же, как если бы он присоединился к пяти экземплярам Classificator3, или если один из столбцов был «NQ» + CAST (c2.CODE как varchar (12)). Средство просмотра индекса (при условии, что оно сделало любую попытку сопоставить этот сложный запрос) должно было бы сопоставить каждую деталь этого запроса с деталями индексов представления в соответствующих таблицах.

У механизма запроса есть цель №1, чтобы выяснить способ эффективного выполнения запроса. Вероятно, он не предназначен для много времени, пытаясь сопоставить каждую деталь пятистороннего соединения и CASTs с индексом представления.

Если я должен был догадаться, я подозреваю, что совпадение индексов представления видит, что столбцы результатов запроса не являются даже столбцами любой базовой таблицы (из-за CAST) и просто не пытаются что-либо попробовать. Добавлено : я ошибаюсь. Я просто попробовал предложение Мартина по обновлению статистики, чтобы сделать запрос дорогим, и индекс просмотра был сопоставлен для некоторых из этих запросов без NOEXPAND. Совместитель вида умнее, чем я думал! Таким образом, проблема заключается в том, что совпадение вида, вероятно, пытается сложнее сопоставить сложный запрос, если его стоимость очень высока.

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

 SELECT ID, Code1 FROM EntityView Where Code1 > 'NR%'; 

и тогда сразу же видно, что индексный признак имеет полезный индекс.

(Примечание: ваш код SQL Fiddle содержит все 5 ссылок на внешние ключи в одну и ту же таблицу, что, вероятно, не то, что вы хотите.)

Используйте подсказку WITH (NOExpand), если вы находитесь на сервере SQL Server Enterprise

Ваш запрос будет SELECT * FROM EntityView with (noexpand)