Request Unit Cost Analysis

Module: Optimize Azure Cosmos DB Solution

Lesson: Request Unit (RU) Cost Analysis

Introduction: Why Request Units Matter

In the world of Azure Cosmos DB, the concept of the Request Unit (RU) is the fundamental currency of performance. Unlike traditional relational databases that rely on CPU, memory, and IOPS as separate metrics, Cosmos DB abstracts these resources into a single, unified metric: the Request Unit per second (RU/s). Understanding how to measure, analyze, and optimize the RU cost of your queries is not just a technical exercise; it is a financial and operational necessity. When your application scales, inefficient queries act as a hidden tax on your budget and a bottleneck for your end-user experience.

If you ignore RU consumption, you risk encountering "429 Too Many Requests" errors, which indicate that your application has exceeded its provisioned throughput. This leads to latency spikes, failed requests, and increased costs as you are forced to scale up your provisioned throughput to compensate for poorly written queries. By mastering RU cost analysis, you transition from simply "making it work" to building highly efficient, cost-effective, and predictable data architectures. This lesson will guide you through the mechanics of RU consumption, how to analyze query costs, and the strategies required to keep those costs at a minimum.

Understanding the Mechanics of Request Units

At its core, a Request Unit is a throughput measurement that encompasses the resources required to perform database operations. Whether you are performing a simple point read (fetching a document by ID and partition key) or a complex cross-partition query involving joins and aggregations, Cosmos DB calculates the cost based on the consumption of CPU, memory, and disk I/O.

When you execute a query, the Cosmos DB engine performs several internal steps:

• Query Parsing: The engine translates your SQL-like query into an internal representation.
• Index Traversal: The engine consults the index to find the documents that match your filter criteria.
• Document Retrieval: The engine fetches the actual documents from the storage layer.
• Projection and Transformation: The engine applies filters, sorts, and projections to format the result set as requested.

Each of these steps consumes a portion of the RU budget. A point read is generally very cheap—often around 1 RU—because it uses the partition key to go directly to the specific physical partition where the data resides. Conversely, a cross-partition query that performs a full table scan can cost hundreds or thousands of RUs because it must touch every single physical partition in your container.

Callout: The Economy of Point Reads vs. Queries Think of a point read as walking into a library and knowing exactly which shelf and book ID you need. You walk straight to the shelf, grab the book, and leave. This is highly efficient. A cross-partition query is like walking into the library and asking the librarian to check every single book on every shelf in the building to see if it matches your criteria. Even if you only find one book, the effort spent searching the entire library is massive. Always prefer point reads whenever your application design allows for it.

Analyzing Query Costs: The Tools of the Trade

To optimize, you must first measure. Cosmos DB provides several ways to inspect the RU cost of your operations. Every response from the Cosmos DB SDK includes a RequestCharge property. This is the gold standard for monitoring your application's real-time performance.

Using the Azure Portal Query Explorer

The Query Explorer in the Azure portal is the most accessible place to start. When you run a query, the "Query Stats" tab provides a breakdown of the RU cost. You will see the total RU charge for the execution, as well as metrics like "Retrieved document count" and "Retrieved document size."

1. Open your Cosmos DB account in the Azure portal.
2. Navigate to the "Data Explorer" tab.
3. Select your database and container.
4. Click "New SQL Query."
5. Write your query and click "Execute Query."
6. Look at the "Query Stats" tab at the bottom of the results pane.

Using the .NET SDK

When writing code, you should capture the request charge programmatically. This allows you to log high-cost queries to your application's telemetry (like Application Insights) for later analysis.

QueryDefinition queryDefinition = new QueryDefinition("SELECT * FROM c WHERE c.status = 'active'");
using FeedIterator<MyObject> resultSet = container.GetItemQueryIterator<MyObject>(queryDefinition);

while (resultSet.HasMoreResults)
{
    FeedResponse<MyObject> response = await resultSet.ReadNextAsync();
    double requestCharge = response.RequestCharge;
    Console.WriteLine($"This batch cost {requestCharge} RUs.");
}

Tip: Always log your RequestCharge in your production environment. If a user reports a slow experience, checking the logs for that specific request ID will tell you exactly how many RUs that operation consumed, helping you isolate the culprit.

Factors Influencing RU Consumption

To optimize, you need to understand the "cost drivers" of your queries. Several factors contribute to the total RU cost of any given operation.

1. Indexing Policy

The indexing policy is the most significant factor in query cost. By default, Cosmos DB indexes every path in your JSON documents. While this makes queries fast, it increases the cost of write operations. If you have a massive index with many paths that you never actually query against, you are wasting RUs on index updates. Conversely, if you exclude a field from the index that you frequently use in a WHERE clause, your query will perform a full scan, which is extremely expensive.

2. Data Volume and Document Size

The size of the documents being returned or scanned directly impacts the RU cost. If you select all fields (SELECT *), you are pulling more data into memory than if you only select the specific fields you need (SELECT c.id, c.name). Large documents increase the I/O cost, as the engine must serialize and deserialize more bytes.

3. Cross-Partition vs. Single-Partition Queries

This is the "golden rule" of Cosmos DB optimization. A query that includes the partition key in the WHERE clause is a single-partition query. It is routed directly to the relevant partition. If you omit the partition key, the query becomes a cross-partition query. The engine must broadcast the query to every physical partition in your container, aggregate the results, and return them. The cost scales linearly with the number of physical partitions.

4. Filter Complexity

Complex filters involving multiple joins, subqueries, or expensive functions (like ST_DISTANCE or complex string manipulations) consume more CPU cycles. The more logic the engine must evaluate for every document, the higher the RU cost.

Step-by-Step: Optimizing a High-Cost Query

Let’s walk through a common scenario where a query is consuming too many RUs and how to fix it.

Scenario: You have an order history container. You are running this query to find orders for a specific user: SELECT * FROM c WHERE c.status = 'shipped'

Step 1: Identify the problem. This query does not include the userId (which is the partition key). It is a cross-partition scan. It is checking every order for every user in your database to see if the status is 'shipped'.

Step 2: Add the partition key. Include the userId in the filter. SELECT * FROM c WHERE c.userId = 'user123' AND c.status = 'shipped' This immediately turns the query into a single-partition query, drastically reducing the RU cost.

Step 3: Refine the projection. Instead of SELECT *, request only the necessary fields. SELECT c.orderId, c.orderDate FROM c WHERE c.userId = 'user123' AND c.status = 'shipped' This reduces the amount of data transferred and processed.

Step 4: Verify the index. Ensure that the userId and status fields are included in the indexing policy. If you have a composite index on (userId, status), the engine can find the exact documents without needing to load them into memory first, which is even cheaper.

Comparison Table: Query Performance Characteristics

Advanced Optimization Techniques

Leveraging Composite Indexes

When your queries involve multiple filters or a filter combined with a sort, composite indexes are your best friend. A composite index allows the query engine to satisfy the filter and the order requirements using a single index lookup.

For example, if you frequently run: SELECT * FROM c WHERE c.userId = 'user123' ORDER BY c.orderDate DESC

Without a composite index, the engine might have to retrieve all documents for the user and then sort them in memory. With a composite index on (userId, orderDate), the results are already sorted by date within the index for that specific user.

Avoiding "Expensive" Operators

Certain operators are inherently more expensive than others:

• OR: Using OR across different properties can prevent the query engine from effectively using the index.
• LIKE / CONTAINS: These require full scans of the indexed string values. If you need to search for text, consider using Azure Cognitive Search instead of forcing Cosmos DB to perform heavy string matching.
• Functions in the WHERE clause: Expressions like WHERE UPPER(c.name) = 'JOHN' prevent the index from being used because the engine must apply the UPPER function to every record. Instead, store the name in the desired format (e.g., all uppercase) and query against that.

Warning: Avoid putting functions in your WHERE clause. If you query WHERE LOWER(c.email) = '[email protected]', the database cannot use the index on email because it has to calculate the lowercase version of every single email in the container before comparing it. Store your data in a normalized format instead.

Common Pitfalls and How to Avoid Them

1. The "Select Star" Trap

Many developers use SELECT * by default. This is the easiest way to inflate your RU costs. It forces the engine to return the entire document, including all nested objects and arrays. Only return what the UI or the next service in your pipeline actually needs.

2. The "Cross-Partition" Oversight

As your database grows, you will add more physical partitions. A query that seems "cheap" today because your database is small will become "expensive" tomorrow as the number of partitions increases. Always design your queries to include the partition key from day one, even if the current data set is small.

3. Ignoring Indexing Policy Updates

Developers often forget that the indexing policy is configurable. If you add a new query to your application that filters by a new field, you must ensure that field is included in your index. If it isn't, your new query will perform a full table scan and likely crash your throughput.

4. Excessive Use of OFFSET and LIMIT

Using OFFSET and LIMIT for pagination is a common pattern, but it is expensive. To get to page 100, the engine must skip the first 99 pages of results. This still consumes RUs for the items you are skipping. Use "continuation tokens" instead, which are the native way Cosmos DB handles pagination efficiently.

Callout: Continuation Tokens vs. Offset/Limit Continuation tokens work like a bookmark. When you request a page of results, the SDK returns a token that tells the database exactly where the last request left off. The next request starts exactly where the previous one finished. Offset and Limit, by contrast, require the database to count and ignore items from the start of the collection every time, which is wasteful and slow.

Best Practices for Long-Term Maintenance

1. Monitor RU Trends: Use Azure Monitor to alert on high RU consumption. If you see a sudden spike, investigate the query logs immediately.
2. Test Queries in Staging: Never push a new query to production without checking its RU cost in a staging environment that mimics your production data volume.
3. Review Indexing Policy Regularly: As your query patterns change, your indexing policy should evolve. Remove unused indexes to save on write costs and add necessary indexes to support new query patterns.
4. Use SDK Features: The latest versions of the Cosmos DB SDKs have built-in features for query optimization and diagnostics. Keep your SDKs updated to benefit from the latest performance improvements.
5. Data Modeling: If your queries are consistently expensive, it might not be the query's fault; it might be your data model. If you are joining data across containers frequently, consider denormalizing your data into a single container to facilitate point reads.

Implementing a Query Cost Audit Process

To maintain a healthy Cosmos DB environment, you should implement a recurring audit process. This ensures that as your application evolves, you aren't accumulating "RU debt."

Step 1: Telemetry Collection Ensure that all query executions are logged with their RequestCharge. Include the query text (or a hash of it) and the partition key used.

Step 2: Threshold Alerting Set alerts in Azure Monitor for "Request Charge" per query. If a query consistently exceeds a certain threshold (e.g., 50 RUs), trigger an automated notification to your development team.

Step 3: Periodic Review Once a month, run a report to identify the "top 10 most expensive queries" by total RU consumption. Total RU consumption is calculated as: (Average RU per execution) * (Number of executions). A query that costs 10 RUs but runs 1,000,000 times a day is far more important to optimize than a query that costs 100 RUs but runs once a day.

Step 4: Refactoring Based on the report, prioritize the queries with the highest total impact. Apply the optimization techniques discussed earlier—adding composite indexes, including partition keys, or denormalizing the data model.

Key Takeaways

• RU is the Currency: Understand that every interaction with Cosmos DB has a cost. Managing this cost is as important as managing code quality.
• Prioritize Point Reads: Always strive to design your data model such that queries can target a specific partition key. This is the single most effective way to keep RU costs low.
• Measure Everything: Use the SDK's RequestCharge property to track the cost of every query in production. If you aren't measuring it, you can't optimize it.
• Projection is Key: Never use SELECT * in production code. Select only the specific fields required to minimize data transfer and memory usage.
• Indexing is a Tool, Not a Default: While default indexing is convenient, a custom indexing policy that aligns with your specific query patterns can save significant costs on both reads and writes.
• The Total Impact Metric: Focus your optimization efforts on queries that have high total consumption, not just those that have a high individual cost.
• Avoid Expensive Operators: Be wary of OR, LIKE, and functions in the WHERE clause, as these often force the query engine to ignore your indexes and perform expensive full-collection scans.

By consistently applying these principles, you will ensure that your Azure Cosmos DB solution remains performant, scalable, and cost-effective, regardless of how much your data volume grows over time. Optimization is not a one-time task; it is an ongoing practice of monitoring, analyzing, and refining your data access patterns.