Skip to content

Files

Latest commit

9956ffc · Aug 26, 2021

History

History
185 lines (156 loc) · 7.39 KB

cosmos-db-advanced-queries.md

File metadata and controls

185 lines (156 loc) · 7.39 KB
title titleSuffix description author services ms.service ms.topic ms.date ms.author
Troubleshoot issues with advanced diagnostics queries (SQL API)
Azure Cosmos DB
Learn how to query diagnostics logs to troubleshoot data stored in Azure Cosmos DB - SQL API.
StefArroyo
cosmos-db
cosmos-db
how-to
06/12/2021
esarroyo

Troubleshoot issues with advanced diagnostics queries for the SQL (Core) API

[!INCLUDEappliesto-sql-api]

[!div class="op_single_selector"]

In this article, we'll cover how to write more advanced queries to help troubleshoot issues with your Azure Cosmos DB account by using diagnostics logs sent to Azure Diagnostics (legacy) and resource-specific (preview) tables.

For Azure Diagnostics tables, all data is written into one single table. Users specify which category they want to query. If you want to view the full-text query of your request, see Monitor Azure Cosmos DB data by using diagnostic settings in Azure to learn how to enable this feature.

For resource-specific tables, data is written into individual tables for each category of the resource. We recommend this mode because it:

  • Makes it much easier to work with the data.
  • Provides better discoverability of the schemas.
  • Improves performance across both ingestion latency and query times.

Common queries

Common queries are shown in the resource-specific and Azure Diagnostics tables.

Top N(10) queries ordered by Request Unit (RU) consumption in a specific time frame

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

Requests throttled (statusCode = 429) in a specific time window

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

Queries with the largest response lengths (payload size of the server response)

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
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 consumption by physical partition (across all replicas in the replica set)

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
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 consumption by logical partition (across all replicas in the replica set)

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

Next steps