Мониторинг MS SQL с помощью Proto Observability

На этой странице:

• Сбор метрик MS SQL
  • Конфигурация MS SQL
    • Режим аутентификации
    • Пользователь для мониторинга
    • Дополнительные права
  • Драйвер подключения
  • Конфигурация ProtoOBP агента
    • Если агент запускается в виде службы на хосте
    • Если агент запускается в виде Docker контейнера
  • Проверка
  • Собираемые метрики
    • Лейблы
      • Общие (на всех метриках)
      • Специфичные для MS SQL
    • Экземпляр: доступ к данным и буферный кэш
    • Память
    • Статистика SQL
    • Блокировки и защёлки
    • Транзакции и хранилище версий
    • tempdb
    • Сервер и планировщик
    • Базы данных
    • Файлы баз данных
    • Ввод-вывод по файлам
    • Индексы и фрагментация
    • Размеры таблиц (table_size_metrics)
    • AlwaysOn и отказоустойчивый кластер
    • Log shipping
    • Extended events
    • Метрики уровня запросов (dbm)
    • Задания SQL Server Agent (dbm)
  • Сервисные проверки
  • Ключевые метрики для дашбордов и алертов
• Сбор логов MS SQL
  • Конфигурация ProtoOBP агента
    • Если агент запускается в виде службы на хосте
    • Если агент запускается в виде Docker контейнера
  • Проверка

Сбор метрик MS SQL

Интеграция sqlserver подключается к MS SQL по обычному wire-протоколу (порт 1433), а не через JMX — поэтому образ агента используется обычный, без суффикса -jmx. Проверка читает системные представления и DMV (sys.dm_os_performance_counters, sys.dm_io_virtual_file_stats, sys.databases, sys.master_files, sys.dm_db_index_usage_stats, sys.dm_exec_query_stats и др.), поэтому пользователю агента достаточно прав read-only.

Поддерживаются MS SQL 2012, 2014, 2016, 2017, 2019, 2022 и 2025 (для 2025 требуется агент версии 7.79 и выше).

Часть семейств метрик включается отдельными флагами в блоке database_metrics: внутри instances::

Конфигурация MS SQL

Режим аутентификации

Убедитесь, что экземпляр MS SQL допускает аутентификацию средствами SQL Server:

Свойства сервера → Безопасность → Проверка подлинности SQL Server и Windows

На Windows-хосте можно вместо логина/пароля использовать доменную аутентификацию — см. connection_string ниже.

Пользователь для мониторинга

Создайте логин с правами read-only для агента ProtoOBP в базе master:

CREATE LOGIN protoobp WITH PASSWORD = '<PASSWORD>';
USE master;
CREATE USER protoobp FOR LOGIN protoobp;
GRANT SELECT on sys.dm_os_performance_counters to protoobp;
GRANT VIEW SERVER STATE to protoobp;
GRANT CONNECT ANY DATABASE to protoobp;

GRANT CONNECT ANY DATABASE нужен для метрик уровня баз данных (размеры файлов, состояние, индексы). Для MS SQL 2012 команда GRANT CONNECT ANY DATABASE недоступна — вместо неё создайте пользователя protoobp в каждой прикладной базе:

USE [<DB_NAME>];
CREATE USER protoobp FOR LOGIN protoobp;

Дополнительные права

GRANT VIEW ANY DEFINITION to protoobp;

-- Только для log shipping и заданий SQL Server Agent:
USE msdb;
CREATE USER protoobp FOR LOGIN protoobp;
GRANT SELECT to protoobp;

Драйвер подключения

Способ подключения задаётся параметром connector:

На Windows-хосте имя драйвера должно совпадать с тем, что зарегистрировано в системе: драйвера по умолчанию у диспетчера ODBC нет.

1. Выведите список установленных 64-разрядных ODBC-драйверов:

   Get-OdbcDriver -Platform 64-bit | Select-Object -ExpandProperty Name
   

   Если командлет недоступен, тот же список хранится в реестре:

   Get-ItemProperty 'HKLM:\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers'
   

2. Перенесите имя из вывода в параметр driver посимвольно — например ODBC Driver 18 for SQL Server, ODBC Driver 17 for SQL Server, ODBC Driver 11 for SQL Server или SQL Server. Подходит любой из уже установленных драйверов, ставить новый нужно только если в системе нет ни одного.

3. Если драйвер требуется установить, выберите версию по версии ОС и обязательно 64-разрядную — 32-разрядные драйверы агент не видит:

   Версия Windows Server	Максимальная версия Microsoft ODBC Driver for SQL Server
   2016 и новее	18
   2012 R2	17

На Linux-хосте нужны дополнительные шаги:

1. Установите ODBC-драйвер для SQL Server — например, Microsoft ODBC Driver for SQL Server или FreeTDS.
2. Скопируйте файлы odbc.ini и odbcinst.ini в каталог /opt/protoobp-agent/embedded/etc.
3. В conf.yaml укажите connector: odbc и имя драйвера ровно так, как оно записано в odbcinst.ini.

Конфигурация ProtoOBP агента

Если агент запускается в виде службы на хосте

1. Создайте файл конфигурации проверки:

   • Linux — /etc/protoobp-agent/conf.d/sqlserver.d/conf.yaml
   • Windows — C:\ProgramData\ProtoOBP\conf.d\sqlserver.d\conf.yaml

   init_config:
   
   instances:
     - host: "<SQL_HOST>,<SQL_PORT>"
       username: protoobp
       password: "<PASSWORD>"
       # На Linux — только odbc; на Windows доступен также adodbapi.
       connector: odbc
       driver: "ODBC Driver 18 for SQL Server"
       # По умолчанию проверка подключается к master.
       database: master
       # Автообнаружение прикладных баз: метрики индексов, файлов и размеров
       # собираются по каждой найденной базе.
       database_autodiscovery: true
       autodiscovery_include:
         - ".*"
       database_metrics:
         master_files_metrics:
           enabled: true
         db_fragmentation_metrics:
           enabled: false
         table_size_metrics:
           enabled: false
         ao_metrics:
           enabled: false
       # Включите для метрик уровня SQL-запросов sqlserver_queries_*:
       # dbm: true
   

   Если используется автообнаружение порта, укажите 0 в качестве <SQL_PORT>.

   Для доменной аутентификации на Windows логин и пароль не указываются:

   instances:
     - host: "<SQL_HOST>,<SQL_PORT>"
       connection_string: "Trusted_Connection=yes"
   

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

2. Перезапустите ProtoOBP агента:

   • Linux — systemctl restart protoobp-agent
   • Windows — перезапустите службу ProtoOBP Agent

3. Выполните проверку работы агента и убедитесь, что в разделе sqlserver нет ошибок.

Если агент запускается в виде Docker контейнера

1. Добавьте autodiscovery-лейблы к Docker контейнеру с MS SQL.

   • в docker-compose.yaml

     labels:
       com.protoobp.ad.check_names: '["sqlserver"]'
       com.protoobp.ad.init_configs: "[{}]"
       com.protoobp.ad.instances: '[{"host": "%%host%%,%%port%%", "username": "protoobp", "password": "<PASSWORD>", "connector": "odbc", "driver": "FreeTDS", "database_autodiscovery": true, "autodiscovery_include": [".*"]}]'
     

   • или в Dockerfile

     LABEL "com.protoobp.ad.check_names"='["sqlserver"]'
     LABEL "com.protoobp.ad.init_configs"='[{}]'
     LABEL "com.protoobp.ad.instances"='[{"host": "%%host%%,%%port%%", "username": "protoobp", "password": "<PASSWORD>", "connector": "odbc", "driver": "FreeTDS", "database_autodiscovery": true, "autodiscovery_include": [".*"]}]'
     

2. Примените изменения лейблов для контейнера с MS SQL (перезапуском контейнера), а также перезапустите контейнер с агентом ProtoOBP.

3. Выполните проверку работы агента и убедитесь, что в разделе sqlserver нет ошибок.

Проверка

Убедитесь, что проверка запустилась и собирает метрики:

docker exec protoobp-agent agent status | grep -A 12 "sqlserver ("

Для агента, установленного службой на Linux-хосте:

protoobp-agent status | grep -A 12 "sqlserver ("

Ожидаемый вывод — instance со статусом [OK] и ненулевыми Metric Samples:

    sqlserver (<ВЕРСИЯ ПРОВЕРКИ>)
    -----------------------------
      Instance ID: sqlserver:<хеш> [OK]
      Configuration Source: file:/etc/protoobp-agent/conf.d/sqlserver.d/conf.yaml
      Total Runs: 32
      Metric Samples: Last Run: 118, Total: 3,776
      Events: Last Run: 0, Total: 0
      Service Checks: Last Run: 2, Total: 64
      Average Execution Time : 145ms

Полный список собранных серий с тегами:

docker exec protoobp-agent agent check sqlserver

Если в выводе есть строка Instance ID: ... [ERROR], разверните её целиком — типовые причины: не установлен ODBC-драйвер, не выдан VIEW SERVER STATE, закрыт порт 1433, отключена аутентификация SQL Server.

Строка Last Successful Execution Date : Never означает, что проверка ни разу не подключилась к СУБД: метрик не будет, пока ошибка подключения не устранена.

Собираемые метрики

Большинство метрик MS SQL имеют тип gauge (мгновенное значение на момент сбора): счётчики производительности sys.dm_os_performance_counters уже приходят как «в секунду» или как текущее значение, агент не пересчитывает их. Метрики типа count (sqlserver_files_*, sqlserver_index_*, sqlserver_queries_*, sqlserver_procedures_*) — кумулятивные счётчики, монотонно растущие от старта экземпляра; для дашбордов применяйте PromQL rate(... [5m]).

Лейблы

Общие (на всех метриках)

Добавляются агентом и ProtoOBP backend’ом:

Специфичные для MS SQL

Экземпляр: доступ к данным и буферный кэш

Метрики уровня экземпляра, без лейбла db. Источник — счётчики производительности (instance_metrics).

Память

Статистика SQL

Блокировки и защёлки

Транзакции и хранилище версий

tempdb

Требует tempdb_file_space_usage_metrics (включён по умолчанию). Несут лейбл db. На управляемых облачных СУБД эти метрики не публикуются.

Сервер и планировщик

sqlserver_server_* требуют server_state_metrics (включён по умолчанию), sqlserver_scheduler_* и sqlserver_task_* — task_scheduler_metrics (выключен по умолчанию).

Базы данных

Несут лейбл db. Счётчики транзакций и журнала берутся из объекта производительности Databases, состояние — из sys.databases (db_stats_metrics).

Файлы баз данных

Из sys.database_files (db_files_metrics, включён по умолчанию) и sys.master_files (master_files_metrics, выключен по умолчанию). Несут лейблы db, file_id, file_type, file_location, database_files_state_desc.

Ввод-вывод по файлам

Из sys.dm_io_virtual_file_stats (file_stats_metrics, включён по умолчанию). Тип count — кумулятивные счётчики, применяйте rate(). Лейблы logical_name, file_location, db, state.

Индексы и фрагментация

sqlserver_index_* — из sys.dm_db_index_usage_stats (index_usage_metrics, включён по умолчанию), тип count. Метрики фрагментации — из sys.dm_db_index_physical_stats (db_fragmentation_metrics, выключен по умолчанию). Представление sys.dm_db_index_usage_stats ограничено текущей базой, поэтому включите database_autodiscovery или задайте database.

Размеры таблиц (table_size_metrics)

Выключено по умолчанию; tempdb не собирается. Лейблы db, schema, table.

AlwaysOn и отказоустойчивый кластер

Требуют ao_metrics / fci_metrics и права VIEW ANY DEFINITION.

Log shipping

Требуют primary_log_shipping_metrics / secondary_log_shipping_metrics и пользователя в msdb с ролью db_datareader.

Extended events

Требуют xe_metrics. Лейбл session_name.

Метрики уровня запросов (dbm)

Доступны только при dbm: true. Тип count (кумулятивные), несут лейбл query_signature (нормализованный запрос), а duration.* — user, db, app.

Метрики хранимых процедур (лейблы db, procedure_name):

Помимо метрик, при dbm: true собираются активные сессии, тексты нормализованных запросов и планы выполнения. Тонкая настройка — параметрами query_metrics, procedure_metrics, query_activity, collect_settings и stored_procedure_characters_limit.

Задания SQL Server Agent (dbm)

Требуют dbm: true, agent_jobs.enabled: true и доступа к msdb.

Сервисные проверки

Ключевые метрики для дашбордов и алертов

Доступность экземпляра

• Сервисная проверка sqlserver.can_connect в состоянии CRITICAL либо отсутствие свежих серий по host/port — алерт: MS SQL недоступен или агент не может подключиться.
• sqlserver_database_state != 0 по конкретной базе — база не в состоянии Online (Suspect, Recovery_Pending, Offline).
• sqlserver_server_uptime резко уменьшился — экземпляр был перезапущен.

Нагрузка и компиляция

• sqlserver_stats_batch_requests — базовая метрика нагрузки; сопоставляйте с временем отклика приложения.
• sqlserver_stats_sql_compilations / sqlserver_stats_batch_requests устойчиво выше 0.1 — много ad-hoc SQL, план не переиспользуется.
• sqlserver_stats_sql_recompilations растёт — перекомпиляции съедают CPU (устаревшая статистика, RECOMPILE, изменения схемы).
• sqlserver_stats_connections близко к лимиту пула приложения — новые клиенты начнут получать отказы.

Память и кэш

• sqlserver_buffer_cache_hit_ratio устойчиво ниже 0.95–0.99 на OLTP-нагрузке — данные не помещаются в буферный пул.
• sqlserver_buffer_page_life_expectancy резко падает (типовой ориентир — 300 секунд на 4 ГБ буферного пула) — вытеснение страниц, нехватка памяти.
• sqlserver_memory_memory_grants_pending > 0 — запросы стоят в очереди за рабочей памятью.
• sqlserver_memory_total_server_memory близко к sqlserver_server_target_memory — менеджер памяти упёрся в max server memory.

Блокировки

• sqlserver_locks_deadlocks > 0 — алерт: взаимоблокировки.
• sqlserver_stats_procs_blocked стабильно > 0 — цепочки блокировок.
• sqlserver_stats_lock_waits растёт вместе с временем отклика — конкуренция за строки и страницы.
• sqlserver_latches_latch_wait_time высок — конкуренция за внутренние структуры (часто симптом узкого места в tempdb или в вводе-выводе).

Ввод-вывод

• Средняя задержка файла = rate(sqlserver_files_io_stall[5m]) / (rate(sqlserver_files_reads[5m]) + rate(sqlserver_files_writes[5m])) — выше 20 мс для файлов данных и выше 5 мс для журнала = узкое место в дисках.
• rate(sqlserver_files_read_io_stall_queued[5m]) заметно больше нуля — срабатывает ограничение ввода-вывода на уровне пула ресурсов.
• sqlserver_buffer_checkpoint_pages всплесками — массовые сбросы страниц.

Журнал транзакций и tempdb

• sqlserver_database_log_flush_wait растёт — узкое место в записи журнала (обычно диск под LOG-файлом).
• sqlserver_database_files_space_used / sqlserver_database_files_size по file_type=LOG приближается к 1 — журнал не усекается (проверьте модель восстановления и резервное копирование журнала).
• sqlserver_tempdb_file_space_usage_free_space падает, а sqlserver_transactions_version_store_size растёт — долгая транзакция удерживает версии строк.

Транзакции

• sqlserver_transactions_longest_transaction_running_time большое — «висящая» транзакция держит блокировки и мешает очистке хранилища версий.
• sqlserver_database_active_transactions по базе аномально высок — всплеск незакрытых транзакций.

Индексы и рост данных

• sqlserver_index_user_scans доминирует над sqlserver_index_user_seeks — запросы идут в сканирование, не хватает подходящих индексов.
• sqlserver_index_user_updates высок при нулевых user_seeks / user_scans — индекс только обслуживается, но не используется.
• sqlserver_database_avg_fragmentation_in_percent > 30 % при большом index_page_count — кандидат на REBUILD / REORGANIZE.
• sqlserver_database_files_size, sqlserver_table_total_size — тренд роста для планирования ёмкости.

Резервные копии и AlwaysOn

• sqlserver_database_backup_count не растёт по прикладной базе — резервное копирование не выполняется.
• sqlserver_ao_ag_sync_health < 2 или sqlserver_ao_replica_sync_state != 2 — группа доступности рассинхронизирована.
• sqlserver_ao_secondary_lag_seconds выше SLA либо растущая sqlserver_ao_redo_queue_size — вторичная реплика не успевает применять журнал.
• sqlserver_log_shipping_secondary_time_since_restore больше sqlserver_log_shipping_secondary_restore_threshold — отставание доставки журналов.

Запросы (при dbm: true)

• topk по rate(sqlserver_queries_worker_time[5m]) per query_signature — самые «дорогие» по CPU нормализованные запросы.
• rate(sqlserver_queries_spills[5m]) > 0 — запрос регулярно сбрасывает промежуточные результаты в tempdb.
• sqlserver_queries_duration_max per user/db большое — конкретный пользователь или приложение крутит долгий запрос.

Сбор логов MS SQL

Логи MS SQL собираются ProtoOBP агентом и отправляются в бэкенд ProtoOBP. Здесь — только специфичные для MS SQL настройки. Включение логов на стороне агента (logs_enabled, logs_pobp_url) описано в Получение данных логов.

Конфигурация ProtoOBP агента

Если агент запускается в виде службы на хосте

Дополните уже существующий файл конфигурации проверки (тот же, в котором лежит instances: для метрик) блоком logs::

logs:
  - type: file
    encoding: utf-16-le
    path: 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG'
    source: sqlserver
    service: sqlserver
    log_processing_rules:
      - type: multi_line
        name: log_start_with_date
        pattern: \d{4}-\d{2}-\d{2}

На Linux-хосте — без encoding и с другим путём:

logs:
  - type: file
    path: /var/opt/mssql/log/errorlog
    source: sqlserver
    service: sqlserver
    log_processing_rules:
      - type: multi_line
        name: log_start_with_date
        pattern: \d{4}-\d{2}-\d{2}

Перезапустите агента (systemctl restart protoobp-agent либо перезапуск службы ProtoOBP Agent на Windows).

Если агент запускается в виде Docker контейнера

Шаг 1 — включите сбор логов в самом агенте через переменные окружения и смонтируйте директорию JSON-логов docker-демона:

services:
  protoobp-agent:
    environment:
      POBP_LOGS_ENABLED: "true"
      POBP_LOGS_CONFIG_USE_HTTP: "true"
      POBP_LOGS_CONFIG_LOGS_POBP_URL: <protoobp-backend>:<port>
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro
      # Прямое чтение JSON-лог файлов docker-демона быстрее,
      # чем тянуть их через Docker socket API.
      - /var/lib/docker/containers:/var/lib/docker/containers:ro

Шаг 2 — добавьте лейбл com.protoobp.ad.logs к контейнеру MS SQL. Агент подхватит stdout/stderr контейнера и пометит записи указанными source и service:

services:
  mssql:
    labels:
      com.protoobp.ad.logs: '[{"source": "sqlserver", "service": "sqlserver", "log_processing_rules": [{"type": "multi_line", "name": "log_start_with_date", "pattern": "\\d{4}-\\d{2}-\\d{2}"}]}]'

Проверка

В выводе agent status в разделе Logs Agent должен быть источник с Source: sqlserver, Status: OK и ненулевым BytesRead:

docker exec protoobp-agent agent status | grep -A 60 'Logs Agent'

Ненулевые Lines Combined / MultiLine matches подтверждают, что правило multi_line склеивает многострочные записи в одну. У источника типа «JSON-файл docker-контейнера» поле Status обычно OK, но даже если оно отображается как Pending — ориентируйтесь на BytesRead и LogsProcessed.