Domain 2 of 3 · Chapter 3 of 4

Optimize and remediate database performance

Unlock the complete study guide + 25 practice exams totaling 1,500 questions.

Bundled into the existing Building AI Applications with Azure Cosmos DB premium course — no separate purchase.

14-day refund guarantee on one-time purchases and your first subscription charge.

Included in this chapter:

  • Measure first: the query troubleshooting loop
  • Reading query metrics
  • How a filter uses the index
  • Indexing policy paths and strategy
  • Composite indexes
  • Spatial and tuple indexes
  • Changing an indexing policy safely
  • Index metrics: utilized and potential indexes
  • Query Advisor recommendations
  • Query rewrites that cut the RU charge
  • Cutting latency when the RU charge is acceptable
  • The integrated cache
  • End-to-end latency versus server-side latency
  • Hedging reads across regions
  • Client-side bottlenecks in SDK diagnostics
  • Azure Monitor alerts for Azure Cosmos DB
  • Collecting diagnostic logs in Log Analytics
  • KQL investigations over diagnostic logs
  • Exam patterns: matching the symptom to the fix

The four evidence sources for Azure Cosmos DB performance work

AspectPer-query instrumentationSDK diagnosticsPlatform metricsDiagnostic logs
How you get itQuery metrics read from the response's Diagnostics (the service's own execution report); index metrics and Query Advisor through query request optionsThe Diagnostics property on every .NET SDK response and CosmosExceptionAzure Monitor metrics for the accountA diagnostic setting that routes chosen log categories to a Log Analytics workspace
Collected by default?Query metrics yes; index metrics and Query Advisor default to falseYes, but your code decides what to logYes, automaticallyNo, not until you create a diagnostic setting
ScopeOne query, one round trip at a timeOne operation, seen from the clientAccount, database, container and region over timeIndividual requests, queries and partition keys
Typical questionWhy does this query cost so many RUs?Where did this slow call spend its time?Is the workload crossing a threshold?Which queries, operations or keys consume the RUs?

Decision tree

Explaining onequery's RU cost?YesPer-queryinstrumentationNoSplitting one call'stime by layer?YesSDK diagnosticsNoNumeric signalover time or ametric alert?YesPlatform metricsNoDiagnostic logs(KQL in Log Analytics)

Cheat sheet

  • Partition-targeted queries avoid paying for every physical partition's index work
  • Indexed normalized values avoid applying UPPER or LOWER to every candidate document
  • STARTSWITH is the better match when a search requires only a string prefix
  • Filtering array subqueries before JOIN reduces intermediate cross-products
  • MaxConcurrency reduces fan-out latency without changing which partitions must execute
  • MaxBufferedItemCount controls how much query prefetch can hide page-consumption latency
  • Query Advisor returns query-plan advice on the first .NET query round trip
  • High-cardinality GROUP BY can remain expensive even when its filter uses indexes
  • Case-insensitive STARTSWITH and STRINGEQUALS use expanded index scans
  • More provisioned throughput can reduce query latency dominated by throttling waits
  • Time filters should avoid repeatedly evaluating nonstatic clock functions
  • Ordering by the searched string can make scan-based string filters cheaper
  • An indexed aggregate can still load documents to evaluate its filters
  • EnableScanInQuery permits full scans of filter paths excluded from indexing
  • Smaller continuation-token limits can increase query duration and RU consumption
  • Excluding unqueried paths reduces index maintenance work for writes
  • The indexing root must be included or excluded to define the policy's default behavior
  • Indexing paths distinguish scalar values, array elements, and whole subtrees
  • More precise indexing paths override broader include or exclude rules
  • Index path names are case-sensitive and must match stored JSON names
  • Partition routing does not automatically supply an index for the partition-key property
  • Spatial query acceleration requires an explicit spatial index for the required geometry types
  • Tuple indexes optimize filters on multiple fields in the same array element
  • Range indexes support scalar filtering and single-property ordering
  • A multi-property ORDER BY needs a composite index with the same path sequence
  • A composite index supports its configured sort directions and their complete reversal
  • Composite filter indexes put equality properties before the range property
  • Each composite index optimizes at most one range filter
  • Adding equality-filter fields to ORDER BY can enable a cheaper composite-index plan
  • Filtered SUM and AVG queries can benefit from a composite ending in the aggregate property
  • Composite index paths have an implicit scalar ending and cannot use a subtree wildcard
  • A filter composite is not used if it includes a property absent from the query's filters
  • A composite index can combine a root property with a joined array value
  • Index transformations run online but consume spare provisioned RUs
  • Add and finish a replacement index before removing the old index
  • Group index removals into one policy update to preserve complete results during transformation
  • Temporarily disabling indexing can accelerate a bulk load that does not need indexed queries
  • An exclude-root consistent policy preserves TTL without indexing application paths
  • GetQueryMetrics exposes typed server-side query metrics from a response's diagnostics
  • Query measurements must include every continuation rather than just the first page
  • A large gap between retrieved and output document counts signals wasted document loading
  • Query timing components identify whether loading, index lookup, or execution dominates
  • Per-partition query metrics isolate an expensive physical partition hidden by aggregates
  • A function in SELECT does not disable the filter's index access merely because it would scan in WHERE
  • Projecting only needed fields can reduce query output work without reducing document loading
  • Using an index does not establish that the query's access method is cheap
  • PopulateIndexMetrics requests the query's index usage and recommendations
  • Index metrics are disabled by default and should be enabled selectively
  • Utilized indexes are evidence about the measured query rather than the whole workload
  • Potential index recommendations must be tested against the actual data
  • Index impact scores reflect query shape rather than measured data selectivity
  • Using the integrated cache requires a dedicated gateway endpoint and Gateway mode
  • Only Session and Eventual read requests can use the integrated cache
  • Integrated cache hits cost zero RUs and favor repeat-heavy read workloads
  • MaxIntegratedCacheStaleness controls acceptable age at read time rather than a global cache TTL
  • The query cache stores result sets rather than tracking changes to individual matching items
  • Dedicated gateway nodes have independent caches sharing item and query capacity within each node
  • BypassIntegratedCache avoids cache use and population without bypassing the dedicated gateway
  • A cached Session read must satisfy the requesting client's session token
  • Cache eviction and gateway CPU distinguish memory-size problems from request-volume problems
  • ReadMany populates the query cache while bulk and transactional batch requests do not populate the item cache
  • DedicatedGatewayRequests verifies gateway use independently of cache hits
  • Staleness-driven eviction can indicate that the integrated cache is oversized
  • Server-side latency does not measure the application's complete operation latency
  • Use the Direct or Gateway server-side latency metric that matches the connection path
  • SDK diagnostics should be captured for slow successes as well as exceptions
  • The diagnostic string is for inspection rather than a stable parsing interface
  • High transit time with low BELatencyInMs points away from backend execution
  • Thread-starvation markers and delayed system-history samples identify a client scheduling bottleneck
  • Maximum client CPU is more useful than average CPU for latency troubleshooting
  • Read hedging reduces tail latency by accepting the fastest regional response
  • Database and container existence checks belong outside the item-operation hot path
  • SNAT exhaustion can cause transport timeouts from private-IP application hosts
  • A long Pipelined stage identifies delay writing the request to its TCP socket
  • Many callsPendingReceive can justify more TCP channels to reduce head-of-line blocking
  • Metric, Activity log, and log-query alerts address different monitoring requirements
  • Logical-partition storage alerts use PartitionKeyStatistics diagnostic logs
  • Alert dimensions keep a local performance problem from being diluted by account-wide aggregation
  • Static thresholds enforce a specified boundary while dynamic thresholds learn metric behavior
  • Action groups define notifications or automation separately from the alert condition
  • ReplicationLatency measures regional replication lag rather than application request latency
  • ServiceAvailability reports account request availability as a percentage
  • A throttling-count alert uses TotalRequests filtered to status code 429
  • DataUsageV2 and IndexUsageV2 measure stored data and index bytes for storage alerts
  • Choose Resource Health for an account and Service Health for broader incidents
  • Diagnostic settings route selected Cosmos DB log categories to Log Analytics
  • Resource-specific mode separates diagnostic categories into CDB tables
  • Join data-plane requests to query runtime statistics to associate RU charge with query text
  • Diagnostics full-text query logging is required to see deobfuscated query content
  • KQL percentiles expose tail request durations that a mean can hide
  • Partition-key RU and storage investigations require different diagnostic tables
  • Legacy diagnostic fields often need numeric conversion before filtering or aggregation
  • Partition-key storage statistics report sampled outliers rather than every logical partition
  • Aggregated request categories consolidate telemetry into five- or fifteen-minute records
  • Blocked-request logs identify the source address to investigate before changing an allowlist

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. https://learn.microsoft.com/en-us/azure/cosmos-db/troubleshoot-query-performance
  2. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-420
  3. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/troubleshoot-dotnet-sdk-slow-request
  4. https://learn.microsoft.com/en-us/azure/cosmos-db/monitor-server-side-latency
  5. https://learn.microsoft.com/en-us/azure/cosmos-db/monitor-resource-logs
  6. https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-types
  7. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query-metrics-performance
  8. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query-metrics
  9. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/pagination
  10. https://learn.microsoft.com/en-us/azure/cosmos-db/index-overview
  11. https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy
  12. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/index-metrics
  13. https://learn.microsoft.com/en-us/azure/advisor/advisor-overview
  14. https://learn.microsoft.com/en-us/cosmos-db/query/advisor
  15. https://learn.microsoft.com/en-us/cosmos-db/query/advisor/qa1003
  16. https://learn.microsoft.com/en-us/cosmos-db/query/getcurrenttimestampstatic
  17. https://learn.microsoft.com/en-us/azure/cosmos-db/integrated-cache
  18. https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-configure-integrated-cache
  19. https://learn.microsoft.com/en-us/azure/cosmos-db/dedicated-gateway
  20. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-dotnet-read-item
  21. https://learn.microsoft.com/en-us/azure/cosmos-db/transactional-batch
  22. https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-migrate-from-bulk-executor-library
  23. https://learn.microsoft.com/en-us/azure/cosmos-db/monitor-reference
  24. https://learn.microsoft.com/en-us/azure/cosmos-db/performance-tips-dotnet-sdk-v3
  25. https://learn.microsoft.com/en-us/azure/cosmos-db/per-partition-automatic-failover
  26. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/troubleshoot-dotnet-sdk
  27. https://learn.microsoft.com/en-us/azure/cosmos-db/create-alerts
  28. https://learn.microsoft.com/en-us/azure/service-health/overview
  29. https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-alert-on-logical-partition-key-storage-size
  30. https://learn.microsoft.com/en-us/azure/cosmos-db/monitor-logs-basic-queries
  31. https://learn.microsoft.com/en-us/azure/cosmos-db/monitor-aggregated-logs
  32. https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/cdbdataplanerequests5m
  33. https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-configure-firewall