Добрый день.

Есть сервер MS SQL 20016 SP2 с несколькими базами данных, где дефрагментации индексов не проводилось долго.
В основных таблицах доходит до 45-50%. Я настроил средствами SSMS management план обслуживания для индексов вида:
перестроение индекса (свободное место не изменяет, индексы онлайн по возможности, maxdop не меняет. приоритет низкий, быстрое сканирование, срабатывает при 30+ фрагментации и больше 1000 страниц), потом реорганизация индекса (сжатие больших объектов, быстрое сканирование, 10+ фрагментация и 1000 страниц), последнее действие обновление статистики.

Джоб запустился один раз на выходные. А в понедельник начались жалобы на увеличение времени отработки запросов, некоторые джобы разработчиков в 5-10 раз дольше выполнялись. Основная видимая причина - нагрузка на ЦПУ начала стабильно уходить в 100%.

Базы аналитики, потеря данных устраивала и базы восстановили на время до выходных. Запросы опять отрабатывают нормально.

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

Сейчас меня интересует может ли реально быть проблема от дефрагментации индексов, что вызывает и как избежать.

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

Yasha123,
Сжатие больших объектов - название на русском внутри проектировщика планов.
Так это - compact large objects. Данный пункт именно в reorganize, не в rebuild. Включен по умолчанию и рекомендаций его отключать я не видел.

komrad,

При возникновении проблем запускал обновление статистики для пользовательских баз. Лучше не стало.

Max Degree of parallelism - 0. Это, насколько я помню, без ограничений. Но с чего бы внезапно начало плодить лишний параллелизм для загрузки ЦПУ?

Вторая часть

Конечно вариант, что там сами разрабы успели наваять отбрасывать нельзя. Но интересуют возможные проблемы со стороны СУБД, не хочется встретить проблему на основном продуктиве после моего плана обслуживания.

Andy_OLAP,
"У Вас был loop join из-за давно непересчитываемой статистики, а теперь hash join с полным сканированием крупных таблиц. Раньше нагрузка ложилась при merge join на tempdb, сейчас не хватает ядер на процессорах."
Версия интересная, нужно обновить знания по теме.
"Проверяйте охлаждение на процессорах, терпопасту под кулерами, проверяйте профиль электропитания, чтобы была максимальная производительность вместо сбалансированной."
Виртуальная машина, сам сервер в ЦОД. Выдано ей 15 процов, 2,20 ГГЦ.

a_voronin,

Думал об этом варианте, в sys.configuration min - 0, max - 100%.
Но опция в плане обслуживания default free space per page вроде оставляет то, что было задано при создании индекса.

komrad,

На поддержку передали несколько серверов. Разворачивал и первоначально настраивал их не я, пока стараюсь менять настройки по минимуму. Хорошо, что мной же настроенные бекапы успели пройти. Только начал приводить сервера в адекватных вид и тут такой сюрприз.

Andy_OLAP,
Честно говоря, не очень помню информацию по loop join и hash join. Как освобожусь немного буду перечитывать по ним.
У меня есть бекапы до дефрагментации индексов и на дату, когда проблемы начались. При развертывании на тестовом сервере примерно повторить проблему и сравнить настройки удастся или тут привязка именно к тому серверу?

Andy_OLAP,
Спасибо за ссылку на статью по join.

Попробую по тестировать.
При изменении join план запроса должен же меняться?

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

с 7 на 8 прошел план обслуживания, по графику регрессивных запросов план запроса при этом не менялся.

Спасибо всем за ответы.

Если верна версия Andy_OLAP, то со временем статистика обновится и сама по принципу "По умолчанию оптимизатор запросов обновляет статистику по мере необходимости для усовершенствования плана запроса" и похожее опять возникнет?

И кто разбирается в ошибках системных таблиц пользовательских БД - просьба подсказать в моей же теме
https://www.sql.ru/forum/1306487/oshibka-pri-checkdb

Заранее спасибо.