Эффективное администрирование баз данных немыслимо без возможности retrospektive (ретроспективного) анализа. Архив выполненных запросов — это не просто лог-файл‚ а стратегический актив для DBA и разработчиков‚ позволяющий выявлять узкие места‚ расследовать инциденты безопасности‚ оптимизировать производительность и аудитить изменения данных. В этой статье мы подробно разберем механизмы сбора‚ хранения и анализа истории запросов в экосистеме Microsoft SQL Server и смежных технологиях.

Уровни архивирования: от мгновенного снимка к долгосрочному хранилищу

В SQL Server существует три основных уровня доступа к истории запросов‚ различающихся по глубине детализации‚ накладным расходам и сроку хранения:

• Текущая активность (Real-time): Динамические административные представления (DMV) — sys.dm_exec_requests‚ sys.dm_exec_sessions‚ sys.dm_exec_query_stats. Показывают‚ что происходит прямо сейчас или кэшированные планы недавних выполнений.
• Хранилище запросов (Query Store): Встроенный механизм (начиная с SQL Server 2016 / Azure SQL)‚ автоматически захватывающий текст запросов‚ планы выполнения и статистику рантайма на диск. Это основной инструмент для долгосрочного анализа производительности.
• Событийная трассировка (Extended Events / Profiler): Низкоуровневый аудит каждого события (RPC:Completed‚ SQL:BatchCompleted). Дает максимальную детализацию‚ но требует ручной настройки и управления файлами трассировки.

Query Store: «Черный ящик» производительности

Query Store (Хранилище запросов), это рекомендуемый Microsoft способ ведения архива выполненных запросов. В отличие от кэша планов (sys.dm_exec_query_stats)‚ который очищается при перезапуске сервера или нехватке памяти‚ Query Store сохраняет данные на диск.

Архитектура и компоненты

Данные в Query Store разделены на логические хранилища (внутренние таблицы‚ доступные через каталоговые представления):

1. Query Text Store (sys.query_store_query_text): Уникальные тексты запросов (параметризованные).
2. Query Store (sys.query_store_query): Связь текста с контекстом (настройки SET-опций‚ схема). Один текст может иметь несколько записей здесь.
3. Plan Store (sys.query_store_plan): Планы выполнения (XML)‚ привязанные к конкретному Query ID.
4. Runtime Stats Store (sys.query_store_runtime_stats / sys.query_store_runtime_stats_interval): Агрегированная статистика выполнения (CPU‚ Duration‚ Logical Reads‚ Row Count и т.д.) за интервалы времени (по умолчанию 1 час).

Ключевой запрос для анализа архива

Ниже приведен базовый запрос‚ объединяющий все сущности для получения полной картины: текста запроса‚ плана‚ статистики компиляции и времени выполнения.

<!-- Пример кода для статьи -->
SELECT 
 Txt.query_text_id‚
 Txt.query_sql_text‚
 Pln.plan_id‚
 Qry.query_id‚
 RtSt.runtime_stats_id‚
 RtSt.avg_duration‚
 RtSt.avg_cpu_time‚
 RtSt.avg_logical_io_reads‚
 RtSt.count_executions‚
 Rsi.start_time AS interval_start‚
 Rsi.end_time AS interval_end
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry 
 ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt 
 ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS RtSt 
 ON Pln.plan_id = RtSt.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS Rsi
 ON RtSt.runtime_stats_interval_id = Rsi.runtime_stats_interval_id
WHERE Rsi.start_time > DATEADD(DAY‚ -7‚ GETUTCDATE) -- Последние 7 дней
ORDER BY RtSt.avg_duration DESC;

Настройка сброса на диск (Data Flush Interval)

Важно понимать: статистика рантайма (Runtime Stats) сначала накапливается в памяти. Параметр DATA_FLUSH_INTERVAL_SECONDS (по умолчанию 900 сек / 15 мин) определяет‚ как часто данные гарантированно попадают на диск.

ALTER DATABASE [YourDB] SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 60);

Совет: Для систем с высокой требовательностью к сохранности данных (аудит‚ расследование инцидентов) снизьте этот интервал до 60 секунд‚ принимая незначительную нагрузку на подсистему ввода-вывода.

DMV для оперативного доступа: sys.dm_exec_requests и sys.dm_exec_query_stats

Если Query Store отключен или нужно посмотреть на «живую» нагрузку прямо сейчас‚ используются динамические представления.

Анализ текущих выполняющихся запросов

Полезен для поиска блокировок (blocking)‚ долгих операций и понимания текущей нагрузки.

SELECT 
 der.session_id‚
 der.status‚
 der.command‚
 der.wait_type‚
 der.wait_time‚
 der.blocking_session_id AS blocker_spid‚
 DB_NAME(der.database_id) AS database_name‚
 s.login_name‚
 s.host_name‚
 s.program_name‚
 LEFT((SELECT text FROM sys.dm_exec_sql_text(der.sql_handle))‚ 500) AS current_sql_text‚
 der.start_time‚
 der.total_elapsed_time / 1000.0 AS elapsed_time_sec‚
 der.cpu_time / 1000.0 AS cpu_time_sec‚
 der.logical_reads
FROM sys.dm_exec_requests der
LEFT JOIN sys.dm_exec_sessions s 
 ON s.session_id = der.session_id
WHERE der.session_id > 50 -- Исключаем системные процессы
 AND der.session_id <> @@SPID -- Исключаем свой запрос
ORDER BY der.total_elapsed_time DESC;

История из кэша планов (sys.dm_exec_query_stats)

Хранит агрегаты по запросам‚ планы которых сейчас в кэше. Внимание: Данные теряются при перезапуске сервера‚ сбросе кэша (DBCC FREEPROCCACHE) или вытеснении по LRU.

SELECT TOP 50
 qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms‚
 qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms‚
 qs.total_logical_reads / qs.execution_count AS avg_logical_reads‚
 qs.execution_count‚
 qs.last_execution_time‚
 qs.creation_time‚
 SUBSTRING(st.text‚ (qs.statement_start_offset/2) + 1‚
 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
 ELSE qs.statement_end_offset END ⎼ qs.statement_start_offset)/2) + 1) AS statement_text‚
 qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_elapsed_time / qs.execution_count DESC;

Определение времени последнего обращения к объектам

Частая задача: «Когда последний раз запрашивалась эта таблица?». Прямого лога «SELECT * FROM Table» в стандартном SQL Server нет (если не настроен Audit/XE)‚ но можно использовать статистику индексов.

sys.dm_db_index_usage_stats

Это представление сбрасывается при перезапуске сервера‚ но показывает факты обращений к индексам (Seek‚ Scan‚ Lookup‚ Update).

SELECT 
 OBJECT_NAME(ius.object_id‚ ius.database_id) AS table_name‚
 i.name AS index_name‚
 ius.last_user_seek‚
 ius.last_user_scan‚
 ius.last_user_lookup‚
 ius.last_user_update‚
 ius.user_seeks‚
 ius.user_scans‚
 ius.user_lookups
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i 
 ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE ius.database_id = DB_ID
 AND OBJECTPROPERTY(ius.object_id‚ 'IsUserTable') = 1
ORDER BY 
 GREATEST(ius.last_user_seek‚ ius.last_user_scan‚ ius.last_user_lookup) DESC;

Примечание: Функция GREATEST доступна в Azure SQL / SQL Server 2022+. В старых версиях используйте CASE/IIF.

Инструменты расширенного аудита и логирования

Когда встроенных DMV и Query Store недостаточно (нужен полный аудит безопасности‚ логирование всех ошибок‚ параметров вызовов)‚ применяются:

• Extended Events (XEvents): Наследники Profiler. Легковесные‚ мощные‚ позволяют писать в файл (event_file) или кольцевой буфер. Сессия system_health работает по умолчанию.
  • События: sql_batch_completed‚ rpc_completed‚ error_reported.
  • Поля: sql_text‚ username‚ client_hostname‚ duration.
• SQL Server Audit: Настроен на уровне сервера/БД для соответствия стандартам (GDPR‚ HIPAA‚ PCI DSS). Пишет в файл безопасности Windows или файл аудита SQL.
• Transaction Log (fn_dblog / fn_dump_dblog): Позволяет увидеть кто и что менял (INSERT/UPDATE/DELETE)‚ но не показывает чистые SELECT; Требует знания LSN и понимания внутренней структуры.

Клиентские инструменты и IDE: локальная история

Не стоит забывать‚ что история запросов часто нужна разработчику локально.

• SSMS (SQL Server Management Studio): Не имеет встроенного постоянного архива истории вкладок. Решается плагинами: SSMS Tools Pack (функция «Window Connection Coloring» и «Query History»)‚ SQL History (Redgate).
• DataGrip / JetBrains IDEs: Имеют мощный SQL Log (Help | Show SQL Log). Записывает абсолютно все запросы‚ посланные драйвером (включая служебные метаданные). Сохраняется в текстовые файлы с ротацией.
• Azure Data Studio: Расширения «Query History» позволяют сохранять выполненные скрипты в локальный JSON/SQLite.

Облачные платформы: Fabric‚ Azure SQL‚ Databricks

Современные PaaS-решения выносят мониторинг на уровень выше экземпляра сервера.

Microsoft Fabric / Azure SQL: Query Activity

В Microsoft Fabric (и современных версиях Azure SQL) доступен интерфейс Query Activity (Активность запросов). Это единая панель‚ объединяющая:

• Выполняющиеся сейчас запросы (Real-time).
• Историю выполненных запросов (Completed).
• Топ по длительности / частоте / ресурсам.

Преимущество: Не нужно писать T-SQL код для DMV — UI позволяет фильтровать по времени‚ пользователю‚ базе‚ тексту запроса и сразу смотреть план выполнения.

Azure Databricks SQL: Query History

В Databricks SQL история запросов доступна в UI и через системные таблицы (system.query.history). Показывает: полный текст‚ источник (UI‚ API‚ JDBC)‚ длительность‚ строки‚ I/O статистику и профиль выполнения (Photon/Classic).

Сравнительная таблица методов архивирования

Лучшие практики (Best Practices)

1. Включайте Query Store на всех продакшн-базах. Это стандарт де-факто. Установите OPERATION_MODE = READ_WRITE‚ CLEANUP_POLICY (например‚ 30-90 дней)‚ MAX_STORAGE_SIZE_MB (1-10% от размера БД).
2. Настройте Extended Events сессию для аудита ошибок и долгих запросов. Например‚ логгируйте sql_batch_completed WHERE duration > 5000000 (5 сек). Это даст детализацию‚ которой нет в агрегатах Query Store.
3. Не полагайтесь только на sys.dm_db_index_usage_stats для аудита доступа. Он сбрасывается при рестарте. Для критичных таблиц настройте Audit или XEvent на SELECT.
4. Автоматизируйте очистку. Query Store имеет встроенную очистку. Для XEvent файлов настройте max_file_size и max_rollover_files. Для логов клиентских инструментов — лимиты размера папки логов.
5. Используйте параметризацию. Query Store и кэш планов лучше работают с параметризованными запросами. Ad-hoc запросы с литералами загрязняют архив дубликатами.

Типичные сценарии расследования с использованием архива

Сценарий А: «Запрос работал быстро вчера‚ сегодня тормозит»

1. Откройте Query Store -> Regressed Queries (Регрессировавшие запросы).
2. Найдите Query ID по времени или тексту.
3. Посмотрите на графике появление нового Plan ID (точка на графике смены цвета/формы).
4. Сравните планы (старый быстрый vs новый медленный) -> Причина: отсутствие индекса‚ устаревшая статистика‚ parameter sniffing.
5. Примените Force Plan на старый хороший план как временное решение.

Сценарий Б: «Кто удалил данные из таблицы X в 14:30?»

1. Query Store не поможет (он не логгирует значения параметров DML по умолчанию‚ только агрегаты).
2. Проверьте SQL Server Audit (если настроен на DELETE для этой таблицы).
3. Если Audit нет — анализируйте Transaction Log через fn_dblog или сторонние инструменты (ApexSQL Log‚ SQL Log Rescue) по LSN / времени.
4. Проверьте Extended Events сессию (если ловили sql_batch_completed с фильтром по тексту).

Сценарий В: «Нужно восстановить запрос‚ который я писал 3 дня назад в SSMS и закрыл без сохранения»

1. Если стоит SSMS Tools Pack -> Меню SSMS Tools -> Query History.
2. Если пользуетесь DataGrip -> Help -> Show SQL Log -> Поиск по времени/тексту.
3. Если ничего не стоит — шансы минимальны (только если запрос попал в Query Store / кэш планов и не вытеснился).

Грамотная организация архива выполненных запросов — это многослойная система защиты и диагностики. Базовым слоем должен быть Query Store (включен по умолчанию в Azure SQL‚ обязателен к включению в On-Prem). Слойом аудита и детального расследования, Extended Events и SQL Audit. Слойом личной продуктивности разработчика — история в IDE (DataGrip‚ SSMS+Plugins). Облачные платформы (Fabric‚ Databricks) абстрагируют сложность‚ предоставляя единый UI Query Activity.

Инвестиции в настройку этих механизмов окупаются с первой же инцидентной ситуацией‚ когда вопрос «Что произошло?» получает точный‚ основанный на данных ответ за минуты‚ а не часы гаданий.

Статья подготовлена на основе актуальной документации Microsoft Learn (SQL Server‚ Azure SQL‚ Fabric)‚ практик сообщества SQL.ru‚ Stack Overflow и опыта администрирования высоконагруженных систем. Дата актуальности: .