Azure Synapse DW数据库SQL请求量分析与导出方法问询

一、使用Synapse SQL池系统视图查询 #

Synapse数据仓库提供了专用的动态管理视图(DMV)，查询结果比通用SQL Server DMV更贴合Synapse监控数据：

1. 快速统计特定视图的请求量 #

替换[YourSchema].[YourView]为目标视图的架构和名称，执行以下SQL：

SELECT 
    COUNT(DISTINCT r.request_id) AS total_requests,
    MIN(r.start_time) AS earliest_request_time,
    MAX(r.end_time) AS latest_request_time
FROM 
    sys.dm_pdw_exec_requests r
JOIN 
    sys.dm_pdw_exec_sessions s ON r.session_id = s.session_id
WHERE 
    r.command LIKE '%FROM [YourSchema].[YourView]%'
    AND s.is_system = 0 -- 排除系统自动发起的请求
    AND r.status IN ('Completed', 'Failed', 'Cancelled') -- 过滤已结束的请求

2. 精准匹配视图访问请求 #

避免LIKE语句的误匹配，可通过底层执行步骤关联视图元数据：

WITH view_access_log AS (
    SELECT 
        r.request_id,
        r.start_time,
        r.end_time,
        r.command
    FROM 
        sys.dm_pdw_exec_requests r
    WHERE 
        EXISTS (
            SELECT 1 
            FROM sys.dm_pdw_request_steps rs
            JOIN sys.pdw_mappings m ON rs.object_id = m.object_id
            JOIN sys.views v ON m.physical_name = v.name
            WHERE 
                rs.request_id = r.request_id
                AND v.schema_id = SCHEMA_ID('YourSchema')
                AND v.name = 'YourView'
        )
)
SELECT 
    COUNT(DISTINCT request_id) AS total_view_requests,
    MIN(start_time) AS first_request_time,
    MAX(end_time) AS last_request_time
FROM view_access_log;

二、通过Azure Monitor日志查询并导出 #

将Synapse诊断日志接入Log Analytics工作区，即可灵活查询并导出数据：

1. 前置配置 #

在Synapse Studio的诊断设置中，开启SqlRequests日志类别，并将其发送到指定的Log Analytics工作区。

2. Kusto查询统计 #

在Log Analytics中执行以下查询（替换占位符为实际信息），结果可直接导出为CSV/Excel：

SynapseSqlRequests
| where WorkspaceName == "你的Synapse工作区名称"
| where DatabaseName == "你的DW数据库名称"
| where CommandText contains "你的视图名称"
| summarize TotalRequests = count() by bin(TimeGenerated, 1h) -- 按小时维度统计
| order by TimeGenerated asc

三、使用PowerShell批量导出请求历史 #

绕过Export-AzSynapseSqlScript的局限性，直接调用API获取请求数据：

$workspaceName = "你的Synapse工作区名称"
$resourceGroupName = "资源组名称"
$databaseName = "DW数据库名称"

# 获取过去7天的所有SQL请求
$allRequests = Get-AzSynapseSqlPoolRequest -WorkspaceName $workspaceName `
    -ResourceGroupName $resourceGroupName `
    -SqlPoolName $databaseName `
    -StartTime (Get-Date).AddDays(-7)

# 筛选访问目标视图的请求并导出
$viewRequests = $allRequests | Where-Object { $_.CommandText -like "*你的视图名称*" }
$viewRequests | Select-Object RequestId, StartTime, EndTime, CommandText `
    | Export-Csv -Path "C:\SynapseViewRequests.csv" -NoTypeInformation

内容的提问来源于stack exchange，提问作者João Felipe B. C. Carvalho