Мониторинг активности в MSSQL: методы обнаружения неслужебных запросов

Для мониторинга активности в базах данных MSSQL существуют различные методы, включая Activity Monitor, SQL Server Audit, Extended Events, Query Store и Dynamic Management Views. Каждый из этих инструментов позволяет отслеживать активность пользователей, выявлять неслужебные запросы и анализировать производительность системы. SQL Server Audit является наиболее эффективным механизмом для обнаружения подозрительной активности за определенный период времени, тогда как Extended Events обеспечивает детальный мониторинг выполнения запросов с минимальным влиянием на производительность.

Содержание

• Введение в методы мониторинга MSSQL
• Activity Monitor: мониторинг в реальном времени
• SQL Server Audit: обнаружение неслужебных запросов
• Extended Events: детальный мониторинг производительности
• Query Store: история выполнения запросов
• Dynamic Management Views: диагностика сервера
• Сравнение методов мониторинга
• Практические рекомендации по внедрению
• Заключение
• Источники

Введение в методы мониторинга MSSQL

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

Основные сценарии, требующие мониторинга, включают:

• Обнаружение неслужебных запросов, которые могут указывать на попытки несанкционированного доступа
• Анализ производительности для выявления медленных запросов
• Контроль за использованием ресурсов базы данных
• Аудит изменений в данных и структуре

В этом руководстве мы рассмотрим все основные методы мониторинга активности в MSSQL, их преимущества и недостатки, а также практические примеры реализации.

Activity Monitor: мониторинг в реальном времени

Activity Monitor — это встроенный инструмент в SQL Server Management Studio (SSMS), который предоставляет обзор текущей активности на сервере. По информации из официальной документации Microsoft, для просмотра реальной активности требуются права VIEW SERVER STATE, а для раздела Data File I/O — дополнительные права CREATE DATABASE, ALTER ANY DATABASE или VIEW ANY DEFINITION.

Основные компоненты Activity Monitor включают:

Обзор (Overview)

Этот раздел показывает графики ключевых метрик:

• Процессорное время (% Processor Time)
• Задачи в ожидании (Waiting Tasks)
• Ввод-вывод базы данных (Database I/O)
• Запросы в секунду (Batch Requests/sec)

Процессы (Processes)

Предоставляет детальную информацию о текущих сеансах:

• SPID (идентификатор сессии)
• Пользователь и база данных
• Текущая команда и состояние
• Типы ожиданий и блокировки
• Использование памяти

Недавние ресурсоемкие запросы (Recent Expensive Queries)

Показывает запросы, выполненные за последние 30 секунд, с метриками:

• Использование CPU
• Количество операций чтения/записи
• Средняя длительность выполнения

Согласно руководству SQLShack, Activity Monitor особенно полезен для быстрого выявления проблемных запросов в реальном времени, но он не сохраняет историю активности для последующего анализа.

SQL Server Audit: обнаружение неслужебных запросов

SQL Server Audit — это встроенная система аудита, специально разработанная для обнаружения неслужебных запросов и подозрительной активности за определенные периоды времени. Как указано в официальной документации, эта система позволяет записывать действия на уровне сервера и базы данных, включая сами запросы, и сохранять их в файл или журнал событий Windows.

Основные возможности SQL Server Audit

SQL Server Audit собирает отдельные экземпляры действий на уровне сервера или базы данных и группирует их для мониторинга. Ключевая особенность — возможность настройки аудита конкретных типов событий, включая:

• Успешные и неудачные попытки входа в систему
• Запросы SELECT, INSERT, UPDATE, DELETE
• Операции создания, изменения и удаления объектов
• Изменения прав доступа и ролей

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

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

• Запросы, выполняемые вне рабочего времени
• Запросы с использованием оператора SELECT на все таблицы
• Запросы с высокой нагрузкой на ресурсы
• Запросы, содержащие ключевые слова, указывающие на потенциальные атаки

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

Extended Events: детальный мониторинг производительности

Extended Events (XE) — это легковесная система мониторинга, введенная в SQL Server 2008 и пришедшая на смену тяжелому SQL Profiler. Как описано в статье SQLShack, Extended Events позволяет захватывать широкий спектр событий движка с минимальным влиянием на производительность.

Ключевые преимущества Extended Events

• Низкое влияние на производительность (менее 5% нагрузки)
• Гибкая система фильтрации событий
• Возможность сохранения данных в различные цели (файлы, память, кольцевые буферы)
• Поддержка пользовательских событий и сеансов

Распространенные сценарии использования

Для мониторинга активности в MSSQL Extended Events применяются для:

• Захвата информации о выполнении запросов
• Мониторинга статистики ожиданий
• Отслеживания операций с tempdb
• Анализа производительности конкретных запросов

Событие sql_statement_completed захватывает все выполняемые запросы и включает следующие метрики:

• Время выполнения
• Количество логических чтений и записей
• Использование CPU
• Идентификатор плана выполнения

Extended Events особенно эффективны для детального анализа производительности и выявления проблемных запросов в реальном времени.

Query Store: история выполнения запросов

Query Store — это функция, представленная в SQL Server 2016, которая автоматически захватывает историю запросов, планов и статистики выполнения. Согласно документации Microsoft, Query Store работает как “черный ящик”, постоянно собирая информацию о компиляции и времени выполнения.

Основные возможности Query Store

Query Store автоматически отслеживает:

• Историю выполнения запросов
• Изменения планов выполнения
• Статистику по каждому запросу (CPU, время выполнения, чтение/запись)
• Регрессию производительности

Использование для мониторинга активности

Для администраторов баз данных Query Store предоставляет:

• Возможность сравнения производительности запросов во времени
• Анализ влияния статистики индексов на производительность
• Обнаружение регрессий после обновлений
• Историю планов выполнения для оптимизации

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

Dynamic Management Views: диагностика сервера

Dynamic Management Views (DMVs) и Dynamic Management Functions (DMFs) предоставляют доступ к внутреннему состоянию сервера и могут использоваться для мониторинга здоровья диагностики проблем и настройки производительности. Как указано в официальной документации, DMVs позволяют получать актуальную информацию о текущем состоянии системы.

Основные DMVs для мониторинга активности

Наиболее полезные DMVs для отслеживания активности включают:

sys.dm_exec_requests

Возвращает текущие запросы, выполняющиеся на сервере:

• Текст запроса
• Состояние выполнения
• Время ожидания
• Использование ресурсов

sys.dm_exec_query_stats

Предоставляет агрегированную статистику по запросам:

• Общее время выполнения
• Количество выполнений
• Среднее время выполнения
• Статистика по чтениям и записям

sys.dm_exec_sessions

Информация о текущих сессиях:

• Идентификатор сессии
• Имя пользователя
• Состояние сессии
• Время начала сессии

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

Сравнение методов мониторинга

Для обнаружения неслужебных запросов за определенный период времени наиболее эффективны SQL Server Audit и Extended Events. Первые предоставляют более структурированные данные для аудита, в то время как вторые дают более детальную информацию о производительности.

Практические рекомендации по внедрению

Оптимальная комбинация методов

Для комплексного мониторинга активности рекомендуется следующая комбинация инструментов:

1. Ежедневный мониторинг:

   • Activity Monitor для быстрого выявления проблем
   • Ключевые DMVs для диагностики текущей нагрузки

2. Детальный аудит:

   • SQL Server Audit для отслеживания неслужебных запросов
   • Extended Events для анализа производительности

3. Долгосрочный анализ:

   • Query Store для отслеживания производительности запросов
   • Исторические данные SQL Server Audit для анализа тенденций

Настройка аудита для обнаружения неслужебных запросов

Пример конфигурации SQL Server Audit:

-- Создание цели аудита
CREATE SERVER AUDIT MSSQL_Audit
TO FILE (FILEPATH = 'C:\Audit\');
GO

-- Включение аудита
ALTER SERVER AUDIT MSSQL_Audit WITH (STATE = ON);
GO

-- Создание спецификации аудита для базы данных
CREATE DATABASE AUDIT SPECIFICATION Detect_Malicious_Queries
FOR SERVER AUDIT MSSQL_Audit
ADD (SELECT ON SCHEMA::dbo BY PUBLIC),
    (INSERT ON SCHEMA::dbo BY PUBLIC),
    (UPDATE ON SCHEMA::dbo BY PUBLIC),
    (DELETE ON SCHEMA::dbo BY PUBLIC);
GO

-- Включение спецификации
ALTER DATABASE AUDIT SPECIFICATION Detect_Malicious_Queries
WITH (STATE = ON);
GO

Оптимизация Extended Events для мониторинга запросов

Пример сеанса Extended Events для мониторинга медленных запросов:

-- Создание цели (файл)
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'SlowQueryMonitor')
    DROP EVENT SESSION SlowQueryMonitor ON SERVER;
GO

CREATE EVENT SESSION SlowQueryMonitor ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.username
    )
    WHERE 
        duration > 1000000  -- Запросы дольше 1 секунды
        AND sqlserver.database_name NOT IN ('master', 'tempdb', 'model', 'msdb')
)
ADD TARGET package0.asynchronous_file_target(
    SET filename = 'C:\XE\SlowQueryMonitor.xel',
    metadatafile = 'C:\XE\SlowQueryMonitor.xem'
);
GO

-- Запуск сеанса
ALTER EVENT SESSION SlowQueryMonitor ON SERVER STATE = START;
GO

Заключение

Мониторинг активности в базах данных MSSQL является критически важной задачей для обеспечения безопасности и производительности. В зависимости от конкретных требований могут использоваться различные инструменты: Activity Monitor для реального времени, SQL Server Audit для обнаружения неслужебных запросов, Extended Events для детального анализа производительности, Query Store для долгосрочного отслеживания и DMVs для диагностики.

Для эффективного обнаружения неслужебных запросов за определенный период времени наиболее подходят SQL Server Audit и Extended Events, которые позволяют собирать исторические данные и анализировать их для выявления аномальной активности. Комбинируя несколько методов мониторинга, администраторы могут обеспечить комплексный контроль над состоянием базы данных и своевременно реагировать на потенциальные угрозы.

Источники

1. Open Activity Monitor (SSMS) - SQL Server

2. SQL Server Audit (Database Engine) - SQL Server

3. Activity Monitor - SQL Server

4. SQL Server Activity Monitor

5. Using SQL Server Extended Events to monitor query performance

6. Monitor Performance by Using the Query Store - SQL Server

7. System dynamic management views (Transact-SQL) - SQL Server