Как посмотреть историю запросов к sql server

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

Системные представления позволяют получить сведения о недавно выполненных запросах. Например, sys.dm_exec_query_stats содержит статистику по кэшу запросов, включая план выполнения, время запуска и количество запусков. Для расшифровки текста запроса используется связка с sys.dm_exec_sql_text.

Если задача – отслеживание активности в реальном времени, можно использовать Extended Events или SQL Server Profiler. Первый предпочтительнее на современных версиях SQL Server, так как потребляет меньше ресурсов и поддерживает гибкую фильтрацию событий. В Profiler стоит избегать отслеживания всех событий без фильтров – это создаёт серьёзную нагрузку.

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

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

Как просмотреть последние выполненные запросы с помощью sys.dm_exec_query_stats

Для получения информации о последних выполненных запросах используется представление sys.dm_exec_query_stats, которое содержит статистику выполнения кэшированных планов запросов. Чтобы получить текст запросов, время последнего выполнения и другие параметры, необходимо соединить его с sys.dm_exec_sql_text и sys.dm_exec_query_plan.

Пример запроса:


SELECT
TOP 50
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_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 query_text,
qp.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
qs.last_execution_time DESC;

Пояснения:

qs.creation_time – когда запрос впервые попал в план кэш;
qs.last_execution_time – время последнего выполнения;
qs.execution_count – сколько раз выполнялся запрос;
avg_cpu_time – среднее время использования CPU;
avg_elapsed_time – среднее общее время выполнения;
query_text – фрагмент текста SQL-запроса;
query_plan – XML-план выполнения.

Для более точной фильтрации можно использовать условия по времени выполнения, CPU или конкретным ключевым словам в тексте запроса. Очистка кэша (DBCC FREEPROCCACHE) приведёт к потере всех данных из sys.dm_exec_query_stats.

Как получить текст запроса по его идентификатору

Чтобы извлечь текст запроса по его идентификатору, используйте представление системного кэша `sys.dm_exec_query_stats` вместе с функцией `sys.dm_exec_sql_text`. Пример:

SELECT qs.query_hash, qs.query_plan_hash, qs.sql_handle, st.text AS query_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE qs.sql_handle = 0x03000500... -- Укажите нужный идентификатор

Если известен `query_hash`, фильтрация возможна по нему:

SELECT st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE qs.query_hash = 0x123456789ABCDEF0

Для получения информации о последних выполнениях запроса с конкретным `plan_handle` можно использовать:

SELECT st.text, qs.execution_count, qs.total_worker_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE qs.plan_handle = 0x06000600...

Если идентификатор взят из журнала Extended Events или трассировки, он может потребовать преобразования или извлечения из вложенной структуры. В этом случае используйте функции `CONVERT` и `CAST`, если необходимо привести тип к `varbinary`.

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

Как отследить длительные запросы через sys.dm_exec_requests

Для получения информации о текущих выполняемых запросах используется представление sys.dm_exec_requests. Оно позволяет выявить запросы, которые работают непропорционально долго, и оценить их влияние на систему.

Для определения длительных операций целесообразно отфильтровать запросы по полю cpu_time или total_elapsed_time. Например:

SELECT session_id, status, start_time, cpu_time, total_elapsed_time, command, sql_handle FROM sys.dm_exec_requests WHERE total_elapsed_time > 5000 ORDER BY total_elapsed_time DESC;

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

Чтобы получить текст запроса, можно использовать функцию sys.dm_exec_sql_text:

SELECT r.session_id, t.text, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.total_elapsed_time > 5000;

Анализируя данные по session_id, можно также обратиться к sys.dm_exec_sessions и sys.dm_exec_connections для получения информации о пользователе, клиентском приложении и хосте.

Для исключения фоновых системных процессов следует добавить фильтр: WHERE r.session_id > 50.

Мониторинг этого представления помогает оперативно находить и анализировать потенциально проблемные запросы до того, как они повлияют на производительность базы данных.

Как использовать журнал событий SQL Server для анализа запросов

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

Создание сеанса начинается с определения нужных событий. Чтобы отследить выполнение запросов, выбираются события sql_statement_completed и rpc_completed. Они позволяют получить информацию о выполненных T-SQL командах и хранимых процедурах соответственно.

Пример создания сеанса:

CREATE EVENT SESSION QueryTracking
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text, sqlserver.client_hostname, sqlserver.username)
WHERE duration > 100000)
ADD TARGET package0.ring_buffer;
GO
ALTER EVENT SESSION QueryTracking ON SERVER STATE = START;

Фильтрация по длительности (в микросекундах) позволяет исключить короткие запросы, не влияющие на производительность. Дополнительно можно добавить фильтры по пользователю или базе данных.

Для анализа собранных данных используется запрос:

SELECT
event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text,
event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(256)') AS client_hostname,
event_data.value('(event/action[@name="username"]/value)[1]', 'NVARCHAR(256)') AS username,
event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') AS duration
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON s.address = t.event_session_address
WHERE s.name = 'QueryTracking'
AND t.target_name = 'ring_buffer'
) AS data;

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

Сеанс можно остановить и удалить при необходимости:

ALTER EVENT SESSION QueryTracking ON SERVER STATE = STOP;
DROP EVENT SESSION QueryTracking ON SERVER;

Extended Events не нагружают систему так сильно, как Profiler, и подходят для работы как в режиме реального времени, так и при последующем разборе накопленных данных.

Как настроить Extended Events для фиксации истории запросов

Откройте SQL Server Management Studio и подключитесь к нужному экземпляру. В меню «Management» выберите «Extended Events», затем – «Sessions». Щёлкните правой кнопкой и выберите «New Session…».

Укажите имя сессии, например QueryHistoryCapture. Снимите галочку «Start the event session at server startup», если запуск вручную предпочтительнее. Перейдите на вкладку «Events». Нажмите «Add Event», введите sql_batch_completed и rpc_completed в поиск, добавьте оба события.

На вкладке «Global Fields (Actions)» добавьте: sql_text, username, client_hostname, database_name, session_id, query_hash. Это позволит зафиксировать текст запроса и сопутствующие параметры.

Перейдите на вкладку «Data Storage». Выберите «Event File», укажите путь, например: C:\XELogs\QueryHistory.xel. Ограничьте размер файла и включите циклическую перезапись, чтобы избежать переполнения.

Нажмите OK для создания сессии. Чтобы запустить её, кликните правой кнопкой на созданной сессии и выберите «Start Session».

Для чтения логов используйте:


SELECT
event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(256)') AS username,
event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(256)') AS client_hostname,
event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(256)') AS database_name,
event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id,
event_data.value('(event/@timestamp)[1]', 'datetime2') AS [timestamp]
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\XELogs\QueryHistory*.xel', NULL, NULL, NULL)
) AS events

Настройка завершена. Журналы будут пополняться автоматически при выполнении запросов.

Как анализировать кэш запросов для выявления повторяющихся операций

Анализ кэша запросов в SQL Server позволяет выявить часто выполняемые запросы и повторяющиеся операции, которые могут негативно влиять на производительность системы. Чтобы эффективно анализировать кэш, используйте следующие методы:

Использование представлений DMVs (Dynamic Management Views): Представление sys.dm_exec_cached_plans позволяет получить информацию о текущих запросах, находящихся в кэше SQL Server. Это представление помогает выявить часто повторяющиеся запросы.
Поиск по схожим запросам: Запросы, которые используют одинаковые операторы или параметры, могут попадать в кэш и выполнять схожие операции. Используйте представление sys.dm_exec_query_stats для получения статистики по каждому запросу, находящемуся в кэше.
Анализ выполнения запросов: Используйте sys.dm_exec_requests и sys.dm_exec_sessions для мониторинга текущих запросов и их состояния. Эти представления помогут отслеживать повторяющиеся запросы и их выполнение в реальном времени.

Чтобы улучшить анализ, следуйте рекомендациям:

Идентификация повторяющихся запросов: Обратите внимание на запросы с высокой частотой использования. Часто выполняемые запросы могут быть результатом одинаковых операций, которые нужно оптимизировать.
Удаление устаревших планов: Если запросы долго не выполнялись, старые планы могут занимать место в кэше, не принося пользы. Используйте DBCC FREEPROCCACHE для удаления неактуальных планов.
Использование параметризированных запросов: Параметризация запросов помогает избежать создания множества схожих планов для различных значений параметров, что снижает нагрузку на кэш.

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

Вопрос-ответ:

Как я могу узнать историю запросов в SQL Server?

Чтобы просмотреть историю запросов в SQL Server, можно воспользоваться функцией динамических управляемых представлений, таких как `sys.dm_exec_query_stats`, `sys.dm_exec_sql_text` и `sys.dm_exec_query_plan`. Эти представления содержат информацию о выполненных запросах, их производительности и планах выполнения. Вы можете использовать эти данные для анализа запросов, которые выполнялись в сервере, а также для диагностики производительности.

Есть ли способ получить список всех запросов, выполняемых на сервере в реальном времени?

Для этого можно использовать представление `sys.dm_exec_requests`, которое показывает информацию о текущих запросах, выполняющихся в SQL Server. Вы также можете объединить его с другими представлениями, такими как `sys.dm_exec_sql_text`, чтобы получить полные данные о запросах, включая текст SQL-запроса и идентификаторы сессий. Такой подход позволяет отслеживать активные запросы в реальном времени.

Можно ли увидеть всю историю запросов в SQL Server, даже если сервер был перезагружен?

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

Какие инструменты можно использовать для более детального анализа выполнения запросов в SQL Server?

Для более детального анализа запросов в SQL Server можно использовать SQL Server Profiler, который позволяет отслеживать и анализировать выполнение SQL-запросов в реальном времени. Также полезным инструментом является Dynamic Management Views (DMV), такие как `sys.dm_exec_query_stats`, для получения статистики по выполненным запросам, а также `sys.dm_exec_query_plan` для анализа планов выполнения запросов. Эти инструменты помогут вам детально изучить производительность запросов и выявить проблемные участки.

Как сохранить историю запросов в SQL Server для дальнейшего анализа?

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