方法一、查询动态管理视图（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 '结束时间';

打赏赞(1)