Cosmos DB Queries

cosmos-queries.kql

// Consumed RU/s in last 24 hours 
// Identify consumed RU/s on Cosmos databases and collections. 
// To create an alert for this query, click '+ New alert rule'
//You can compare the RU/s consumption with your provisioned RU/s to determine if you should scale up or down RU/s based on your workload.
AzureDiagnostics
| where Category == "DataPlaneRequests"
//| where collectionName_s == "CollectionToAnalyze" //Replace to target the query to a collection
| summarize ConsumedRUsPerMinute = sum(todouble(requestCharge_s)) by collectionName_s, _ResourceId, bin(TimeGenerated, 1m)
| project TimeGenerated , ConsumedRUsPerMinute , collectionName_s, _ResourceId
| render timechart


// RU charges by  
CDBPartitionKeyRUConsumption 
| where DatabaseName == "dsp-db"
| summarize sum(RequestCharge) by PartitionKey
| order by sum_RequestCharge desc 




// RU charges by  
CDBPartitionKeyRUConsumption 
| where DatabaseName == "dsp-db"
| summarize sum(RequestCharge) by PartitionKey
| order by sum_RequestCharge desc 

// RU per query
CDBQueryRuntimeStatistics
| join kind=inner (
    CDBDataPlaneRequests
    | project RequestCharge, ActivityId
) on $left.ActivityId == $right.ActivityId
| distinct RequestCharge, QueryText
| order by RequestCharge desc 
| limit 10

CDBPartitionKeyRUConsumption
| join kind=innerunique CDBPartitionKeyStatistics on PartitionKey
| project PartitionKey, AccountName, CollectionName, DatabaseName, RegionName, OperationName, TenantId, _SubscriptionId, RequestCharge, TimeGenerated,SizeKb, SourceSystem, _BilledSize
| join kind=innerunique CDBDataPlaneRequests on CollectionName
| project PartitionKey, AccountName, CollectionName, DatabaseName, RegionName, OperationName, TenantId, _SubscriptionId, RequestCharge, TimeGenerated,SizeKb, SourceSystem, _BilledSize, ActivityId
| join kind=innerunique CDBQueryRuntimeStatistics on CollectionName
| project PartitionKey, AccountName, CollectionName, DatabaseName, RegionName, OperationName, TenantId, _SubscriptionId, RequestCharge, TimeGenerated,SizeKb, SourceSystem, _BilledSize, ActivityId, Type
| summarize sum(RequestCharge) by PartitionKey, CollectionName
| order by sum_RequestCharge desc

CDBPartitionKeyRUConsumption
| where DatabaseName == "dsp-db"
| where isnotempty(PartitionKey)
// Sum total request units consumed by logical partition key for each second
| project OperationName, CollectionName, PartitionKey, RequestCharge
| summarize sum(RequestCharge) by PartitionKey, CollectionName,OperationName
| order by sum_RequestCharge desc

//Determine what requests are returning 429 responses
CDBDataPlaneRequests
| summarize throttledOperations = dcountif(ActivityId, StatusCode == 429), totalOperations = dcount(ActivityId), totalConsumedRUPerMinute = sum(RequestCharge) by DatabaseName, CollectionName, OperationName, bin(TimeGenerated, 1min)
| extend averageRUPerOperation = 1.0 * totalConsumedRUPerMinute / totalOperations
| extend Most429s = 1.0 * throttledOperations / totalOperations
| order by Most429s desc