針對 Azure Cosmos DB for NoSQL 的進階診斷查詢問題進行疑難排解

適用於： NoSQL

在本文中，我們將探討如何使用傳送至 Azure 診斷 (舊版) 和資源專用 (預覽) 資料表的診斷記錄，撰寫更進階的查詢，協助針對 Azure Cosmos DB 帳戶的問題進行疑難排解。

Azure 診斷資料表的所有資料皆會寫入單一資料表中。 使用者指定要查詢的類別。 若要檢視要求的全文檢索查詢，請參閱使用 Azure 中的診斷設定來監視 Azure Cosmos DB 資料，了解如何啟用這項功能。

資源專屬資料表的資料會寫入該資源各類別的個別資料表。 建議採用此模式，因為：

• 使用資料時更方便。
• 可更深入探索結構描述。
• 同時改善擷取延遲及查詢時間兩者的效能。

常用查詢

資源專屬及 Azure 診斷資料表會顯示常用查詢。

依特定時間範圍內的要求單位 (RU) 耗用量排序的前 N(10) 個查詢

資源專屬

Kusto

let topRequestsByRUcharge = CDBDataPlaneRequests 
| where TimeGenerated > ago(24h)
| project  RequestCharge , TimeGenerated, ActivityId;
CDBQueryRuntimeStatistics
| project QueryText, ActivityId, DatabaseName , CollectionName
| join kind=inner topRequestsByRUcharge on ActivityId
| project DatabaseName , CollectionName , QueryText , RequestCharge, TimeGenerated
| order by RequestCharge desc
| take 10

Azure 診斷

Kusto

let topRequestsByRUcharge = AzureDiagnostics
| where Category == "DataPlaneRequests" and TimeGenerated > ago(24h)
| project  requestCharge_s , TimeGenerated, activityId_g;
AzureDiagnostics
| where Category == "QueryRuntimeStatistics"
| project querytext_s, activityId_g, databasename_s , collectionname_s
| join kind=inner topRequestsByRUcharge on activityId_g
| project databasename_s , collectionname_s , querytext_s , requestCharge_s, TimeGenerated
| order by requestCharge_s desc
| take 10

在特定時間範圍內節流的要求 (statusCode = 429)

資源專屬

Kusto

let throttledRequests = CDBDataPlaneRequests
| where StatusCode == "429"
| project  OperationName , TimeGenerated, ActivityId;
CDBQueryRuntimeStatistics
| project QueryText, ActivityId, DatabaseName , CollectionName
| join kind=inner throttledRequests on ActivityId
| project DatabaseName , CollectionName , QueryText , OperationName, TimeGenerated

Azure 診斷

Kusto

let throttledRequests = AzureDiagnostics
| where Category == "DataPlaneRequests" and statusCode_s == "429"
| project  OperationName , TimeGenerated, activityId_g;
AzureDiagnostics
| where Category == "QueryRuntimeStatistics"
| project querytext_s, activityId_g, databasename_s , collectionname_s
| join kind=inner throttledRequests on activityId_g
| project databasename_s , collectionname_s , querytext_s , OperationName, TimeGenerated

回應長度 (伺服器回應的承載大小) 最大的查詢

資源專屬

Kusto

let operationsbyUserAgent = CDBDataPlaneRequests
| project OperationName, DurationMs, RequestCharge, ResponseLength, ActivityId;
CDBQueryRuntimeStatistics
//specify collection and database
//| where DatabaseName == "DBNAME" and CollectionName == "COLLECTIONNAME"
| join kind=inner operationsbyUserAgent on ActivityId
| summarize max(ResponseLength) by QueryText
| order by max_ResponseLength desc

Azure 診斷

Kusto

let operationsbyUserAgent = AzureDiagnostics
| where Category=="DataPlaneRequests"
| project OperationName, duration_s, requestCharge_s, responseLength_s, activityId_g;
AzureDiagnostics
| where Category == "QueryRuntimeStatistics"
//specify collection and database
//| where databasename_s == "DBNAME" and collectioname_s == "COLLECTIONNAME"
| join kind=inner operationsbyUserAgent on activityId_g
| summarize max(responseLength_s1) by querytext_s
| order by max_responseLength_s1 desc

實體分割區 (複本集的所有複本) 的 RU 耗用量

資源專屬

Kusto

CDBPartitionKeyRUConsumption
| where TimeGenerated >= now(-1d)
//specify collection and database
//| where DatabaseName == "DBNAME" and CollectionName == "COLLECTIONNAME"
// filter by operation type
//| where operationType_s == 'Create'
| summarize sum(todouble(RequestCharge)) by toint(PartitionKeyRangeId)
| render columnchart

Azure 診斷

Kusto

AzureDiagnostics
| where TimeGenerated >= now(-1d)
| where Category == 'PartitionKeyRUConsumption'
//specify collection and database
//| where databasename_s == "DBNAME" and collectioname_s == "COLLECTIONNAME"
// filter by operation type
//| where operationType_s == 'Create'
| summarize sum(todouble(requestCharge_s)) by toint(partitionKeyRangeId_s)
| render columnchart  

邏輯分割區 (複本集的所有複本) 的 RU 耗用量

資源專屬

Kusto

CDBPartitionKeyRUConsumption
| where TimeGenerated >= now(-1d)
//specify collection and database
//| where DatabaseName == "DBNAME" and CollectionName == "COLLECTIONNAME"
// filter by operation type
//| where operationType_s == 'Create'
| summarize sum(todouble(RequestCharge)) by PartitionKey, PartitionKeyRangeId
| render columnchart  

Azure 診斷

Kusto

AzureDiagnostics
| where TimeGenerated >= now(-1d)
| where Category == 'PartitionKeyRUConsumption'
//specify collection and database
//| where databasename_s == "DBNAME" and collectioname_s == "COLLECTIONNAME"
// filter by operation type
//| where operationType_s == 'Create'
| summarize sum(todouble(requestCharge_s)) by partitionKey_s, partitionKeyRangeId_s
| render columnchart  

下一步

• 如需有關如何為 Azure Cosmos DB 建立診斷設定的詳細資訊，請參閱建立診斷設定。
• 如需有關如何使用 Azure 入口網站、Azure CLI 或 PowerShell 建立診斷設定的詳細資訊，請參閱建立診斷設定以收集 Azure 中的平台記錄和計量。