CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Pankaj Rawat
Pankaj Rawat

Reputation: 4573

Cosmos DB SQL API query tracing

I have many collections in Cosmos DB (SQL API) and many services using the same database. 100s of query executing every minute, Sometimes I can see RU exceeding warning. I know all query is not optimized in all the application.

Is there any way to get all queries and execution time (like SQL server Profiler Trace)?

I tried to enable Cosmos DB Diagnostic Settings, but didn't get any way to trace queries

Upvotes: 1

Views: 1504

Answers (1)

Nick Chapsas
Nick Chapsas

Reputation: 7200

The way you would have to go about it is enabling PopulateQueryMetrics in the SDK.

Ones enabled your queries will have the x-ms-documentdb-query-metrics header populated. The value looks like this:

totalExecutionTimeInMs=33.67;queryCompileTimeInMs=0.06;queryLogicalPlanBuildTimeInMs=0.02;queryPhysicalPlanBuildTimeInMs=0.10;queryOptimizationTimeInMs=0.00;VMExecutionTimeInMs=32.56;indexLookupTimeInMs=0.36;documentLoadTimeInMs=9.58;systemFunctionExecuteTimeInMs=0.00;userFunctionExecuteTimeInMs=0.00;retrievedDocumentCount=2000;retrievedDocumentSize=1125600;outputDocumentCount=2000;writeOutputTimeInMs=18.10;indexUtilizationRatio=1.00

As you can see this needs be to split and parsed but it has a lot of useful info which you can log and query to see a lot of stuff about your queries and their performance.

You can read more about query metrics here: https://learn.microsoft.com/en-us/azure/cosmos-db/sql-api-query-metrics

Upvotes: 1

Related Questions