共用方式為

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
  • Last updated on