Retrieve and use query metrics

Query metrics in Azure Cosmos DB give important insights into the resources used by a query or operation. They can help you tune your performance and troubleshoot issues by providing detailed information about latency, consumed RU (request units), and other performance-related data.

Understanding Query Metrics

In Azure Cosmos DB, query metrics pertain to the resources, such as CPU, I/O, and memory, utilized by a request made to the database. These metrics can be retrieved and examined for performance tuning and to understand the consumption of request units (RUs).

In Azure Cosmos DB, each read/write operation including queries consumes a specific amount of RUs. This consumption depends on factors such as the complexity of the query, the size of the result set, and the number of properties being retrieved. By examining query metrics, it helps in optimizing the performance and monitor the consumption of RUs effectively.

Retrieving Query Metrics

Query metrics can be accessed in two ways:

• Directly from Azure portal: The Azure portal provides an in-built SQL query editor where you can execute queries on your Cosmos DB data. When you run the query, the editor will return the query’s metrics.
• Through Azure Cosmos DB SDKs: The SDKs offer options to fetch query metrics through programmatic means. For example, in the .NET SDK, setting the `PopulateQueryMetrics` property for `FeedOptions` to true will allow the SDK to fetch query metrics.

Here’s an example using the Azure Cosmos DB .NET SDK V3:

QueryRequestOptions requestOptions = new QueryRequestOptions { PopulateQueryMetrics = true };
FeedIterator query = this.container.GetItemQueryIterator("SELECT * FROM c", requestOptions: requestOptions);
FeedResponse queryResults = await query.ReadNextAsync();
Console.WriteLine($"Request Charge: {queryResults.RequestCharge}");
Console.WriteLine($"Query Metrics: {queryResults.Diagnostics.ToString()}");


In this snippet, `PopulateQueryMetrics` is set to true to fetch query metrics. The query results can then be examined for metrics using the `RequestCharge` and `Diagnostics` properties.

Using Query Metrics

Once you’ve fetched query metrics, you can analyze these to improve your Azure Cosmos DB performance. These metrics particularly help in:

• RU optimization: By understanding how many request units (RUs) your queries are consuming, you can adjust your database operations to ensure efficient usage of provisioned throughput.
• Query performance optimization: Understanding the metrics and taking appropriate measures (e.g., indexing policy adjustments) can help achieve better query performance.

Conclusion

In conclusion, retrieving and analyzing query metrics plays an important role in performance optimization while designing and implementing native applications using Microsoft Azure Cosmos DB. Not only can it help to tune your queries for optimal resource usage, but it can also potentially decrease operation costs by efficient utilization of provisioned throughput. Consider implementing this strategy to optimize your Cosmos DB applications for better performance and efficiency.

Practice Test

True or False: The TotalRequestUnitsConsumed property can be used to retrieve the total number of Request Units consumed by a query.

• True
• False

Answer: True

Explanation: The TotalRequestUnitsConsumed property is available on the metrics returned by a query to allow you to determine how many Request Units were consumed. This can be useful for capacity planning and performance optimization.

Which of the following can be used to retrieve latency metrics from Azure Cosmos DB?

• A. Azure Monitor
• B. Azure Advisor
• C. PowerShell
• D. Azure DevOps

Answer: A. Azure Monitor

Explanation: Azure Monitor provides built-in capabilities for analyzing performance and usage metrics from Azure Cosmos DB, including latency metrics.

The FeedResponse class in the .NET SDK provides which of the following information?

• A. Diagnostic strings
• B. Request charge
• C. Activity Id
• D. All of the above

Answer: D. All of the above

Explanation: The FeedResponse class provides various metrics like Diagnostic strings, Request charge, and Activity Id which can help you to better understand the operation’s cost and performance.

True or False: Query metrics provide execution details for a query but do not include metrics for total Request Units.

• True
• False

Answer: False

Explanation: Query metrics not only provide detailed breakdowns of execution details but also include total Request Units consumed by the query.

Which of the following properties in query metrics provides the total elapsed time of the query?

• A. TotalTime
• B. ElapsedTime
• C. ExecutionTime
• D. Runtime

Answer: B. ElapsedTime

Explanation: The ElapsedTime property in QueryMetrics class provides the total elapsed time of the query.

What is the purpose of the QueryMetrics class in Azure Cosmos DB?

• A. To track the usage of Request Units
• B. To provide metrics for capacity planning
• C. To provide execution details for a query
• D. All of the above

Answer: D. All of the above

Explanation: The QueryMetrics class provides total elapsed time, retrieved document sizes, and total Request units consumed allowing for performance optimization and capacity planning.

True or False: You can use Azure Monitor to retrieve and use query metrics from all Azure services.

• True
• False

Answer: False

Explanation: While Azure Monitor can be used to retrieve metrics from several Azure services, not all services are supported. In the context of Cosmos DB, it does, however, provide detailed metrics for queries.

Which property in QueryMetrics class provide retrieved document size?

• A. TotalDocumentSize
• B. RetrievedDocumentSize
• C. DataSize
• D. DocumentLength

Answer: B. RetrievedDocumentSize

Explanation: The RetrievedDocumentSize property in the QueryMetrics class gives the size of the documents that were retrieved as part of the query.

What unit of measure does the TotalRequestUnitsConsumed property use?

• A. Megabytes
• B. Gigabytes
• C. Request Units
• D. Milliseconds

Answer: C. Request Units

Explanation: The TotalRequestUnitsConsumed property in the QueryMetrics class is expressed in Request Units.

True or False: It’s not possible to access query metrics without executing the query.

• True
• False

Answer: True

Explanation: Query metrics are generated as part of the execution of a query in Cosmos DB. Therefore, they cannot be accessed without executing the query.

Which of the following programming languages can use SDKs provided by Azure to retrieve query metrics?

• A. JavaScript
• B. Python
• C. .NET
• D. All of the above

Answer: D. All of the above

Explanation: Azure provides SDKs in multiple languages including JavaScript, Python, and .NET to retrieve and use query metrics from Cosmos DB.

True or False: ElapsedTime property in QueryMetrics class only gives the time taken for executing the query.

• True
• False

Answer: False

Explanation: ElapsedTime property gives the total elapsed time of the query, including any time spent waiting for a retry due to rate limiting.

Which property in QueryMetrics class provides the total number of request charge?

• A. TotalRequestUnits
• B. RequestCharge
• C. TotalRequestCharge
• D. TotalRequestUnitsConsumed

Answer: D. TotalRequestUnitsConsumed

Explanation: TotalRequestUnitsConsumed property provides the total number of request units consumed by the query.

True or False: Azure Cosmos DB query metrics do not include details about the system resources consumed by the query.

• True
• False

Answer: False

Explanation: Azure Cosmos DB query metrics includes system resource utilization, such as the number of Request Units consumed.

What is the purpose of the query execution metrics in Azure Cosmos DB?

• A. To track the real-time performance of the application
• B. To help users troubleshoot performance issues
• C. For capacity planning
• D. All of the above

Answer: D. All of the above

Explanation: Query metrics provide detailed breakdowns of the query’s execution which can be used for real-time performance tracking, troubleshooting, and capacity planning.

Interview Questions

What is Microsoft Azure Cosmos DB and how is it related to retrieving and using query metrics?

Microsoft Azure Cosmos DB is a globally distributed, multi-model database service used for managing data at scale. It allows you to use key benefits of relational databases and the benefits of cloud scalability. Retrieving and using query metrics provide detailed statistics about the individual queries, which can be used to optimize queries for best performance.

What is the purpose of EnableCrossPartitionQuery property in Cosmos DB?

When querying in Cosmos DB, the EnableCrossPartitionQuery property can be set to true to allow the query to span multiple partitions. This can provide a more comprehensive dataset for retrieval.

How to retrieve query metrics in Cosmos DB?

You can retrieve query metrics using the Cosmos DB SQL API. Through FeedOptions, set the value of EnableCrossPartitionQuery to true and the PopulateQueryMetrics to true as well.

What key insights can query metrics provide on the Cosmos DB performance?

Query metrics can provide key insights such as the number of documents returned, output document size, total query execution time, and query compilation time. They can also provide statistics about consumed RUs (Request Units), index utilization efficiency, and top-level system counters.

What does the ‘Retrieved Document Count’ value indicate in Cosmos DB query metrics?

‘Retrieved Document Count’ is the count of all the documents that were read as part of the query execution process. It does not include document lookup from Index lookups.

What does the ‘Consumed RUs’ mean in Cosmos DB query metrics?

‘Consumed RUs’ indicates the total request units consumed by the query. It’s a way to understand the capacity utilization and cost of the query.

What are the different sections of the query metrics result?

The sections of the query metrics result are the Query Preparation Times, Index Lookup Time, Document Load Time, VM Execution Time, Runtime Execution Times, and Client Side Metrics.

How to enable the retrieval of the query metrics in the .NET SDK for Cosmos DB?

To enable the retrieval of the query metrics, use the FeedOptions class when executing a query and set the PopulateQueryMetrics property to true.

How are the query metrics useful in optimizing the Cosmos DB performance?

Query metrics provide detailed insights into how a query is processed. This allows developers to understand the performance implications of their queries and thus optimize them. For instance, it can help identify expensive queries, which might need to be redesigned or indexed for better performance.

What does the ‘Index Utilization’ mean in Cosmos DB query metrics?

‘Index Utilization’ specifies how efficiently the Index was used in processing the query. A lower value may indicate that the query could be optimized better.

What does the ‘Total Query Execution Time’ mean in Cosmos DB query metrics?

‘Total Query Execution Time’ indicates the total time taken to execute the query and return results.

How to view a summary of query metrics in the Azure portal?

In the Azure portal, you can view the summary of query metrics by navigating to the Data Explorer for your Cosmos DB account and then clicking on the ‘Metrics’ tab.

Can you provide an example of when query metrics would be necessary?

Query metrics would be especially helpful when diagnosing performance issues in Cosmos DB. If a particular query is running slower than expected, the metrics could provide insights into why that might be the case.

What are Request Units (RUs) in Cosmos DB?

Request Units, or RUs, are a measure of throughput consumption in Cosmos DB. They are charged for each type of operation, such as read, write, or query operation, based on the resources required to complete the operation.

What does ‘Output Document Size’ mean in Cosmos DB query metrics?

‘Output Document Size’ is the total size of all result documents returned by the query. This can help in understanding the transfer costs associated with a query.