Базовый анализ проблем на сервере СУБД. Часть 2. Логические проблемы

В предыдущей публикации мы подняли тему того, как «ДБ-сервис» диагностирует неполадки после получения жалоб от клиентов. А также рассмотрели первую большую группу возможных аварий — инфраструктурные проблемы.

Сегодня мы поговорим о второй группе, в которую входят логические проблемы, относящиеся к внутренней работе СУБД. Хотя часть из них может вызываться неисправностями в работе физического оборудования, решение именно логических проблем находится в компетенции наших инженеров.

При выполнении транзакций СУБД накладывают на данные блокировки — временные ограничения на выполнение некоторых операций обработки данных, такие как чтение и запись. Это позволяет выстраивать в очередь все транзакции, работающие с конкретной строкой в базе и помогает защищать целостность данных.

SQL-блокировки — важный механизм работы СУБД, однако нередки случаи, когда неоптимальное пересечение процессов влечет за собой появление разных проблем одновременного доступа к данным. В таких ситуациях самое главное — найти корень проблемы.

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

Анализ типов ожиданий и ресурсов позволяет проанализировать ситуацию и решить, что с ней делать. Если блокировок много и виновник не очевиден или их несколько, нужно построить «дерево блокировок» и станет точно ясно, кто является «корнем».

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

Следующий важный аспект логических проблем, затрудняющих нормальную работу БД — неоптимальное состояние таблиц. Речь в первую очередь идет о фрагментации индексов и таблиц (если они без кластерного индекса) и актуальности статистики по ним.

В «ДБ-сервис» для анализа статистики мы используем собственные скрипты, позволяющие получать наиболее точные данные.

Как видно из скриншота, индексы не фрагментированы, однако, даже если бы картина была обратной, их размеры (менее 5 ГБ) позволили бы без проблем запустить скрипт для ребилда:

В рассматриваемом примере статистика по таблице обновляется с выборкой 25% - для выбора такого значения параметра нужно точно знать, что таблица невелика. Также здесь используется многопоточный режим, который доступен для SQL SERVER, начиная с версии 2014 SP3.

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

Процесс 3754, выполняющие вставки / апдейты, наложил ни с чем не совместимую монопольную блокировку на таблицу INVENTSUMDELTA. В результате становится невозможным ни обновить статистику, ни перестроить индекс в онлайн т.к. они требуют блокировку уровня схемы.

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

В свойствах индекса можно увидеть, что его фрагментация в норме (всего 7,5%), однако число фантомных строк версии превышает 4 миллионов.

Наши инженеры опытным путем вывели, что перестроение такого индекса убирает проблему и скорость выполнения запросов многократно возрастает, однако более профессиональным все же считается выполнение обслуживания проблемной таблицы целиком. Делается это с помощью выполнения следующей команды:

Еще один аспект логических проблем в СУБД — отсутствие необходимой структуры данных. Речь может идти об индексах или даже статистике.

Оптимизатор запросов — это механизм, управляющийся ядром СУБД и транслирующий команды TSQL в план запроса. Сам этот процесс принято называть компиляцией. Компиляция происходит перед каждым выполнением запроса, если его план не был скомпилирован ранее и сохранен в кэше.

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

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

Причин проблем с логикой запросов может быть несколько:

Симптомы проблем с логикой SQL внутри СУБД не всегда являются явными. К примеру, если много мелких запросов выполняются не 10 Мс., а 500 МС., разница не будет заметна ни для человеческого глаза, ни для многих диагностических скриптов, однако скорость процесса будет замедлена в 50 раз.

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

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

Анализ количества запросов и потребления ресурсов в этом примере не позволяет сделать вывод о наличии проблем. Однако, если бы первый запрос выполнялся 120 раз, он стал бы первым кандидатом на оптимизацию. Если после фрагментации, обновления статистики и повторного запуска процедуры запрос выполнялся бы уже 1000 раз, можно было бы говорить о восьмикратном ускорении в результате устранения неполадок.

В этой статье мы рассказали об основных аспектах логических проблем, возникающих на серверах СУБД. Были рассмотрены конфликты блокировок SQL, неоптимальные состояния таблиц, участвующих в запросах, отсутствие необходимой структуры данных и проблемы внутри логики СУБД.

Хотя общее понимание того, как диагностировать такие неполадки может пригодиться каждому системному администратору, еще раз подчеркнем, что базовый анализ и устранение логических проблем на серверах СУБД — зона ответственности «ДБ-сервис». Наши инженеры в любое время суток помогут с сопровождением любой базы данных и всем комплексом работ по их администрированию, чтобы ваш бизнес был надежно защищен от технических неполадок.