如何查看Azure Synapse专用池的表查询次数及使用统计

1. 利用查询存储（Query Store）完整追踪 #

Synapse专用池支持查询存储，它会完整记录查询文本，不会出现字符截断问题，且可自定义数据保留周期。

• 先确认查询存储已启用（默认可能开启），若未开启执行：

  ALTER DATABASE [你的数据库名] SET QUERY_STORE = ON;
  

• 调整保留周期至120天，覆盖你的需求：

  ALTER DATABASE [你的数据库名] SET QUERY_STORE (RETENTION_DAYS = 120);
  

• 执行以下查询提取表的访问统计：

  SELECT 
      OBJECT_NAME(qo.object_id) AS 表名,
      SUM(rs.count_executions) AS 查询总次数,
      MAX(rs.last_execution_time) AS 最后访问时间,
      qt.query_sql_text AS 关联查询语句
  FROM sys.query_store_query_text qt
  JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan p ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
  JOIN sys.query_store_object qo ON p.plan_id = qo.plan_id
  WHERE qo.object_type = 'OBJECT'
    AND rs.last_execution_time >= DATEADD(day, -120, GETDATE())
  GROUP BY OBJECT_NAME(qo.object_id), qt.query_sql_text
  ORDER BY 查询总次数 DESC;
  

2. 扩展事件（Extended Events）捕获完整语句 #

通过扩展事件会话捕获sql_statement_completed事件，可完整记录SQL语句，不受字符长度限制。

• 创建事件会话：

  CREATE EVENT SESSION [表访问追踪] ON SERVER 
  ADD EVENT sqlserver.sql_statement_completed(
      ACTION(sqlserver.sql_text, sqlserver.username)
      WHERE sqlserver.database_id = DB_ID('[你的数据库名]'))
  ADD TARGET package0.event_file(SET filename=N'表访问追踪.xel', max_file_size=(100), max_rollover_files=(20))
  WITH (STARTUP_STATE=ON);
  

• 启动会话后，解析事件文件获取统计：

  SELECT 
      OBJECT_NAME(CAST(event_data.value('(data[@name="object_id"]/value)[1]', 'int') AS int)) AS 表名,
      COUNT(*) AS 查询次数,
      MAX(event_data.value('(data[@name="end_time"]/value)[1]', 'datetime2')) AS 最后访问时间
  FROM (
      SELECT CAST(event_data AS XML) AS event_data
      FROM sys.fn_xe_file_target_read_file('表访问追踪*.xel', NULL, NULL, NULL)
  ) AS x
  WHERE event_data.value('(data[@name="object_id"]/value)[1]', 'int') IS NOT NULL
    AND event_data.value('(data[@name="end_time"]/value)[1]', 'datetime2') >= DATEADD(day, -120, GETDATE())
  GROUP BY OBJECT_NAME(CAST(event_data.value('(data[@name="object_id"]/value)[1]', 'int') AS int))
  ORDER BY 查询次数 DESC;
  

  注意：此方法需提前配置，无法获取会话开启前的历史数据。

3. 借助sys.dm_pdw_dms_workers补充统计 #

该视图会记录涉及数据移动（跨分布查询）的表访问情况，可作为补充手段：

SELECT 
    object_name AS 表名,
    COUNT(DISTINCT request_id) AS 查询次数,
    MAX(end_time) AS 最后访问时间
FROM sys.dm_pdw_dms_workers
WHERE end_time >= DATEADD(day, -120, GETDATE())
  AND object_name IS NOT NULL
GROUP BY object_name
ORDER BY 查询次数 DESC;

局限性：仅统计涉及DMS操作的查询，纯本地分布查询不会被记录。

4. 自定义审计表（适合长期追踪） #

通过DML触发器在审计表中记录表访问信息，完全自定义但需逐个配置：

• 创建审计表：

  CREATE TABLE 表访问审计日志 (
      表名 NVARCHAR(128),
      访问时间 DATETIME DEFAULT GETDATE(),
      访问用户 NVARCHAR(128) DEFAULT SUSER_SNAME(),
      执行语句 NVARCHAR(MAX)
  );
  

• 为目标表创建触发器示例：

  CREATE TRIGGER [追踪_用户表访问] ON [你的目标表]
  AFTER SELECT, INSERT, UPDATE, DELETE
  AS
  BEGIN
      INSERT INTO 表访问审计日志 (表名, 执行语句)
      VALUES ('你的目标表', (SELECT text FROM sys.dm_exec_sql_text(@@SPID)));
  END;
  

  优势：精准记录所有访问；缺点：仅能记录触发器创建后的访问，对性能有轻微影响。

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