Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Замедление после дефрагментации индексов  [new]
Danion
Member

Откуда: Москва
Сообщений: 203
Добрый день.

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

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

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

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

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

Пока видел только упоминание об увеличении времени до первого обновления статистики. Статистику обновлял - не помогло.
Либо вариант с изменением свободного места для страницы и MAXDOP, но тоже не мой вариант.
11 дек 18, 10:50    [21759938]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
вот это вот поясните, плиз:
автор
потом реорганизация индекса (сжатие больших объектов, быстрое сканирование, 10+ фрагментация и 1000 страниц)

реорг ничего не сжимает.
сжимает компрессия, но это не реорг, а ребилд,
и главное, блобы как раз и не сжимаются.

в общем, если вы пожали данные, сделав rebuild with data_compression,
то не надо удивляться, что запросы теперь CPU выкушивают
11 дек 18, 12:26    [21760101]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

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

К сообщению приложен файл. Размер - 54Kb
11 дек 18, 12:51    [21760149]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5230
Danion

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


проблемы у вас от изменившихся планов выполнения, полагаю
индексы перестроены, статистики перестроены, оптимизатор решил посканировать таблицы

если autoupdate/autocreate статистики на базе выключен, то может быть подобный эффект
11 дек 18, 13:04    [21760180]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
нет, compact large objects это совсем другое.
реорг это делает по умолчанию,
но это НЕ компрессия,
ничего не сжимается, просто высвобождаются пустые страницы.
это никак не повлияет на дальнейшее чтение, разве только в лучшую сторону
11 дек 18, 13:07    [21760187]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
komrad
оптимизатор решил посканировать таблицы

но ведь если "решил посканировать", то IO подскочит, а не CPU
11 дек 18, 13:09    [21760195]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5230
Yasha123
komrad
оптимизатор решил посканировать таблицы

но ведь если "решил посканировать", то IO подскочит, а не CPU

+ параллелизм вестимо


Danion,
покажите результат

select * from sys.configurations
11 дек 18, 13:19    [21760211]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7658
komrad,

То есть раньше параллелизма не было, а после переиндексации выскочил? Я скорее поверю в перерасчет статистик. Но это кратковременно должно быть, несколько часов от силы.
11 дек 18, 13:24    [21760224]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Danion
Добрый день.

Есть сервер MS SQL 20016 SP2 с несколькими базами данных, где дефрагментации индексов не проводилось долго.
последнее действие обновление статистики.

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

Статистику обновлял - не помогло.


У Вас был loop join из-за давно непересчитываемой статистики, а теперь hash join с полным сканированием крупных таблиц. Раньше нагрузка ложилась при merge join на tempdb, сейчас не хватает ядер на процессорах.

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

И начните с того, что напишите сюда количество ядер и процессоров, тактовую частоту, модель процессоров. И что установлено в maxdop для всего инстанса MSSQL.
11 дек 18, 13:41    [21760250]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

Откуда: Москва
Сообщений: 203
komrad,

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

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

К сообщению приложен файл. Размер - 136Kb
11 дек 18, 15:21    [21760443]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

Откуда: Москва
Сообщений: 203
Вторая часть

К сообщению приложен файл. Размер - 139Kb
11 дек 18, 15:21    [21760445]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5230
Danion
komrad,

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

статистику можно обновлять по-разному
самая точная и долгая - fullscan

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

см коммент от Andy_Olap, очень вероятно
11 дек 18, 15:32    [21760463]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

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

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

К сообщению приложен файл. Размер - 120Kb
11 дек 18, 15:35    [21760470]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5230
Danion,

вот это еще покажите

select * from [DB].sys.database_scoped_configurations


DB - ваша база
11 дек 18, 15:42    [21760482]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4680
Danion,

Вы случайно не загоняли fill factor к 100%. Если да, то это всё объясняет.
11 дек 18, 16:01    [21760513]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7658
a_voronin,

сплит страниц аццки нагружает CPU по-вашему? У меня в базе все таблицы 100% заполнение имеют и нагрузка не выше 20% в плохую погоду.
11 дек 18, 16:09    [21760525]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
a_voronin
Danion,

Вы случайно не загоняли fill factor к 100%. Если да, то это всё объясняет.

Вы думаете, что автор темы вместо дефолтного "reorganize pages" выбрал "change free space" как на картинке? И выкрутил его на 0, получив fill factor 100?
11 дек 18, 16:12    [21760529]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7658
Обратите внимание, что у комрада виртуалка. А там уже какую гайку админ подкрутит.
11 дек 18, 16:13    [21760532]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

Откуда: Москва
Сообщений: 203
a_voronin,

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

komrad,

К сообщению приложен файл. Размер - 79Kb
11 дек 18, 16:16    [21760535]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

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

Andy_OLAP,
Честно говоря, не очень помню информацию по loop join и hash join. Как освобожусь немного буду перечитывать по ним.
У меня есть бекапы до дефрагментации индексов и на дату, когда проблемы начались. При развертывании на тестовом сервере примерно повторить проблему и сравнить настройки удастся или тут привязка именно к тому серверу?
11 дек 18, 16:26    [21760551]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Danion
На поддержку передали несколько серверов. Разворачивал и первоначально настраивал их не я, пока стараюсь менять настройки по минимуму. Хорошо, что мной же настроенные бекапы успели пройти. Только начал приводить сервера в адекватных вид и тут такой сюрприз.

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

Берете разработчика, который говорит "с понедельника вот такой селект стал работать в 10 раз медленнее".
Берете бэкап до дефрагментации и разворачиваете на любом тестовом сервере. Запускаете селект. Записывает полученный план и время выполнения. Запускаете дефрагментацию по примеру на боевом сервере. Ждете завершения. Запускаете селект. Аналогично. Далее на всякий случай разворачиваете бэкап после дефрагментации с боевого сервера на дату, когда начались проблемы. Запускаете селект. Аналогично.

Затем с полученной информацией идете к разработчику, который умеет читать план выполнения запросов, садитесь рядом и долго думаете, раздирая свои одежды и посыпая голову пеплом.
11 дек 18, 16:30    [21760558]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Danion,

Чтобы все было кошерно - тестовый сервер во время опытов не нагружаете больше ничем.
11 дек 18, 16:31    [21760560]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

Откуда: Москва
Сообщений: 203
Andy_OLAP,
Спасибо за ссылку на статью по join.

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

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

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

К сообщению приложен файл. Размер - 47Kb
11 дек 18, 16:46    [21760579]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5230
Andy_OLAP
Берете бэкап до дефрагментации и разворачиваете на любом тестовом сервере. Запускаете селект. Записывает полученный план и время выполнения.


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

если получили таки его, попробуйте пришпилить с помощью query data store, и перестроив индекы проверить производительность с форсированным "быстрым" планом и свежесгенеренным новым
11 дек 18, 16:47    [21760581]     Ответить | Цитировать Сообщить модератору
 Re: Замедление после дефрагментации индексов  [new]
Danion
Member

Откуда: Москва
Сообщений: 203
Спасибо всем за ответы.

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

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

Заранее спасибо.
11 дек 18, 16:58    [21760597]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить