PGSQLPgStatActivitySessions 테이블에 대한 쿼리

Azure Portal에서 이러한 쿼리를 사용하는 방법에 대한 자세한 내용은 Log Analytics 자습서를 참조하세요. REST API는 쿼리를 참조 하세요.

가장 긴 세션

상태에 관계없이 가장 긴 세션을 검색합니다.

PGSQLPgStatActivitySessions
| where UserId != 10 //exclude azure managed user
| where BackendType =='client backend'
| where isnotempty(DatabaseId) and DatabaseName !in('azure_sys','azure_maintenance')
| extend ConnectionDurationSec = datetime_diff('second',CollectionTime,BackendStartTime)
| summarize maxConnectionDurationSeconds=max(ConnectionDurationSec),arg_max(LastSampledTime=TimeGenerated,lastState=State) by ProcessId,BackendStartTime,DatabaseName,UserId,ApplicationName,ClientIpAddress
| order by maxConnectionDurationSeconds desc
| take 100

가장 긴 트랜잭션

실행 시간이 가장 긴 세션 및 TransactionId를 검색합니다.

PGSQLPgStatActivitySessions
| where UserId != 10 and State !='idle' //exclude azure managed user and idle sessions
| where BackendType =='client backend'
| where isnotempty(DatabaseId) and DatabaseName !in('azure_sys','azure_maintenance')
| extend TransactionDurationMs = datetime_diff('millisecond',CollectionTime,TransactionStartTime)
| summarize MaxTransactionDurationMs=max(TransactionDurationMs),arg_max(LastSampledTime=TimeGenerated,lastState=State) by ProcessId,BackendStartTime,TransactionId,DatabaseName,UserId,ApplicationName,ClientIpAddress
| order by MaxTransactionDurationMs desc
| take 100

긴 쿼리가 있는 세션

쿼리 실행 시간이 높은 세션을 검색합니다.

PGSQLPgStatActivitySessions
| where UserId != 10 and State !='idle' //exclude azure managed user and idle sessions
| where BackendType =='client backend'
| where isnotempty(DatabaseId) and DatabaseName !in('azure_sys','azure_maintenance')
| extend QueryExecDurationMs = datetime_diff('millisecond',CollectionTime,QueryStartTime)
| summarize MaxQueryExecDurationMs=max(QueryExecDurationMs),arg_max(LastSampledTime=TimeGenerated,lastState=State) by ProcessId,BackendStartTime,DatabaseName,UserId,ApplicationName,ClientIpAddress
| order by MaxQueryExecDurationMs desc
| take 100