Cosmos DB Query execution metrics

   

   

      CosmosDB is a managed database system, You really don't have that much control when it comes to execution plans. In SQL Server, we have Query Hints. If you know what you are doing, Hints might be helpful in some situations. (adding NOLOCK to every query is not helping to your queries/databases)

     When I speak about CosmosDB, I always get questions like "How can I retrieve information about the execution plans?" or "Isn't there a tool like SSMS which can show me what's happening in the background?" Usually, questions like that comes from DBAs. If you have questions like that, I have good and bad news for you. Good news is, Yes you can get retrieve metrics from CosmosDB about execution plans. Bad news is, you need to know some programming to be able to do that because you need to use CosmosDB SDK.

     The only way to access this information is from CosmosDB SDK 2.x. I couldn't retrieve execution metrics by using SDK 3.x for custom queries. Here is the available metrics you can retrieve from CosmosDB queries.

Overall Metrics
totalExecutionTimeInMs	Query execution time
retrievedDocumentCount	Total number of retrieved documents
retrievedDocumentSize	Total size of retrieved documents in bytes
outputDocumentCount	Number of output documents
indexUtilizationRatio	Ratio of number of documents matched by the filter to the number of documents loaded

Query Preparation Metrics
queryCompileTimeInMs	Query compile time
queryLogicalPlanBuildTimeInMs	Time to build logical query plan
queryPhysicalPlanBuildTimeInMs	Time to build physical query plan
queryOptimizationTimeInMs	Time spent in optimizing query

Query Engine Metrics
documentLoadTimeInMs	Time spent in loading documents
indexLookupTimeInMs	Time spent in physical index layer
writeOutputTimeInMs	Query execution time in milliseconds
Runtime Execution Metrics
systemFunctionExecuteTimeInMs	Total time spent executing system (built-in) functions in milliseconds
userFunctionExecuteTimeInMs	Total time spent executing user-defined functions in milliseconds
VMExecutionTimeInMs	Time spent in query runtime

      To be able to retrieve these metrics, you need to use FeedOptions object. Following example uses CosmosDB SDK 2.1, I tried to retrieve execution metrics in SDK 3 but not luck so far. SDK 3 is still in preview mode and it looks like there is no way to pass FeedOptions when you need to run a custom query. I am sure it will be fixed soon.

var query = client.CreateDocumentQuery(
                    UriFactory.CreateDocumentCollectionUri(_dbname, _container),
                    $"SELECT * FROM Orders o WHERE o.OrderId = {@orderid}",
                    new FeedOptions {
                        PopulateQueryMetrics = true,
                        EnableCrossPartitionQuery =true }).AsDocumentQuery();
                FeedResponse<dynamic> result = await query.ExecuteNextAsync();
                IReadOnlyDictionary<string, QueryMetrics> metrics = result.QueryMetrics;

Here is the execution metrics of my query.