Коллеги, почему по вашему мнению происходит такая недооценка?

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

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


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

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

msLexkomradпропущено...




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

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

msLexmsLexпропущено...


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

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

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



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


Экспортировать в QueryStore пока не представляется возможным, использовать хинт option use plan можно только в академических целях. Такое решение как постоянное не годится.

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

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

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

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

вторая

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

размеры таблиц на проде

размеры таблиц на тесте

msLex,

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

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

[quot msLex]komradmsLex,

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

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

https://www.sql.ru/forum/actualfile.aspx?id=21915690] Приложенный файл (2 actual plans.zip - 99Kb)
msLexВо-первых, у вас построение плана завершилось по timeout-у

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

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

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


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

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

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