Не знаю почему (подозреваю что поломалась статистика), но изменился план выполнения для одного из запросов: вместо Index Seek стал выполняться Index Skan. Эту таблицу я постоянно загружаю в обмене, и из-за такой досадной неприятности обмен сломался.
Есть 2 таблицы: одна с данными (больше 1 млрд строк), другая содержит ключи с изменениями. Для получения изменившихся данных я соединяю эти 2 таблицы и получаю все изменившиеся строки.
Если изменений < 5000, либо при использовании LOOP JOIN, либо если принудительно указать индекс WITH (INDEX=) - то происходит Index Seek. Иначе - Index Skan.
Есть несколько аналогичных запросов к другим таблицам, схожим по структуре и объёму данных. С ними всё в порядке.
Кеш сервера сбрасывали, статистику для таблицы обновляли - ничего не помогло. Что ещё можно сделать чтобы оптимизатор снова правильно построил план?

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

TaPaKMax_11111,
т.е. вы не видите что вашего индекса не достаточно?
Вы про то что не все поля содержатся в индексе? Так это и незачем. Index Seek + обращение по ключу выполняется мгновенно - если смотреть по времени. Почему SQL приписывает ему такую сложность - не понимаю

TaPaK,
В таком случае как дать понять SQL что сложность запроса с Index Seek действительно будет меньше, чем с Index Skan?
Пихать всю таблицу в каждый индекс (которых там штук 5) - точно не решение

TaPaKMax_11111,
попробуйте FORCESEEK. Можно и WITH INDEX (не знаю решилась ли проблема с удалением индекса, вроде пофиксили) но это не самое здравое решение. По остальному не ясно, что на самом деле в actual plan
FORCESEEK построил "правильный план".
А что нужно сделать чтобы оптимизатор сам догадался без подсказок? У меня из идей пока только обновить статистику с полным сканированием таблицы. Но из-за размера таблица может зависнуть на долго, чего очень не хотелось бы

TaPaKпосмотрите в актуальный план. Если в нём некритично расходится estimate / actual то статистика вам больше ничего не даст
Критично. Запрос возвращает порядка 50000 строк, при этом оценочное количество на 4 порядка выше!

alexeyvgНужно попробовать сделать полное обновление, ну или оставить FORCESEEK
Тоже к этому склоняюсь, как к самому простому что можно сделать. Надеюсь поможет.

GlebanskiМеня вероятно запинают, но все ж интересно.
Может быть, что SQL Server по какой-то причине стал полагать SORT слишком тяжеловесным? Изменится ли план, если внутри CTE заранее отсортировать данные? Ну с чем-то типа TOP 10000000
В меньшую сторону оценка может сдвинуться, а вот в большую - скорее всего нет. Как я понимаю:
- если оптимизатор оценивает объём выборки в 100 строк, а вы говорите TOP 1000 - то оптимизатор проигнорирует это значение, ведь 100 - это максимум, который он ожидает.
- Если оптимизатор даёт оценку в 1000 строк, а вы пишите TOP 100 - то оптимизатор с радостью изменит оценку на 100 строк.

MindВы хотите чтобы мы вам сказали где и как сервер ошибается в эстимейтах по оценочным планам?
В идеале - да :)

MindВ видели что у вас нет нормального поиска по [dns_m].[dbo].[_InfoRg4781]. [_Fld4784]=(0.) ? И вообще, 0 тут это константа или может меняться?
Какое из этих условий наиболее селективное? И есть ли среди этих значений константы?


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

MindСервер ошибается на выборке из этой таблицы или на джойне?
Если первое, то можно попробовать создать правильные многоколоночные статистики с полным сканом, или если возможно то фильтрованные. Второй вариант - селектить chng_gr во временную таблицу и потом джойнить.
Если проблема в джойне (маловероятно), то это может быть из-за того что соединение по 2м полям. Сервер плохо перевиривает такие джойны. Тут или попробовать создать вручную стастистику по обоим полям на обеих таблицах в обе стороны с полным сканом, либо переходить на 2014, там с этим вроде получше.
Ошибка на JOIN.
Временная таблица не поможет, т.к. запрос вызывается в SSIS потоке данных, а там временные таблицы запрещены. Табличная переменная решила бы эту проблему, т.к. оптимизатор всегда считает что в ней 1 строка - поэтому план бы строил с Index Seek.
База крутится на 2014 SQL.
Статистику обновим, скорее всего на выходных