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