Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
Коллеги, почему по вашему мнению происходит такая недооценка?

Индексы перестроены, статистика хоть авто, хоть фуллскан - недооценка постоянная.

индекс скан : таблица 709млн записей, колумнстор + обычные индексы
индекс сик : таблица 1.4млрд записей, колумнстор + обычные индексы


Microsoft SQL Azure (RTM) - 12.0.2000.8 May 2 2019 20:11:13 Copyright (C) 2019 Microsoft Corporation

К сообщению приложен файл. Размер - 19Kb
25 июн 19, 17:49    [21915087]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36694
Там еще поди хитрый предикат при скане есть?
25 июн 19, 17:51    [21915088]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
Гавриленко Сергей Алексеевич
Там еще поди хитрый предикат при скане есть?


К сообщению приложен файл. Размер - 58Kb
25 июн 19, 17:59    [21915096]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
komrad
Гавриленко Сергей Алексеевич
Там еще поди хитрый предикат при скане есть?


Картинка с другого сайта.


Смотрите подробности в "property", там будут предикаты.
25 июн 19, 18:23    [21915104]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
msLex
komrad
пропущено...


Картинка с другого сайта.


Смотрите подробности в "property", там будут предикаты.


В property Columnstore index scan нет предикатов

К сообщению приложен файл. Размер - 37Kb
25 июн 19, 18:37    [21915108]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
komrad
msLex
пропущено...


Смотрите подробности в "property", там будут предикаты.


В property Columnstore index scan нет предикатов

Картинка с другого сайта.


Есть подозрение, что в скане показывается эстимайт с учетом probe.
Так же есть подозрение, что вам нужны все данные из CS и эвристическая оценка probe сильно занижена относительно реальных 100% записей, что проходят probe предикат.
25 июн 19, 18:49    [21915115]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
msLex
komrad
пропущено...


В property Columnstore index scan нет предикатов

Картинка с другого сайта.


Есть подозрение, что в скане показывается эстимайт с учетом probe.
Так же есть подозрение, что вам нужны все данные из CS и эвристическая оценка probe сильно занижена относительно реальных 100% записей, что проходят probe предикат.


Про 100 % я ошибся, остальное в силе.
25 июн 19, 18:54    [21915119]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
msLex
msLex
пропущено...


Есть подозрение, что в скане показывается эстимайт с учетом probe.
Так же есть подозрение, что вам нужны все данные из CS и эвристическая оценка probe сильно занижена относительно реальных 100% записей, что проходят probe предикат.


Про 100 % я ошибся, остальное в силе.


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



Кстати, на тестовом сервере в QueryStore нашелся план при котором запрос отрабатывает за 10 минут, вместо "типичных" 25 и редких 50 минут на проде. Планы, естественно, разные.
В быстром плане оценка кол-ва записей в таблице поближе к истине и, соответственно, меняется порядок джойна таблиц.
Сам запрос - это селект из вью, которая использует 11 других вью, которые в свою очередь смотрят на 9 таблиц.


Экспортировать в QueryStore пока не представляется возможным, использовать хинт option use plan можно только в академических целях. Такое решение как постоянное не годится.
26 июн 19, 11:17    [21915424]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
komrad,

предположу, что статистика распределения значительно неравномерна. Возможно, хинт for unknowh улучшит предположения оптимизатора.
26 июн 19, 11:26    [21915431]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
Владислав Колосов
komrad,

предположу, что статистика распределения значительно неравномерна. Возможно, хинт for unknowh улучшит предположения оптимизатора.


хинт попробую

histogram steps у большого кол-ва столбцов действительно в максимуме (200).
на картинке у некоторых 201 - это подвирает sys.dm_db_stats_properties

К сообщению приложен файл. Размер - 144Kb
26 июн 19, 12:19    [21915463]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
вторая

К сообщению приложен файл. Размер - 123Kb
26 июн 19, 12:22    [21915464]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
Неверная оценка probe предикатом количества записей это ожидаемое поведение.

Этот предикат работает только в параллельных планах.
Добавьте option(maxdop 1) в запрос и он пропадет (правда вместе с параллелизмом).
26 июн 19, 12:43    [21915479]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
В продолжение темы

Насколько я понял, оценки probe предикат это просто доля от исходного объема данных в фильтруемой таблице (в общем случае резалтсете).
У меня получилось, что оценки начинаются от 1/1000000 и дальше повышаются с шагом в x10
1/100000, 1/10000, 1/1000 и т.д.

Оценка эта зависит от количества записей, на основе которых построен bitmap (первая таблица в hash-join, которой нет на скриншоте вашего плана). Соответственно, для увеличения эстимейт оценки probe, нужно повышать эстимейт оценки первой таблицы, участвующей в hash join .

PS
Кстати, насколько точна оценка записей в первой таблице?
26 июн 19, 14:07    [21915570]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
msLex
В продолжение темы

Насколько я понял, оценки probe предикат это просто доля от исходного объема данных в фильтруемой таблице (в общем случае резалтсете).
У меня получилось, что оценки начинаются от 1/1000000 и дальше повышаются с шагом в x10
1/100000, 1/10000, 1/1000 и т.д.

Оценка эта зависит от количества записей, на основе которых построен bitmap (первая таблица в hash-join, которой нет на скриншоте вашего плана). Соответственно, для увеличения эстимейт оценки probe, нужно повышать эстимейт оценки первой таблицы, участвующей в hash join .

PS
Кстати, насколько точна оценка записей в первой таблице?


actual соответствует реальному кол-ву записей
отношение estimated/actual в моем случае 1/10000

План, показанный на скриншоте, на самом деле самый медленный из полученных, и был использован для иллюстрации недооценки, которая и фигурует в двух других планах, которые используются в prod & test.

Cамый быстрый план (10 минут) в тестовой среде, выгружен из QueryStore и получить/воспроизвести его на проде пока не получается.
Самый быстрый план на проде работает 25 минут.
Кол-во записей в самых больших таблицах (Treinactiviteit_MS_SPK, Treinactiviteit_L) на тесте на 10% больше, чем на проде.

По уровню ресурсов оба "сервера" одинаковы. Написано в кавычках, поскольку по это Azure SQL DB и админского доступа к серверу нет.

планы приложены в zip

К сообщению приложен файл (2 plans.zip - 45Kb) cкачать
26 июн 19, 15:52    [21915660]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
размеры таблиц на проде

К сообщению приложен файл. Размер - 56Kb
26 июн 19, 15:53    [21915661]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
размеры таблиц на тесте

К сообщению приложен файл. Размер - 78Kb
26 июн 19, 15:54    [21915662]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
komrad
actual соответствует реальному кол-ву записей

естественно


komrad
отношение estimated/actual в моем случае 1/10000


На самом деле это actual на выходе из скана это просто количество записей в таблице, а estimated на выходе из скана - это эстимейт после фильтра



В аттаче предварительные планы, там нет actual значений. Увидеть ошибку в оценке невозможно.
26 июн 19, 16:01    [21915667]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
msLex,

приложены актуальный план с прода

с теста не сохранился и получить пока невозможно
поэтому приложил актуальный план запроса с форсированным планом (option (use plan N'...')) от тестового сервера, если это сравнимо

К сообщению приложен файл (2 actual plans.zip - 99Kb) cкачать
26 июн 19, 16:44    [21915690]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
komrad
msLex,

приложены актуальный план с прода

с теста не сохранился и получить пока невозможно
поэтому приложил актуальный план запроса с форсированным планом (option (use plan N'...')) от тестового сервера, если это сравнимо

Приложенный файл (2 actual plans.zip - 99Kb)

Во-первых, у вас построение плана завершилось по timeout-у
Во-вторых, эстиматор сильно промахнулся при оценке количества строк, причем в обоих планах

Попробуйте разбить запрос на несколько, с промежуточной материализацией части данных во временных таблицах.
26 июн 19, 17:10    [21915714]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
[quot msLex]
komrad
msLex,

приложены актуальный план с прода

с теста не сохранился и получить пока невозможно
поэтому приложил актуальный план запроса с форсированным планом (option (use plan N'...')) от тестового сервера, если это сравнимо

Приложенный файл (2 actual plans.zip - 99Kb)

msLex
Во-первых, у вас построение плана завершилось по timeout-у

это видел, но считал легитимным исходом

msLex
Во-вторых, эстиматор сильно промахнулся при оценке количества строк, причем в обоих планах.

Да, но смотря на estimated plan на тесте эстиматор промахивается на порядок меньше, чем в проде (700к vs 60k записей), и оптимизатор смог построть 10-минутный план вместо 25-минутного.
Или тут сыграла случайность и таймаут ?


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

есть такой шаг в списке задач разработчиков, но пока пытаюсь "оживить" то что есть
26 июн 19, 17:27    [21915726]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
msLex
Во-первых, у вас построение плана завершилось по timeout-у

в этом ажуре руки просто связаны
можно было бы попробовать дать больше времени оптимизатору на выбор плана (OPTION(QUERYTRACEON 8780))
но не разрешено :(

Msg 2571, Level 14, State 3, Line 1
User 'dbo' does not have permission to run DBCC TRACEON.
26 июн 19, 17:46    [21915736]     Ответить | Цитировать Сообщить модератору
 Re: сильная недооценка кол-ва записей (underestimate)  [new]
msLex
Member

Откуда:
Сообщений: 7730
komrad
msLex
Во-первых, у вас построение плана завершилось по timeout-у

это видел, но считал легитимным исходом


Ну как легитимный, оптимизатор мог (или считал, что мог) сделать план запроса лучше, но у него не хватило "времени"


komrad
msLex
Во-вторых, эстиматор сильно промахнулся при оценке количества строк, причем в обоих планах.

Да, но смотря на estimated plan на тесте эстиматор промахивается на порядок меньше, чем в проде (700к vs 60k записей), и оптимизатор смог построть 10-минутный план вместо 25-минутного.
Или тут сыграла случайность и таймаут ?


Может таймаут, может разница в нагрузке на боевой/тестовый стенд.
Как я уже сказал, повлиять на эстиматы probe можно только этимайетами внешней, первой таблицы, но у вас там, как я увидел, точные оценки (est = 1 / act = 1).

Если б это были не view, можно было бы попробовать где-то захинтовать хеш джойны вместо nested loop.

В рамках эксперемента, попробуйте выполнить этот запрос с option(hash join), может скан всех таблиц будет проще, чем миллионы index seek-ов
26 июн 19, 17:46    [21915738]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить