如何通过T-SQL获取Azure SQL数据库近24小时查询的用户信息

首先明确：Query Store（查询存储）本身不记录执行查询的用户身份信息，所以你之前的Query Store语句无法获取用户信息；而sys.dm_exec_requests只能返回当前正在运行的请求，无法获取历史记录。以下是两种可行的T-SQL方案：

方案一：使用扩展事件捕获历史查询（推荐，可持久化历史数据） #

扩展事件是轻量级的跟踪机制，可以持久化存储查询执行的详细信息，包括执行用户。你需要先创建一个事件会话来捕获所需数据，之后查询该会话的存储结果：

1. 创建扩展事件会话 #

CREATE EVENT SESSION [CaptureQueryAndUser] ON DATABASE 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.login_name, sqlserver.session_id, sqlserver.sql_text)
    WHERE sqlserver.database_id = DB_ID()
)
ADD TARGET package0.event_file(SET filename=N'CaptureQueryAndUser.xel', max_file_size=(10), max_rollover_files=(5))
WITH (STARTUP_STATE=ON);
GO
-- 启动会话
ALTER EVENT SESSION [CaptureQueryAndUser] ON DATABASE STATE = START;
GO

2. 查询近24小时的捕获数据 #

SELECT
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS execution_time,
    event_data.value('(event/action[@name="login_name"]/value)[1]', 'nvarchar(100)') AS login_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS query_text,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint')/1000 AS duration_ms,
    event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint')/1000 AS cpu_time_ms,
    event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('CaptureQueryAndUser*.xel', NULL, NULL, NULL)
) AS x
WHERE
    event_data.value('(event/@timestamp)[1]', 'datetime2') >= DATEADD(DAY, -1, GETDATE())
ORDER BY execution_time DESC;
GO

注意：需要确保数据库有足够的权限创建事件会话，并且存储路径有写入权限。该会话会持续捕获数据，适合长期跟踪。

方案二：利用DMV获取缓存中的历史查询（仅临时可用，数据可能丢失） #

如果无法创建扩展事件，可以使用sys.dm_exec_query_stats结合相关DMV获取缓存中仍保留的查询信息，但缓存中的查询会因为内存压力、计划更新等原因被清理，无法保证覆盖所有近24小时的查询：

SELECT
    qs.last_execution_time,
    s.login_name,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(qt.text) 
            ELSE qs.statement_end_offset 
         END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.total_elapsed_time/1000 AS total_elapsed_time_ms,
    qs.cpu_time/1000 AS cpu_time_ms,
    qs.logical_reads
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
LEFT JOIN sys.dm_exec_sessions s ON qs.session_id = s.session_id
WHERE
    qs.last_execution_time >= DATEADD(DAY, -1, GETDATE())
ORDER BY qs.last_execution_time DESC;
GO

说明：该方法依赖查询计划缓存，对于执行频率低、内存压力大的环境，数据可能不完整。

内容的提问来源于stack exchange，提问作者BIDeveloper