Когда начинают поступать подобные сигналы и появляются первые претензии от пострадавшего бизнеса, первое решение, которое приходит в голову, – «надо увеличить производительность баз данных посредством покупки сервера помощнее». Но задача повышения производительности СУБД и работающих в ней баз данных не всегда решается линейным увеличением ресурсов и мощности, например, прямой подход «приобретения сервера с более новым процессором, большей памятью и т. д.» чаще всего не сработает.
Реальные проблемы часто лежат в непроработанных узких местах баз данных, настройках СУБД, неоптимальных и избыточно сложных запросов от приложений, а не в нехватке ресурсов. А это значит, что решение задач производительности должно опираться на реальные потребности и особенности работы вашей конкретной СУБД и конкретных используемых в ней баз данных. Эти потребности и особенности определяются только тщательным анализом конкретной системы и включают работу над несколькими аспектами:
Тюнинг запросов
Как параллелятся запросы на процессоре?
Когда огромные частые запросы и процедуры выполняются десятками секунд в один поток, как правило, смена процессора и увеличение ресурсов обернутся зря потраченным бюджетом и временем.
Насколько сложны и с какой частотой выполняются хранимые процедуры?
Возможно, процедуры требуют модернизации, например, сокращения вложенности, создания модульных текстов, или актуализации под задачи, системы, отдельные БД и пр.
Структура частых и длительных запросов.
Как правило, ее анализ позволяет выявить требования по нормализации базы или переопределения процедур и запросов из приложения.
Анализ пиков загрузки CPU и снимков запросов в MS SQL SERVER и операционной системе. Это ресурсный инструмент, необходимый для определения коррелирующих событий, не попадающих в часто используемые запросы и хранимые процедуры.
Глубина частых или повисших запросов.
Его результат приводит нас к задачам актуализации запросов приложений или хранимых процедур, или переиндексации и нормализации базы данных.
СУБД
Объем и использование памяти для СУБД MS SQL.
Мы встречали системы, где СУБД использовала 15 % от доступной памяти — так исторически сложилось :-)
Длина очередей в СУБД…
Сами знаете, о чем я.
Длительность, частота, процедура авторизации подключений (коннекций).
Длительная авторизация коннекции (подключения) к базе данных в нагруженных системах может занимать до 15-20% времени получения ответа от сервера. Нужно проанализировать, как именно осуществляются авторизации, и производить настройки и анализ в областях настройки кеширования AD (в случае использования для авторизации коннекций СУБД), изменений самой структуры / схемы авторизации (возможно вынеся авторизацию на иной уровень из СУБД, например, на уровне FW). Анализ лучше проводить инструментами диагностики и путем создания специфичных Extended Events.
Анализ влияния репликации.
Синхронная репликация оказывает влияние на производительность, но иногда из-за влияния сети, настроек и иных факторов она может провоцировать случайные по времени критические стопоры в работе.
Нет ли ошибок хранимых процедур и maintenance plan’ов (планов обслуживания)?
Нет ли критичного совпадения расписаний задач, которое негативно влияет на производительность?
Случай из практики: во время пиковых нагрузок с построением отчетов и работающими пользователями запустилось сканирование файловой системы Antimalware Service Executable.
Анализ и нормализация содержимого БД:
Слишком большой размер и переполненность журналов транзакций/transaction log file’s. Эта проблема часто встречается после нормализации БД. Причина— неправильное выполнение или невыполнение процедуры усечения журналов транзакций.
Штатно ли проходит нормализация базы данных?
Живая система требует периодической переиндексации, при этом могут случаться ошибки в связности.
Анализ расположения и параметров tempDBs.
Они во многом зависят от специфики запросов, вложенности и структуры данных, а также имеющихся ресурсов.
Ввод-вывод:
Как разбиты DataFile (файлы данных), какова интенсивность ввода-вывода по файлам данных?
Часто выгрузкой в отдельные файлы данных наиболее активных таблиц и размещением их на быстром носителе можно покрыть проблемы с длительными запросами. Ещё лучше помогает кеширование операционной системы.
Как происходит загрузка, не возникают ли очереди дискового ввода вывода?
Часто дисковые очереди увеличивают время выполнения запроса из-за длительного извлечения данных. Причина может быть в том, что задачи попросту не настроены. Мониторинг ввода-вывода доступен из операционной системы.
И наконец…
Может, случился банальный DDOS или подбор паролей?
В нашей практике у клиентов встречались такие проблемы, для решения которых достаточно было ограничить сетевые подключения, а для диагностики — проанализировать логи сервера баз данных.
После детального анализа обычно вырисовывается один из вариантов дальнейшего развития событий или их комбинация:
Решаем текущие проблемы настройками и тюнингом СУБД, баз данных, операционной системы без модернизации оборудования.
Составляем список рекомендаций об увеличении ресурсов и подбираем новое оборудование под специфику работы СУБД и баз данных. Если все-таки наращивать мощности, нужно понять, что именно: увеличивать тактовую частоту и гнаться за новыми процессорными ресурсами, или линейно увеличить количество вычислительных ядер процессора, параметры памяти, каналов, ввода-вывода или дисков.
Составляем рекомендации разработчикам приложений по изменению структур и текстов конкретных запросов.
Перенастраиваем архитектуру MS SQL SERVER и параметры баз данных. Проводим работу по повышению доступности, отказоустойчивости, отдельной аналитики через группы AlwaysON, архитектуру Failover Cluster.
Настраиваем мониторинг: уведомления и контрольные точки для предупреждения о возможных проблемах для оперативного принятия мер.
Еще раз подчеркну: не всегда можно решить проблемы производительности сервера MS SQL линейно, и уж тем более не всегда увеличение ресурсов даст стабильный результат в течение времени. Проблемы MS SQL SERVER нужно решать комплексно, приводя в равновесие разные части системы:
СУБД;
базы данных;
настройки ОС;
настройки и характеристики окружения (сеть, сервера авторизации, сторонние сервисы…);
запросы и специфику приложений;
вычислительные ресурсы системы.
