Мониторинг sql server

Введение

Напоминаю одну важную деталь. Если вы ставите Zabbix Server не с нуля, а обновляете старую версию, у вас не обновляются стандартные шаблоны. А они последнее время сильно изменились, плюс появились новые. Посмотреть их можно на github — https://github.com/zabbix/zabbix/tree/master/templates.

В данном случае я буду использовать шаблон из директории /db/mysql_agent/. Он написан для старого агента. Напомню, что начиная с версии 4.4 доступна новая версия агента, написанная на Go — zabbix_agent2. Для него появился новый функционал и новые шаблоны. Я пока буду использовать старого агента, так как с новым еще не разбирался.

Если у вас еще нет своего сервера для мониторинга, то рекомендую материалы на эту тему. Для тех, кто предпочитает систему CentOS:

Установка CentOS 8.
Настройка CentOS 8.
Установка и настройка zabbix сервера.

То же самое на Debian 10, если предпочитаете его:

Установка Debian 10.
Базовая настройка Debian.
Установка и настройка zabbix на debian.

Ставьте себе сервер и погнали настраивать.

Мониторинг баз данных доступности

Для мониторинга баз данных доступности используйте следующие представления.

sys.availability_databases_cluster
Содержит одну строку для каждой базы данных в экземпляре SQL Server, которая является частью групп доступности AlwaysOn в кластере, независимо от того, присоединена ли база данных локальных копий к группе доступности или нет.

Примечание

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

Имена столбцов: group_id, group_database_id, database_name

sys.databases
Содержит одну строку для каждой базы данных в экземпляре SQL Server. Если база данных принадлежит к реплике доступности, то в строке для этой базы данных отображается идентификатор GUID реплики и уникальный идентификатор базы данных внутри группы доступности.

Группы доступности AlwaysOn : replica_id, group_database_id

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

Имена столбцов: database_id, file_id, page_id, error_type, page_status, modification_time

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

Имена столбцов: database_id, group_id, replica_id, group_database_id, is_local, synchronization_state, synchronization_state_desc, is_commit_participant, synchronization_health, synchronization_health_desc, database_state, database_state_desc, is_suspended, suspend_reason, suspend_reason_desc, recovery_lsn, truncation_lsn, last_sent_lsn, last_sent_time, last_received_lsn, last_received_time, last_hardened_lsn, last_hardened_time, last_redone_lsn, last_redone_time, log_send_queue_size, log_send_rate, redo_queue_size, redo_rate, filestream_send_rate, end_of_log_lsn, last_commit_lsn, last_commit_time, low_water_mark_for_ghosts

sys.dm_hadr_database_replica_cluster_states
Возвращает строку с информацией, помогающей составить представление о работоспособности баз данных доступности каждой из групп доступности в отказоустойчивой кластеризации Windows Server (WSFC). Динамическое административное представление удобно использовать при планировании или при отработке отказа либо при поиске вторичной реплики в группе доступности, которая не дает усекать журнал данной базы данных-источника.

Имена столбцов: replica_id, group_database_id, database_name, is_failover_ready, is_pending_secondary_suspend, is_database_joined, recovery_lsn, truncation_lsn

Примечание

Расположение первичной реплики — авторитетный источник для группы доступности.

Примечание

Сведения о счетчиках производительности Группы доступности AlwaysOn для баз данных доступности (объект производительности SQLServer:Database Replica ) см. в разделе SQL Server, реплика базы данных. Для мониторинга активности журнала транзакций для баз данных доступности пользуйтесь следующими счетчиками объекта производительности SQLServer:Databases: Время записи журнала на диск (мс) , Записей журнала на диск/с, Неудачных обращений к кэшу пула журнала/с, Операций чтения диска пула журнала/с и Запросов пула журнала/с. Дополнительные сведения см. в статье SQL Server, Databases Object.

Подготовка mysql к мониторингу

Для примера настроим мониторинг Mysql на самом сервере мониторинга Zabbix. Так как это часто узкое место производительности системы, мониторинг базы zabbix лишним не будет. Первым делом добавим новые параметры в агенте. Для этого создаем конфигурационный файл /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf следующего содержания.

UserParameter=mysql.ping, mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables, mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version, mysqladmin -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery, mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize, mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery, mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status, mysql -h"$1" -P"$2" -sNX -e "show slave status"

После этого сразу перезапустим zabbix-agent.

# systemctl restart zabbix-agent

Дальше идем в консоль mysql и создаем пользователя, от которого будет работать мониторинг. Ему достаточно ограниченных прав на чтение.

# mysql -uroot -p
> CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY 'TTRy1bRRgLIB';
> GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
> quit

Теперь смотрим, где у нас домашняя директория пользователя zabbix.

# cat /etc/passwd | grep zabbix
zabbix:x:990:986:Zabbix Monitoring System:/var/lib/zabbix:/sbin/nologin

У меня ее не было, так что создаем.

# mkdir /var/lib/zabbix

Кладем в эту директорию конфиг .my.cnf с реквизитами доступа к серверу mysql.

user='zbx_monitor'
password='TTRy1bRRgLIB'

Назначаем пользователя zabbix владельцем своей домашней директории и файла в ней. Файлу ограничиваем доступ.

# chown -R zabbix. /var/lib/zabbix
# chmod 400 /var/lib/zabbix/.my.cnf

Подготовка к мониторингу mysql сервера завершена. Идем теперь в web интерфейс системы мониторинга Zabbix.

Связанные представления, функции и процедуры

Просматривать хранилище запросов и управлять им можно с помощью Среда Management Studio или следующих представлений и процедур.

Хранимые процедуры в хранилище запросов,

Хранимые процедуры служат для настройки хранилища запросов.

1 В чрезвычайных ситуациях хранилище запросов может перейти в состояние ERROR (Ошибка) из-за внутренних ошибок. Начиная с версии SQL Server 2017 (14.x), в таких случаях хранилище запросов можно восстановить, выполнив хранимую процедуру sp_query_store_consistency_check в соответствующей базе данных. Дополнительные сведения приведены в описании для столбца actual_state_desc в статье sys.database_query_store_options.

Поиск ожидающих запросов

Начиная с SQL Server 2017 (14.x); и База данных SQL Azure, в хранилище запросов доступна статистика ожидания каждого запроса.

Типы ожидания в хранилище запросов объединены в категории ожидания. Сопоставление категорий ожидания с типами ожидания доступно в .

Выберите Статистика ожидания запросов, чтобы открыть панель Статистика ожидания запросов в SQL Server Management Studio v18 или более поздней версии. Панель статистики ожидания запросов показывает диаграмму с главными категориями ожидания в хранилище запросов. Воспользуйтесь раскрывающимся меню в верхней части, чтобы выбрать критерий для времени ожидания: среднее, максимальное, минимальное, стандартное отклонение и общее (по умолчанию).

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

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

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

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

Предыдущая процедура
Новая процедура
Действие
Высокий уровень ожиданий RESOURCE_SEMAPHORE на базу данных
Высокий уровень ожиданий памяти в хранилище запросов для конкретных запросов
Найдите в хранилище запросов те запросы, которые используют больше всего памяти. Вероятнее всего, эти запросы препятствуют дальнейшей обработке затронутых запросов. Рекомендуется использовать указание запроса MAX_GRANT_PERCENT для этих запросов или затронутых запросов.
Высокий уровень ожиданий LCK_M_X на базу данных
Высокий уровень ожиданий блокировки в хранилище запросов для конкретных запросов
Проверьте текст затронутых запросов и выявите целевые сущности. Найдите в хранилище запросов другие запросы, изменяющие ту же сущность, которые часто выполняются и (или) имеют большую длительность. Найдя такие запросы, рекомендуется изменить логику приложения, чтобы улучшить параллелизм, или использовать менее строгий уровень изоляции.
Высокий уровень ожиданий PAGEIOLATCH_SH на базу данных
Высокий уровень ожиданий ввода-вывода буфера в хранилище запросов для конкретных запросов
Найдите в хранилище запросов запросы с большим числом физических операций чтения. Если они соответствуют запросам с высоким уровнем ожиданий ввода-вывода, рекомендуется ввести индекс для базовой сущности, чтобы выполнять поиск вместо сканирования и этим минимизировать временные затраты ввода-вывода для запросов.
Высокий уровень ожиданий SOS_SCHEDULER_YIELD на базу данных
Высокий уровень ожиданий ЦП в хранилище запросов для конкретных запросов
Найдите в хранилище запросов те запросы, которые используют больше всего ресурсов ЦП. Выявите те из них, у которых высокое использование ЦП коррелирует с высоким уровнем ожидания ЦП для затронутых запросов

Уделите внимание оптимизации запросов — может иметь место регрессия плана или отсутствующий индекс.

Наблюдение за процессом

Наблюдение за процессом отслеживания измененных данных позволяет определить, правильно ли записываются изменения и насколько приемлема задержка при записи в таблицы изменений. Наблюдение также помогает выявить возможные ошибки. SQL Server включает два динамических представления управления, которые помогают отслеживать фиксацию измененных данных: sys.dm_cdc_log_scan_sessions и sys.dm_cdc_errors.

Выявление сеансов с пустыми результирующими наборами

Каждая строка в административном представлении sys.dm_cdc_log_scan_sessions представляет сеанс просмотра журнала (за исключением строки с идентификатором 1). Сеанс просмотра журнала является эквивалентом одного выполнения хранимой процедуры sp_cdc_scan. Во время сеанса просмотр может возвратить изменения или пустой результат. Если результирующий набор пуст, то для столбца empty_scan_count в представлении sys.dm_cdc_log_scan_sessions устанавливается значение 1. Если пустые результирующие наборы встречаются последовательно (например, при непрерывном выполнении задания отслеживания), то счетчик empty_scan_count в последней существующей строке увеличивается. Например, если в представлении sys.dm_cdc_log_scan_sessions уже существует 10 строк просмотров, возвративших данные об изменениях, и пять результатов подряд были пусты, то в представлении будет содержаться 11 строк. В столбце empty_scan_count последней строки содержится значение 5. Чтобы определить сеансы, возвратившие пустой результирующий набор, выполните следующий запрос.

Определение задержки

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

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

Еще одним важным показателем эффективности процесса отслеживания является пропускная способность. Это среднее число команд в секунду, обрабатываемых в каждом сеансе. Для определения пропускной способности сеанса следует разделить значение в столбце command_count column на значение в столбце продолжительности. Следующий запрос возвращает среднюю пропускную способность для наиболее новых сеансов.

Получение выборки данных с помощью сборщика данных

Сборщик данных SQL Server позволяет осуществлять сбор моментальных снимков из любой таблицы или динамического административного представления и создать хранилище данных о производительности. Если для базы данных активирована система отслеживания измененных данных, то полезно создавать снимки представлений sys.dm_cdc_log_scan_sessions и sys.dm_cdc_errors с регулярными интервалами для последующего анализа. Следующая процедура настраивает сборщик данных на сбор образцов данных из административного представления sys.dm_cdc_log_scan_sessions.

Настройка сбора данных

Включите сборщик данных и настройте хранилище данных управления. Дополнительные сведения см. в разделе Управление сбором данных.
Выполните следующий код для создания пользовательского сборщика для отслеживания измененных данных.
В среде SQL Server Management Studioразверните вкладку Управление, затем вкладку Сбор данных. Щелкните правой кнопкой мыши пункт Сборщик данных о производительности CDC, затем пункт Запустить набор сбора данных.
В хранилище данных, которое было настроено в шаге 1, найдите таблицу custom_snapshots.cdc_log_scan_data. В данной таблице предоставлен архивный моментальный снимок данных из сеансов просмотра журнала. Эти данные могут быть использованы для анализа задержки, пропускной способности и других показателей производительности во времени.

Включение хранилища запросов

Хранилище запросов не включено по умолчанию для новых баз данных Azure Synapse Analytics и SQL Server, но включено по умолчанию для новых баз данных в службе «База данных SQL Azure».

Использование страницы «Хранилище запросов» в SQL Server Management Studio

В обозревателе объектов щелкните правой кнопкой мыши базу данных и выберите пункт Свойства.

Примечание

Требуется Среда Management Studio версии не ниже 16.
В диалоговом окне Свойства базы данных перейдите на страницу Хранилище запросов .
В поле Режим работы (запрошенный) выберите значение Чтение и запись.

Использование инструкций Transact-SQL

Используйте инструкцию ALTER DATABASE, чтобы включить хранилище запросов для указанной базы данных. Пример:

Другие параметры синтаксиса, связанные с хранилищем запросов, см. в статье Параметры ALTER DATABASE SET (Transact-SQL).

Примечание

Хранилище запросов нельзя включить для базы данных master или tempdb.

Важно!

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

Скрипт XMLA для запуска

Расширенная трассировка событий включается с помощью команды скрипта создания объекта, аналогичной команде XML для аналитики, как показано ниже.

Следующие элементы должны быть определены пользователем с учетом потребностей трассировки:

trace_id
Определяет уникальный идентификатор для данной трассировки.

trace_name
Имя, присвоенное данной трассировке. Как правило, понятное определение трассировки. Обычно принято использовать в качестве имени значение trace_id .

AS_event
Событие служб Analysis Services, к которому должен быть предоставлен доступ. Имена событий см. в разделе События трассировки служб Analysis Services .

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

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

Просмотр отчетов для сервера служб Integration Services

Для текущего выпуска служб SQL ServerСлужбы Integration Servicesв среде SQL Server Management Studio предусмотрены стандартные отчеты, помогающие отслеживать проекты служб Службы Integration Services , развернутые на сервере Службы Integration Services . Дополнительные сведения об отчетах см. в разделе .

Просмотр отчетов для сервера служб Integration Services

В среде SQL Server Management Studioв обозревателе объектов разверните узел Каталоги служб Integration Services .
Щелкните правой кнопкой мыши SSISDB, затем выберите Отчеты и Стандартные отчеты.
Для просмотра отчета выберите один из следующих вариантов.
- Панель мониторинга служб Integration Services
- Все выполнения
- Все проверки
- Все операции
- Все соединения

Как работать с Activity Monitor

Activity Monitor можно открыть в SQL Server Management Studio toolbar используя иконку Activity Monitor на панели, сочетанием клавиш Ctrl+Alt+A или через контекстное меню в Object Explorer.

Как уже было сказано выше, Activity Monitor отслеживает только заранее определенный набор наиболее важных показателей производительности SQL Server. Дополнительных параметров указать нельзя, нельзя и удалить что-то из показателей. Мониторинг возможен только в режиме реального времени. Нет возможности сохранить результаты мониторинга для последующего анализа. Таким образом Activity Monitor – это полезный инструмент для беглого анализа и поиска неисправностей, но он не подходит для детального сбора информации, т.к. в нём отсутствует возможность гибкой настройки счётчиков производительности, указания пороговых значений и нет возможности сбора исторических данных.

Стандартные технологии отказоустойчивости для MS SQL Server 2017

AlwaysOn

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

Быстрота переключения в синхронном режиме-практически мгновенно и не требует вмешательства системного администратора и DBA, в асинхронном-зависит от текущего состояния БД-дублей, но обычно в среднем до 5 минут (также можно автоматизировать переключение силами одного DBA без привлечения системного администратора).

Признана Microsoft рекомендуемой технологией для БД. Доступна с лицензией Enterprise от 2012 версии и выше. Доступна с ограничениями с лицензией Standard

Кластеризация

Несмотря на простоту настройки, данное решение ненадежно в виду узкого места в виде единого для всех хранилища данных. В случае выхода из строя хранилища данных, восстановление займет достаточно длительный промежуток времени-более 1 часа.
Доступна с лицензией Standard до 2008 версии и выше

Репликация

Любая репликация подразумевает создание системных триггеров на каждую таблицу-участницу, а репликация моментальных снимков будет достаточно сильно нагружать основную БД. Поэтому репликацию моментальных снимков можно делать только в минимальные часы нагрузки БД (например, ночью), что неприемлемо, т к необходим горячий резерв. Репликация слиянием сложна в сопровождении для некоторых системы (например, CRM, NAV), также она не подходит для 1С в виду частого изменения структур БД.

Зеркалирование

Возможна в любом режиме, однако как и при AlwaysOn, синхронный режим обеспечивает максимальную надежность и быстрое переключение, а асинхронный режим дает максимальную скорость работы с основной БД, но возможны рассогласованности данных между всеми участниками, а также переключение не будет мгновенным. Здесь переключение на уровне БД обеспечивает следящий сервер автоматически (при например, нагрузке ЦП более, чем в 50% на основном сервере) или средствами DBA. Подключение же к другому серверу обеспечивается силами системного администратора. Резервная БД при любом типе зеркалирования находится в режиме постоянного восстановления, в следствие чего к ней невозможно обратиться.
Режим восстановления БД-полный.

Доставка журналов транзакций

Есть 2 режима-постоянное восстановление на резервном сервере или восстановление с отсрочкой.

Первый режим переводит резервную БД (как и при зеркалировании) в режим постоянного восстанавления и к ней невозможно обратиться.

Второй же режим переводит резервную БД в режим восстановления периодически в момент накатывания обновлений (между накатываниями обновлений резервная БД доступна, но это возможно при условии, что экземпляры MS SQL Server одной версии).

Принцип работы прост:

Периодически делается резервная копия журнала транзакций БД на источнике в общедоступную папку как источнику, так и резервному скулю (настраивается путь и расписание, по умолчанию-каждые 15 минут).
Резервный скуль периодически копирует получившуюся резервную копию журнала транзакций БД себе в локальную доступную папку (настраивается путь и расписание, по умолчанию-каждые 15 минут).
Резервный скуль восстанавливает журнал транзакций из скопированной резервной копии журнала транзакций (настраивается расписание, по умолчанию – каждые 15 минут).

Переключение можно автоматизировать на уровне БД-силами DBA, а на уровне подключений к серверу-на уровне системного администратора.

Различия в параметрах сортировки

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

Если таблица содержит столбцы типа CHAR или VARCHAR с параметрами сортировки, которые отличаются от параметров сортировки в базе данных, а также если в этих столбцах хранятся символы, не входящие в набор ASCII (например, двухбайтовые символы DBCS), технология отслеживания измененных данных не всегда будет сохранять измененные данные в соответствии с данными в базовых таблицах. Это связано с тем, что с переменными промежуточного хранилища не связаны параметры сортировки.

Чтобы обеспечить согласованность отслеживания измененных данных с базовыми таблицами, следует применять один из приведенных ниже подходов:

Используйте тип данных NCHAR и NVARCHAR для столбцов, содержащих данные, не относящиеся к набору ASCII.
Также можно использовать одинаковые параметры сортировки для столбцов и для базы данных.

Например, если в базе данных используются параметры сортировки SQL_Latin1_General_CP1_CI_AS, можно использовать следующую таблицу:

Технология отслеживания измененных данных может не получать двоичные данные для столбца C2, поскольку он использует другие параметры сортировки (Chinese_PRC_CI_AI). Чтобы избежать этой проблемы, используйте тип NVARCHAR: