Здравствуйте!

Работаю на MS SQL 2017.

Есть таблица на 8 300 000 строк. В нее никто никогда ничего не пишет. И только 1 процесс читает.
На ней некластерный индекс, предложенный аналайзером. Вот такой:

CREATE NONCLUSTERED INDEX [idx_pc2] ON [dbo].[tab_pos]
(
[t_id] ASC,
[p_id] ASC
)
INCLUDE([num],[seg_num],[weight],[mask],[cnt])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]


Есть запрос, вызывающий через outer apply функцию, читающую эту таблицу. Он работает продолжительное время.
Время однократной отработки функции 150-200 мс. Смотрю по логирующей таблице и по профайлеру.
Через 40 минут работы (~14000 вызовов) резко падает производительность до 800-1200 мс.
Если процесс остановить и продолжить - производительность остается неудовлетворительной.

Если посмотреть на указанный индекс, то по нему фрагментация показана 0,012.
Если индекс "Перестроить" через менюшку и запустить процесс снова, то производительность ВОССТАНАВЛИВАЕТСЯ.
Но фрагментация не меняется. Что при низкой, что при высокой производительности фрагментация 0,01 - 0,02.
Пробовал читать эту таблицу (NOLOCK) - никаких изменений не заметил.
По дисковым операциям в журналах ничего не заметил.
По графику загрузки диска - 3%. Диск samsung 980 pro М.2
Файл базы 20 Г, лог - 4 Г. Выделены статически с большим запасом.


Что там портится? И почему так быстро? :(
Можно как-нить зафиксировать таблицу и индексы в стабильном состоянии?

Спасибо всем!

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

1. Дождался деградации - снял план.

2. update statistics [42AF9C90-8B3C-454E-9FAA-4C0C4D1485BE_pos] - не имеет никакого эффекта.

3. Пересторил индекс - снял план.

Они огромные, я xml не буду приводить. А картинку - простите :)
План сильно меняется. На медленном плане практически везде неверное предполагаемое число строк.
После перестроения индекса оно исправляется для нужной таблицы, но потом портится опять.
Кроме того, используются две временные таблицы - для обеих изменения в плане вызывают сильную деградацию.

Верно, табличные переменные. Там еще есть передача их через параметры. И внутри функций нельзя создавать/дропать временные таблицы.

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

EXEC sp_recompile N'[dbo].[42AF9C90-8B3C-454E-9FAA-4C0C4D1485BE_pos]'

Вот помогло! Улучшился даже "хороший" план :))
Причем, указываю имя таблицы, а не деградирующей функции.

Всем спасибо!

Думаю, рекомпиляция всех объектов, связанных с таблицей - грубое решение. Мне же нужен был конкретный план.
Поставил OPTION(RECOMPILE) на исследуемый запрос. Вроде проблема пока не проявляется.

Тогда вообще не понимаю...

Если был хороший план, и он был зафиксирован, то почему он однажды превращается в плохой навсегда?
А если он теперь перекомпилируется все время - то почему он все время хороший (т.е. исходный)?

komrad,

Верхний план (на картинке) хороший. Но в какой-то момент во время длительного процесса он изменится на нижний плохой (минимум в 5 раз дольше. То, что там 400мс против 700мс - это только один запрос) . И все время будет плохой до перекомпиляции (перестроения индекса).

Выполнил одну итерацию в отладке - увидел плохой план.
Пустил весь объем - он весь пройдет по плохому плану с плохим временем.
Он даже случайно не даст хороший результат на каких-то особенных данных.

Перестроил, выполнил одну итерацию - увидел хороший план.
Пустил весь объем (те же самые данные) - он идет по хорошему плану 40 минут.
А потом жопа. Смотрю одну итерацию - плохой план.

Включил перекомпиляцию. Везде хорошее время. Останавливаю в любой момент, даю одну итерацию - верхний план. :(

SERG1257,
спасибо, буду изучать