почему оптимизатор запросов игнорирует индексированное представление / Microsoft SQL Server

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / почему оптимизатор запросов игнорирует индексированное представление

20 сообщений из 20, страница 1 из 1

почему оптимизатор запросов игнорирует индексированное представление

#39961389

Mikhail__174

Гость

Есть запрос, который возвращает некоторую агрегацию по таблице заказов:

Код: sql

1.
2.
3.
4.
5.

SELECT
    YEAR(InvoiceDate) AS InvoiceYear,
    COUNT(InvoiceID) AS InvoiceCount
FROM Sales.Invoices
GROUP BY YEAR(InvoiceDate);

..и наблюдаю следующий план запроса:
я бы хотел избавиться от тяжелого оператора Hash Match с помощью индексированного представления

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

CREATE or alter VIEW Sales.vSalesByYear
WITH SCHEMABINDING
AS
SELECT
    YEAR(InvoiceDate) AS InvoiceYear,
    COUNT_BIG(*) AS InvoiceCount
FROM Sales.Invoices
GROUP BY YEAR(InvoiceDate);
GO
CREATE UNIQUE CLUSTERED INDEX idx_vSalesByYear
    ON Sales.vSalesByYear (InvoiceYear);
GO

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

Если напрямую обращаться к представлению, то план, который я хочу увидеть, появляется только после применения хинта (noexpand):

Код: sql

1.
2.
3.

select 
  InvoiceYear
, InvoiceCount from Sales.vSalesByYear with (noexpand)

...

Рейтинг:

0 / 0

24.05.2020, 18:53

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961390

Mikhail__174

Гость

...

Рейтинг:

0 / 0

24.05.2020, 18:54

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961392

Mikhail__174

Гость

с картинками беда какая-то. не знаю как загрузить правильно

...

Рейтинг:

0 / 0

24.05.2020, 18:55

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961399

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

Mikhail__174

Если напрямую обращаться к представлению, то план, который я хочу увидеть, появляется только после применения хинта (noexpand):
[SRC sql][/SRC]

BOLДо SQL Server 2016 (13.x); SP1 автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016. https://docs.microsoft.com/ru-ru/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15#using-noexpand

...

Рейтинг:

0 / 0

24.05.2020, 19:16

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961405

Mikhail__174

Гость

пробовал тут : 2017 Microsoft Corporation Standard Edition (64-bit)
и тут : 2017 Microsoft Corporation Express Edition (64-bit)
везде всё одинаково :(

...

Рейтинг:

0 / 0

24.05.2020, 19:28

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961416

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

Mikhail__174

пробовал тут : 2017 Microsoft Corporation Standard Edition (64-bit)
и тут : 2017 Microsoft Corporation Express Edition (64-bit)
везде всё одинаково :(

А уровень совместимости БД какой?

...

Рейтинг:

0 / 0

24.05.2020, 20:11

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961429

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

alexeyvg

Mikhail__174

пробовал тут : 2017 Microsoft Corporation Standard Edition (64-bit)
и тут : 2017 Microsoft Corporation Express Edition (64-bit)
везде всё одинаково :(

А уровень совместимости БД какой?

И по ссылке на хелп, которую я дал, написано ещё несколько необходимых параметров для подстановки.

...

Рейтинг:

0 / 0

24.05.2020, 21:16

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961524

msLex

Участник

Сообщения: 8 523

Рейтинг: 0 / 0

А если в запросе написать COUNT_BIG вместо COUNT?

...

Рейтинг:

0 / 0

25.05.2020, 08:46

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961615

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

Mikhail__174,

не пойму, что смущает в использовании with (noexpand)? Насколько я читал, в первую очередь оптимизатор использует исходные таблицы представления.
В справке недвусмысленно указано:
Чтобы заставить оптимизатор использовать индекс для индексированного представления, следует указать параметр NOEXPAND.

Вы ознакомились со справкой по ссылке которая была выше?

...

Рейтинг:

0 / 0

25.05.2020, 11:38

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961633

Mikhail__174

Гость

alexeyvg,авторА уровень совместимости БД какой?

130. Но он тут не при чём. То, что я хотел, работает только в enterprise версии

...

Рейтинг:

0 / 0

25.05.2020, 12:15

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961635

Mikhail__174

Гость

Владислав Колосов, авторВы ознакомились со справкой по ссылке которая была выше?
Ознакомился.
авторто смущает в использовании with (noexpand)
То, что его можно просто забыть указать.

Моя проблема решена, SQL server умеет использовать индексы вьюшки и без хинта (noexpand), правда только в enterprise версии

...

Рейтинг:

0 / 0

25.05.2020, 12:20

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961636

Mikhail__174

Гость

msLex, не помогло

...

Рейтинг:

0 / 0

25.05.2020, 12:21

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961650

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Mikhail__174

То, что его можно просто забыть указать.

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

...

Рейтинг:

0 / 0

25.05.2020, 12:48

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961653

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

Mikhail__174

пробовал тут : 2017 Microsoft Corporation Standard Edition (64-bit)
и тут : 2017 Microsoft Corporation Express Edition (64-bit)
везде всё одинаково :(

А полнгая версия какая?

Возможно, это поменяли в SP1 для 2016, но в 2017 к релизу не успели, и это сделано в каком то CU

Mikhail__174

Моя проблема решена, SQL server умеет использовать индексы вьюшки и без хинта (noexpand), правда только в enterprise версии

Э то само собой, так было изначально.

Но непонятно, почему сиквел работает не так, как описано по указанной вами ссылке.

...

Рейтинг:

0 / 0

25.05.2020, 12:56

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961661

Mikhail__174

Гость

alexeyvg, авторА полная версия какая?
у меня уже три версии, пока я разбираюсь с этим примером :)

"Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) "

"Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) "

"Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) "

авторНо непонятно, почему сиквел работает не так, как описано по указанной вами ссылке

Почему, всё работает верно. "Automatic use of indexed view by query optimizer" есть только у Enterprise версии, т.е. в Standart и в Express можно обратиться только к представлению и только с хинтом (noexpand), чтобы задействовать индекс материализованной вьюшки.
В Enterprise QO сам "понимает",что при обращении к таблицам, которые участвуют в материализованной вьюшке, и что индекс вьюшки покрывает запрос, и берёт индекс вьюшки, без обращения к самой вьюшке вовсе.

...

Рейтинг:

0 / 0

25.05.2020, 13:13

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961663

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

alexeyvg

Mikhail__174Моя проблема решена, SQL server умеет использовать индексы вьюшки и без хинта (noexpand), правда только в enterprise версии

Э то само собой, так было изначально.

Но непонятно, почему сиквел работает не так, как описано по указанной вами ссылке.А, посмотрел не на ту строку; без хинта действительно только Энтерпрайз.

Странно, почему тут написано, что автоматическое раскрытие работало только в энтерпрайзе до 2016 SP1
Возможно, косяк перевода...

UPD

Mikhail__174

Почему, всё работает верно. "Automatic use of indexed view by query optimizer" есть только у Enterprise версии

Странно.

Вот, и в английской версии доки написано:
автор Prior to SQL Server 2016 (13.x) SP1, automatic use of an indexed view by the query optimizer is supported only in specific editions of SQL Server.

...

Рейтинг:

0 / 0

25.05.2020, 13:16

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961670

Mikhail__174

Гость

alexeyvg, авторСтранно
Ага, как то витиевато написано,

"До SQL Server 2016 (13.x); SP1 автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server."

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

...

Рейтинг:

0 / 0

25.05.2020, 13:30

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961680

Гавриленко Сергей Алексеевич

Участник

Откуда: Moscow

Сообщения: 37 171

Рейтинг: 0 / 0

Модератор: invm, Mikhail__174, живите дружно и не кидайтесь какашками друг в друга.

...

Рейтинг:

0 / 0

25.05.2020, 13:48

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961728

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

Mikhail__174,

энтерпрайз, тем не менее. не гарантирует, что всегда будет использована индексированное представление. Для гарантии необходимо хинт писать, всё-таки. Недавно с этим столкнулся.

...

Рейтинг:

0 / 0

25.05.2020, 14:58

| Ответить | Цитировать | Написать

почему оптимизатор запросов игнорирует индексированное представление

#39961781

Mikhail__174

Гость

Владислав Колосов,авторэнтерпрайз, тем не менее. не гарантирует, что всегда будет использована индексированное представление. Для гарантии необходимо хинт писать, всё-таки. Недавно с этим столкнулся.
Спасибо, я об этом тоже читал :)

...

Рейтинг:

0 / 0

25.05.2020, 17:05

| Ответить | Цитировать | Написать

20 сообщений из 20, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&gotonew=1&tid=1686076]:	0ms
get settings:	11ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	45ms
get topic data:	11ms
get first new msg:	8ms
get forum data:	3ms
get page messages:	61ms
get tp. blocked users:	1ms
others:	16ms

total:	179ms