Почему может SELECT тормозить через какое-то время, помогает только перестройка пересоздание индекса?

Question

[Евгений Сердюков]

Есть таблица Orders. В которой сейчас около 5 млн записей.
Есть куча полей, индексов. В частности поле RegistrationTime datetime. По нему создан индекс IX_RegistrationTime с сортировкой DESC.
Через какое-то время, примерно 1 неделя, запрос начинает падать по таймауту, за 2 минуты не получает данные. Если пересоздать этот индекс (пересоздается около 3х минут, WITH ONLINE=OFF), то запрос начинает выполняться за секунды (от 2х до 10 секунд). Причем до пересоздания, смотрел отчет SQL по физической статистики индекса, то у этого индекса не было никакой рекомендуемой операции, ни перестроить, ни реорганизовать.
Почему такое может происходить, куда копать?

Answer 1

[Андрей Скоржинский]

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

Comments

[Евгений Сердюков]

Я сравнивал план, на первый взгляд они были одинаковые. В следующий раз повнимательней посмотрю.

Answer 2

[res2001]

На сколько я помню, нужно регулярно обновлять статистику.
Вот подходящий сценарий обслуживания БД.

Comments

[Евгений Сердюков]

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

Answer 3

[d-stream]

Подозреваю что дело не в самом индексе. Самое простое попробовать - в запросе указать option (recompile)

Comments

[Евгений Сердюков]

Сегодня попробовал. Я в шоке. Я дописал в запрос OPTION (RECOMPILE) - запрос выполнился моментально. Комментирую OPTION (RECOMPILE) - долго выполняется. Почему такое происходит?
Я прочитал что RECOMPILE - создает временный план, который удаляет после запроса. Получается у меня устаревает план? И этот план обновляется после обновления статистики?
Много данных обновляется за день получается, что статистика перестает быть актуальной? Или лучше рассмотреть вариант лучшей индексации таблицы?

[d-stream]

Это как раз то место, где человек может подсказать бездушной машине "тут не совсем типично, поэтому не закладывайся на прошлые опыты")

[Евгений Сердюков]

d-stream, а можно ли сказать машине что используй новый план, и сохрани его для будущих запросов? Больше всего смущает то, что новый план работает быстро,а старый тормозит. Главное 10 минут обновление статистики и старый начинает работать, а вот указываешь recompile - новый за секунду работает. Хоть бери и в запросах указывай по умолчанию recompile. Но возможно где-то в другом месте будет хуже?

[d-stream]

Евгений Сердюков, просто это ситуация, когда "раньше вот по такому плану было" - вредит. Именно для этого и используется хинт recompile.
Но это не везде и не всегда, поэтому create procedure with recompile обычно не практикуется

Answer 4

[Евгений Сердюков]

Ну вот сейчас такая же фигня. Запрос выполняется 1 минуту я его отменяю.
Обновление статистики только индексов не помогло.
После обновления статистики по столбцам - запрос нормально стал выполняться.
Вот 99% всего запроса тут. И пока не знаю, можно ли тут как-то оптимизировать запрос.

Comments

[Андрей Скоржинский]

Если есть distinct в запросе, то почти 100% запрос надо переделывать. Как переделывать, определяется бизнес-правилами и физической моделью.

[Евгений Сердюков]

Андрей Скоржинский, а почему DISTINCT надо переделывать?

[Андрей Скоржинский]

Евгений Сердюков, грубо говоря distinct "уничтожает лишние" результаты выборки. А следовательно из не надо изначально выбирать из БД, а следовательно запрос не оптимален. Конечно есть случаи когда без него никак, но это ооооочень редко. Как-то так.

[Евгений Сердюков]

Андрей Скоржинский, спасибо.