PGSQLQueryStoreRuntime 表的查询

有关在 Azure 门户中使用这些查询的信息，请参阅 Log Analytics 教程。 有关 REST API，请参阅查询。

超出阈值的查询执行

确定超过定义的阈值的查询。

// Please change the threshold in milliseconds upon your requirements
// By default, entries are aggregated every 15 mins (see pg_qs.interval_length_minutes)
// PlanId is captured only if pg_qs.store_query_plans=ON
let MinQueryExecutionThresholdInMilliseconds=1000;
PGSQLQueryStoreRuntime
| where IsSystemQuery==false //excludes azure managed user
| where MeanExecDurationMs > MinQueryExecutionThresholdInMilliseconds
| project
    StartTime,
    EndTime,
    QueryId,
    PlanId,
    QueryType,
    UserId,
    DatabaseId,
    MeanExecDurationMs,
    MaxExecDurationMs,
    Calls,
    Rows
| order by MeanExecDurationMs desc, QueryId asc
| limit 100

查询速度最慢

按平均执行时间确定前 10 个最慢的查询。

// PlanId is captured only if pg_qs.store_query_plans=ON
PGSQLQueryStoreRuntime
| where IsSystemQuery==false //excludes azure managed user
| summarize AvgMeanExecDuration=avg(MeanExecDurationMs),MaxExecDuration=max(MaxExecDurationMs) by QueryId, PlanId, QueryType, UserId, DatabaseId
| top 10 by AvgMeanExecDuration desc

查询计数

确定所有查询的执行计数趋势。

// By default, entries are aggregated in QueryStore every 15 mins (see pg_qs.interval_length_minutes)
// AgregationWindow was set to 15min, but you may modify it based on your needs, however should not be less than pg_qs.interval_length_minutes. 
let AgregationWindow=15m;
PGSQLQueryStoreRuntime
| where IsSystemQuery==false //excludes azure managed user
| summarize sum(Calls) by bin(EndTime,AgregationWindow)
| render columnchart