Query per la tabella PGSQLQueryStoreRuntime

Per informazioni sull'uso di queste query nel portale di Azure, vedere Esercitazione su Log Analytics. Per l'API REST, vedere Query.

Esecuzione di query che supera una soglia

Identificare le query che richiedono più tempo rispetto alla soglia definita.

// 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

Query più lente

Identificare le prime 10 query più lente in base al tempo di esecuzione medio.

// 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

Conteggi delle query

Identificare la tendenza dei conteggi di esecuzione di tutte le query.

// 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