方法一、查询动态管理视图(DMV,实时查看近期 SQL)
SELECT
deqs.last_execution_time AS [执行时间],
dest.text AS [SQL_语句]
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY
sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE
deqs.last_execution_time BETWEEN '开始时间' AND '结束时间'
ORDER BY
deqs.last_execution_time DESC;
方法二、使用扩展事件(高效,适合精确监控)
创建扩展事件会话
运行以下 T-SQL 脚本(替换路径和数据库名):
CREATE EVENT SESSION [Capture_SQL]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text, sqlserver.tsql_stack)
WHERE (
sqlserver.database_id = DB_ID('YourDatabase') -- 替换为数据库名
AND sqlserver.client_app_name NOT LIKE '%Management Studio%' -- 可选:排除 SSMS 操作
)
)
ADD TARGET package0.event_file(
SET filename = N'C:\Traces\Capture_SQL.xel' -- 指定保存路径
)
WITH (STARTUP_STATE = OFF);
启动/停止会话
ALTER EVENT SESSION [Capture_SQL] ON SERVER STATE = START;
结束监控:
ALTER EVENT SESSION [Capture_SQL] ON SERVER STATE = STOP;
查看结果:
SELECT
n.value('(@timestamp)[1]', 'datetime') AS [Time],
n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [SQL_Text]
FROM
(SELECT CAST(event_data AS XML) AS data FROM sys.fn_xe_file_target_read_file(
'C:\Traces\Capture_SQL*.xel', NULL, NULL, NULL
)) AS ed
CROSS APPLY ed.data.nodes('//event') AS q(n)
WHERE
n.value('(@timestamp)[1]', 'datetime') BETWEEN '开始时间' AND '结束时间';