Добрый день.

MS SQL Windows Server 2016 Standard 15.0.2080.9.
База данных используется для 1С.

Есть простенький запрос, который выполняется очень часто (текст пойман на стороне SQL сервера в том виде, в котором его сгенерировала 1С).

Иногда приходит немного другой текст запроса, в котором вместо 0x0000045C другая подобная константа.

Для таблицы _AccumRg49944 задан кластерный индекс


Также имеется уникальный индекс

Этот индекс является покрывающим для запроса.

В чем собственно проблема.
Приведенный запрос обычно выполняется быстро (несколько мс). При этом используется второй индекс.
В плане запроса 2 операции:
Однако, спустя примерно сутки, этот запрос начинает выполнять по 30-40 секунд.
План запроса при этом выглядит по другому, используется кластерный индекс

Почему так происходит - мне непонятно.
Пока что я придумал только один способ решения данной проблемы. Это перестроить второй индекс.

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

Дополнительная информация.
Автообновление статистики включено.
Поле _Fld2467 во всей таблице имеет одно значение.
Для поля _RecorderTRef плотность статистики 0.08333334
Для поля _RecorderRRef плотность статистики 0.0002060581

Размер таблицы около 85 млн строк, 95Гб.
Размер второго индекса - 3.5Гб

В течении дня записи в таблицу добавляются и удаляются. Я оцениваю, что осуществляется примерно 10-12 тысяч операций в сутки.

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

0wl,
Спасибо за подробный ответ.
Действительно, в "быстром" плане операция SORT оценена в 4 раза дороже, чем Index Seek.
К сожалению, я несколько ограничен в настройке индексов из-за платформы 1С.
Попробую чаще обновлять статистику по этой таблице или индексу, понаблюдаю за поведением.

spenov,
Запрос, который я выложил, сгенерирован платформой. В 1С он выглядит еще проще и параметр там только один.
Насколько я понимаю, второй параметр платформой 1С специально поставляется напрямую в текст запроса константой. Как раз чтобы исключить parameter sniffing.
В реальности, должно приходить порядка 15 разных запросов, отличающихся только вторым параметром. Меняя тип параметра в 1С я должен получать другой запрос - с другим условием на _RecorderTRef. По идее, для каждого из них должен храниться свой план.
Спасибо за идею, попробую отследить, какие именно запросы начинают "тормозить". Отличается ли второй параметр в этих запросах, или из всей пачки "долгий" план выбирается только для одного единственного значения второго параметра.

Мои наблюдения кончились следующим.

Как описано тут Положительный побочный эффект от перестроения индекса , при перестроении индекса обновляется статистика по нему с полным сканированием всех строк.

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


Это привело к ожидаемому эффекту - планы запроса снова стали использовать этот индекс.

Перед этим я пробовал пересчитать статистику с параметрами по умолчанию, без указания WITH FULLSCAN.
Это эффекта не дало.

Теперь я столкнулся с другой ситуацией. При пересчете статистики сиквел использует последние параметры. Автоматически обновленная статистика также была собрана по всем строкам таблицы. И в итоге, изначальной проблемы с выбором неверного индекса я теперь не наблюдаю.

Где можно почитать про стратегии выбора параметра SAMPLE и расчет количества строк для обновления статистики? Статья UPDATE STATISTICS мне не сильно в этом помогла.
Мне кажется, что пересчитывать статистику по всем строкам может быть не лучшим решением.
И еще хочу разобраться, почему пересчет статистики с параметрами по умолчанию не дает желаемого эффекта.