Tag: Performance Tuning

Optimize query performance and Request Units (RUs) consumption by using indexing policies and consistency levels (AI-200 Exam Prep)

This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Cosmos DB for NoSQL
      --> Optimize query performance and Request Units (RUs) consumption by using indexing policies and consistency levels


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Overview

Azure Cosmos DB for NoSQL is designed to provide globally distributed, low-latency access to JSON data at scale. A key part of developing efficient Cosmos DB solutions is understanding how queries consume Request Units (RUs) and how indexing policies and consistency levels affect query performance, throughput, latency, and cost.

For the AI-200 exam, you should understand how to:

  • Explain what RUs represent.
  • Identify factors that increase or decrease RU consumption.
  • Understand how indexes improve query performance.
  • Configure indexing policies.
  • Include or exclude property paths from indexing.
  • Understand range and composite indexes.
  • Recognize when a query is likely to require a full scan.
  • Understand the relationship between partition keys and query performance.
  • Understand the five Cosmos DB consistency levels.
  • Choose an appropriate consistency level based on application requirements.
  • Understand how consistency affects read throughput.
  • Use query metrics to investigate expensive queries.

A useful way to think about optimization is:

Efficient Cosmos DB queries minimize the amount of data that must be examined and returned while using an indexing and consistency strategy appropriate for the application’s requirements.


1. Understanding Request Units (RUs)

Azure Cosmos DB uses Request Units (RUs) as a normalized measure of the resources required to perform database operations.

Instead of pricing or throttling individual operations according to CPU time, disk operations, memory, and other implementation details, Cosmos DB abstracts those resources into RUs.

For example, operations such as:

  • Creating an item
  • Reading an item
  • Updating an item
  • Deleting an item
  • Running a query

consume RUs.

The amount of RU consumption depends on the work required to perform the operation.

Important exam concept

The number of items returned is not the only factor determining RU consumption.

A query can return a small number of items while still consuming significant RUs if Cosmos DB has to examine a large amount of data.

Conversely, an efficiently indexed query may examine a relatively small amount of data and consume fewer RUs.


2. What Determines RU Consumption?

Several factors influence the RU charge of a request.

Common factors include:

  • Size of the items being read or written
  • Number of items involved
  • Number of properties being indexed
  • Query complexity
  • Whether indexes can be used efficiently
  • Whether the query is single-partition or cross-partition
  • Number of partitions involved
  • Amount of data returned
  • Consistency level
  • Type of operation

For example, consider:

SELECT *
FROM c
WHERE c.customerId = "C1001"

If customerId is efficiently indexed and the query can target the appropriate partition, the query can be relatively inexpensive.

A query such as:

SELECT *
FROM c
WHERE c.description = "some value"

may be considerably more expensive if the query requires examining many partitions or cannot efficiently use an appropriate index.


3. Why Indexing Matters

Azure Cosmos DB for NoSQL automatically indexes properties by default.

This means developers generally don’t have to create indexes manually before executing common queries.

The default indexing policy indexes every property of every item, using range indexes for string and numeric values.

This default behavior provides good general-purpose query performance.

However, an application may benefit from a custom indexing policy.

For example, suppose documents contain:

{
"id": "1001",
"customerId": "C1001",
"name": "Norm",
"description": "...",
"largeMetadata": {
"property1": "...",
"property2": "...",
"property3": "..."
}
}

If the application frequently queries:

WHERE c.customerId = "C1001"

but never queries largeMetadata, indexing every property may provide little benefit while increasing index storage and indexing work.

A custom indexing policy can exclude paths that aren’t needed for queries.


4. Indexing and Write Costs

Indexes aren’t free.

When an item is created or modified, Cosmos DB must maintain the indexes associated with that item.

Therefore, extensive indexing can increase:

  • Write RU consumption
  • Index storage
  • Index maintenance work

This creates an important optimization tradeoff:

StrategyPotential benefitPotential cost
Index many propertiesBetter query flexibilityMore index storage and write overhead
Index fewer propertiesLower indexing overheadSome queries may require scans
Use composite indexesEfficient supported multi-property queriesAdditional index maintenance
Use default policySimple and broadly effectiveMay index properties the application never queries

The goal isn’t to minimize indexes at all costs.

The goal is to index the paths required by the application’s query workload.


5. Indexing Modes

Azure Cosmos DB for NoSQL supports indexing modes that determine how indexes are maintained.

The important mode for normal querying is:

Consistent

The index is updated synchronously as items are created, updated, or deleted.

This provides predictable query behavior and is the normal indexing mode for queryable containers.

A container can also have indexing disabled by setting the indexing mode to none.

This can be useful for workloads where secondary indexing isn’t needed, such as certain key-value-style scenarios or some bulk-loading scenarios.

However, queries against a container without the necessary indexes may require scans and can therefore consume significantly more RUs.


6. Included and Excluded Paths

One of the most important ways to customize an indexing policy is through included paths and excluded paths.

An indexing policy can essentially answer:

Which JSON properties should Cosmos DB index?

For example:

{
"indexingMode": "consistent",
"includedPaths": [
{
"path": "/*"
}
],
"excludedPaths": [
{
"path": "/largeMetadata/*"
}
]
}

This approach indexes the document generally while excluding a portion that isn’t queried.

A useful rule is:

Exclude properties that don’t need to participate in queries, especially large or frequently changing properties, when doing so is appropriate for the workload.

The indexing-policy documentation recommends using an include-root/exclude-specific-path strategy when you want new properties added to the data model to be indexed automatically unless explicitly excluded.


7. The Partition Key Is Critical to Query Performance

Indexing alone does not guarantee an inexpensive query.

The partition key is also extremely important.

Consider a container partitioned by:

/customerId

A query such as:

SELECT *
FROM c
WHERE c.customerId = "C1001"

can potentially be targeted to a single logical partition.

Compare that with:

SELECT *
FROM c
WHERE c.city = "Orlando"

If city isn’t the partition key, Cosmos DB may need to execute the query across multiple partitions.

This is called a cross-partition query.

Cross-partition queries can consume more RUs because multiple partitions may need to participate.

Exam takeaway

When analyzing a query, don’t ask only:

“Is the property indexed?”

Also ask:

“Can the query be directed to the appropriate partition?”

A well-designed partition key and appropriate indexing policy work together.


8. Partition Key Indexing

There is an important detail that can appear in exam questions.

A partition key property isn’t automatically indexed merely because it is the partition key.

If the partition key isn’t /id, it should generally be included in the indexing policy when queries filter on it. Otherwise, queries using that property can be forced into full scans, increasing RU consumption.

For example, if the partition key is:

/customerId

and the application frequently queries:

WHERE c.customerId = "C1001"

the indexing policy should support that path.


9. Types of Indexes

Azure Cosmos DB supports several index types.

For AI-200, you should understand at least the major concepts surrounding:

  • Range indexes
  • Composite indexes
  • Spatial indexes
  • Vector indexes

The most important indexes for traditional query optimization are range and composite indexes.


10. Range Indexes

Range indexes are based on an ordered structure and can support many common query operations.

They can support operations such as:

=
>
<
>=
<=

as well as certain ORDER BY, JOIN, and string-function scenarios.

For example:

SELECT *
FROM c
WHERE c.price > 100

can benefit from an appropriate range index on price.

Similarly:

SELECT *
FROM c
ORDER BY c.price

requires a range index on the ordered property.


11. Composite Indexes

A composite index indexes multiple properties together.

Composite indexes are particularly useful for queries involving multiple properties and certain combinations of filtering and sorting.

For example:

SELECT *
FROM c
WHERE c.category = "AI"
ORDER BY c.timestamp DESC

may benefit from an appropriate composite index involving:

/category
/timestamp

The order of properties in a composite index matters.

For example, these are not necessarily interchangeable:

(category ASC, timestamp DESC)

and:

(timestamp DESC, category ASC)

The appropriate ordering depends on the query workload.

Exam tip

If a question describes a query using multiple properties with filtering and/or ordering, think:

Could a composite index make this query more efficient?


12. Index Utilization

Cosmos DB’s query engine can use indexes in different ways.

The query engine can perform operations ranging from highly efficient index seeks to full scans.

Generally, the progression is:

  1. Index seek
  2. Precise index scan
  3. Expanded index scan
  4. Full index scan
  5. Full scan

An index seek is particularly efficient because the query engine can identify the relevant index entries without examining the entire dataset.

A full scan is considerably more expensive because Cosmos DB must inspect the underlying data rather than efficiently locating matching records through an appropriate index.


13. Why SELECT * Can Cost More

The amount of data returned affects RU consumption.

Consider:

SELECT *
FROM c
WHERE c.customerId = "C1001"

versus:

SELECT c.id, c.name
FROM c
WHERE c.customerId = "C1001"

The second query may consume fewer RUs because it returns less data.

This leads to an important optimization principle:

Return only the properties your application needs.

Avoid retrieving large documents when only a few properties are required.


14. Avoid Unnecessary Cross-Partition Queries

Suppose a container has:

Partition key: /customerId

This query can potentially target a partition:

SELECT c.id, c.name
FROM c
WHERE c.customerId = "C1001"

But this query may involve many partitions:

SELECT c.id, c.name
FROM c
WHERE c.status = "Active"

If status isn’t the partition key, Cosmos DB may need to query multiple partitions.

Cross-partition queries aren’t inherently bad.

They are sometimes necessary.

The important point is:

Don’t accidentally create expensive cross-partition queries when the application can supply the partition key.


15. Measuring Query RU Consumption

The Cosmos DB SDKs provide information about the RU charge associated with operations.

For example, application code can inspect the response from a query and determine how many RUs were consumed.

This is valuable because optimization should be based on actual workload measurements rather than assumptions.

When troubleshooting an expensive query, examine:

  • RU charge
  • Query execution time
  • Number of returned documents
  • Index utilization
  • Number of partitions involved
  • Query predicates
  • Requested properties
  • Partition-key usage

16. Index Transformation

Changing an indexing policy can cause Cosmos DB to perform an index transformation.

For example, adding an indexed path requires Cosmos DB to build the new index for existing data.

Index transformation is asynchronous and consumes RUs. Queries begin using a newly added indexed path after the index transformation has completed.

This is important operationally.

If you replace one index with another, a good strategy is generally:

  1. Add the new index.
  2. Wait for the transformation to complete.
  3. Verify the workload.
  4. Remove the old index if it is no longer required.

Removing an indexed path takes effect immediately, so removing an index before the replacement is ready can temporarily cause queries to fall back to scans.


17. Understanding Consistency Levels

Indexing affects how efficiently data can be located.

Consistency affects what version of the data a read is allowed to return.

Azure Cosmos DB provides five consistency levels, ordered from strongest to weakest:

  1. Strong
  2. Bounded staleness
  3. Session
  4. Consistent prefix
  5. Eventual

Choosing the consistency level is a business and application decision.

You should not automatically select the strongest consistency level.


18. Strong Consistency

Strong consistency guarantees that reads return the latest committed version of the data.

This provides the strongest read guarantee.

The tradeoff is that strong consistency can increase write latency and reduce availability in some globally distributed scenarios because replicas must satisfy the stronger synchronization requirements.

Appropriate scenarios

Strong consistency may be appropriate for scenarios where stale data is unacceptable, such as:

  • Certain financial transactions
  • Critical inventory decisions
  • Applications requiring immediate globally consistent reads

Exam clue

If a question says:

“The application must always read the most recently committed value.”

Think:

Strong consistency.


19. Bounded Staleness

Bounded staleness guarantees that reads aren’t allowed to become older than a configured limit based on:

  • Time
  • Number of versions/operations

This is useful when the application can tolerate a controlled amount of replication lag but needs a stronger guarantee than eventual consistency.

For example:

“Data can be up to a few seconds old, but never older than that.”

This points toward bounded staleness.

Bounded staleness is particularly relevant to globally distributed applications that need near-strong consistency without the full cost of strong consistency.


20. Session Consistency

Session consistency is commonly useful for interactive applications.

It provides guarantees such as:

  • Read-your-writes
  • Monotonic reads
  • Monotonic writes

In practical terms, a user who writes data should be able to read that data within the same session.

For example:

  1. User updates their profile.
  2. User immediately refreshes the profile.
  3. The application should see the user’s update.

Session consistency is often a good balance between strong consistency and scalability.


21. Consistent Prefix

Consistent prefix guarantees that reads see writes in the order they occurred, without observing them out of sequence.

The application may not immediately see every write, but it won’t see writes in an inconsistent order.

For example, suppose writes occur in this order:

A → B → C → D

A reader might see:

A
A, B
A, B, C
A, B, C, D

but shouldn’t see:

A, C

while missing B.


22. Eventual Consistency

Eventual consistency provides the weakest consistency guarantee.

Different replicas may temporarily return different values, but replicas eventually converge.

The major advantages include:

  • Lower coordination requirements
  • High availability
  • Good performance
  • Lower latency in many distributed scenarios

Eventual consistency may be appropriate for:

  • Social feeds
  • Recommendation systems
  • Analytics dashboards
  • Non-critical status information
  • Content where temporary staleness is acceptable

23. Consistency and Read Throughput

Consistency isn’t simply about correctness.

It can also affect read throughput.

For strong and bounded staleness consistency, reads are performed against two replicas in a four-replica set to satisfy the consistency guarantees.

Session, consistent prefix, and eventual consistency use single-replica reads.

Consequently, for the same number of provisioned RUs, strong and bounded staleness consistency provide approximately half the read throughput of the weaker consistency levels.

This is a very important AI-200 exam concept.

Remember:

Stronger consistency can consume more read capacity.

Therefore, if an application does not require strong consistency, relaxing the consistency requirement can improve read scalability.


24. Consistency Does Not Change Write RU Charges

For the same type of write operation, write RU consumption is generally identical across consistency levels.

However, stronger consistency can have other performance implications, particularly around replication and latency.

Therefore, don’t confuse:

Consistency → read behavior and read throughput

with:

Indexing → query efficiency and index maintenance

Both affect application performance, but in different ways.


25. Choosing the Right Consistency Level

A useful decision framework is:

RequirementRecommended consideration
Must always see the latest committed valueStrong
Can tolerate a precisely bounded amount of stalenessBounded staleness
Users need read-your-writes behaviorSession
Writes must appear in order but can be delayedConsistent prefix
Temporary inconsistency is acceptableEventual

The key is to choose the weakest consistency level that still satisfies the application’s requirements.

This can improve scalability and reduce unnecessary coordination.


26. Combining Indexing and Consistency Optimization

Indexing and consistency should be considered separately.

Suppose an application has an expensive query.

You might investigate:

Indexing

  • Is the filtered property indexed?
  • Is an appropriate range index available?
  • Is a composite index appropriate?
  • Is the partition key included in the indexing policy?
  • Is the query performing a full scan?
  • Are unnecessary properties being indexed?

Query design

  • Is the partition key supplied?
  • Is the query unnecessarily cross-partition?
  • Is SELECT * returning unnecessary data?
  • Can the query be simplified?

Consistency

  • Does the application actually require strong consistency?
  • Could session consistency satisfy the requirement?
  • Could eventual consistency satisfy the requirement?

This distinction is important:

Don’t try to solve every RU problem by changing the indexing policy.

Likewise:

Don’t weaken consistency when the application actually requires stronger guarantees.


27. A Practical Optimization Example

Imagine an AI-powered customer-support application.

The container contains millions of support conversations.

The partition key is:

/customerId

The application runs:

SELECT *
FROM c
WHERE c.customerId = "C1001"
AND c.status = "Open"
ORDER BY c.createdDate DESC

Several optimization questions should be considered.

Question 1: Can the query target a partition?

Yes.

It specifies:

customerId = C1001

which is the partition key.

Question 2: Are the relevant properties indexed?

The query uses:

customerId
status
createdDate

The indexing policy should support the query.

Question 3: Would a composite index help?

Potentially.

The query combines filtering and sorting across multiple properties, so a composite index may be appropriate depending on the exact query workload and index requirements.

Question 4: Does the application need every property?

Perhaps not.

Instead of:

SELECT *

the application could retrieve only:

SELECT c.id, c.status, c.createdDate, c.subject

Question 5: Does the application need strong consistency?

If the support application can tolerate some temporary staleness, a weaker consistency level may provide better read scalability.

This illustrates an important principle:

Query performance is usually the result of several design decisions working together.


28. Common AI-200 Exam Traps

Trap 1: “Indexes always reduce RU consumption.”

Not necessarily.

Indexes can reduce the amount of data that must be examined for queries, but maintaining indexes also adds write and storage overhead.


Trap 2: “The partition key automatically makes the property indexed.”

Not necessarily.

The partition key should be considered separately from the indexing policy. A partition key property should be included in the indexing policy when queries need to efficiently filter on it.


Trap 3: “Strong consistency is always better.”

Strong consistency provides stronger guarantees, but it can reduce read throughput and increase latency/availability tradeoffs.

Choose it only when required.


Trap 4: “Eventual consistency means data is permanently inconsistent.”

No.

Eventual consistency means replicas may temporarily disagree, but they eventually converge.


Trap 5: “A query returning one item must be inexpensive.”

Not necessarily.

Cosmos DB may have to examine many items or partitions to discover that single matching item.


Trap 6: “Cross-partition queries are always wrong.”

No.

Cross-partition queries are sometimes necessary.

The goal is to avoid unnecessary cross-partition queries and design the partition key appropriately for the workload.


Trap 7: “Removing an index is harmless.”

Removing an index can cause queries that depended on it to fall back to less efficient execution, potentially increasing RU consumption.


29. AI-200 Exam Quick Reference

ConceptRemember
RUNormalized unit of Cosmos DB resource consumption
IndexHelps locate matching data efficiently
Default indexingAutomatically indexes properties by default
Custom indexingCan include/exclude paths
Range indexEquality, range, ordering, and other supported operations
Composite indexMultiple-property query patterns
Full scanPotentially expensive; examines underlying data broadly
Partition keyDetermines data distribution and can enable targeted queries
Cross-partition queryMay require querying multiple partitions
SELECT *Can return more data and increase RU consumption
Strong consistencyLatest committed value
Bounded stalenessControlled maximum staleness
SessionRead-your-writes and session guarantees
Consistent prefixWrites observed in order
EventualTemporary inconsistency allowed
Strong/bounded read throughputLower than weaker levels for same RU allocation
Index transformationAsynchronous and consumes RUs
Best practiceChoose indexes and consistency based on workload requirements

Practice Exam Questions

Question 1

An application stores customer records in Azure Cosmos DB for NoSQL. The container is partitioned by /customerId. The application frequently executes the following query:

SELECT *
FROM c
WHERE c.customerId = "C1005"

The developer wants to minimize RU consumption.

Which approach is most appropriate?

A. Add a spatial index to the customerId property.

B. Disable indexing so the query engine can scan the container faster.

C. Change the consistency level to Strong regardless of the application’s requirements.

D. Ensure the customerId path is appropriately indexed and provide the partition key value when executing the query.

Answer: D

Explanation

The query uses the partition key, allowing Cosmos DB to target the appropriate logical partition. The property should also be appropriately indexed when queries filter on it. This combination can significantly improve query efficiency.

Disabling indexing would generally make query execution less efficient. Spatial indexes are intended for geospatial data, not customer identifiers. Strong consistency does not inherently optimize this query.


Question 2

A globally distributed application displays product recommendations. Recommendations can be temporarily stale as long as replicas eventually converge.

Which consistency level is generally the most appropriate?

A. Strong

B. Bounded staleness

C. Session

D. Eventual

Answer: D

Explanation

The application explicitly permits temporary staleness and does not require read-your-writes or strict ordering guarantees. Eventual consistency is therefore appropriate.

Strong consistency provides stronger guarantees than necessary. Bounded staleness provides a specific staleness guarantee that isn’t required by the scenario. Session consistency would provide stronger session-level guarantees than needed.


Question 3

A Cosmos DB container contains documents with hundreds of properties. An application queries only /customerId, /status, and /createdDate. Many large metadata properties are never queried.

The development team wants to reduce indexing overhead and index storage.

What should they consider?

A. Enable strong consistency.

B. Customize the indexing policy to exclude properties that don’t need to be queried.

C. Remove the partition key.

D. Replace all range indexes with spatial indexes.

Answer: B

Explanation

A custom indexing policy can exclude properties that don’t participate in queries. This can reduce index size and indexing maintenance overhead.

Changing consistency doesn’t address unnecessary indexes. Removing the partition key is not an appropriate optimization, and spatial indexes aren’t appropriate for ordinary scalar properties such as customer IDs and status values.


Question 4

An application requires that a user immediately see an item after the user creates it, but the application does not require globally strong consistency for every user.

Which consistency level is generally the best fit?

A. Eventual

B. Consistent prefix

C. Session

D. Strong

Answer: C

Explanation

Session consistency provides read-your-writes behavior and is well suited to interactive applications where a user expects to see their own changes.

Eventual consistency doesn’t provide the same session guarantees. Consistent prefix guarantees write ordering but doesn’t provide the same read-your-writes behavior. Strong consistency is stronger than necessary for the stated requirement.


Question 5

A query returns only one document but consumes a surprisingly large number of RUs. The query doesn’t specify the partition key and runs against a container with many physical partitions.

What is the most likely explanation?

A. Cosmos DB charges a fixed RU amount for every returned document.

B. The query must always use a spatial index.

C. The query may be executing across multiple partitions and examining significant amounts of data before finding the matching document.

D. Returning one document always requires Strong consistency.

Answer: C

Explanation

The number of returned documents isn’t the only determinant of RU consumption. A cross-partition query can require Cosmos DB to examine multiple partitions, potentially consuming significant RUs even if only one document ultimately matches.

There is no fixed RU charge per returned document, spatial indexing is unrelated, and consistency doesn’t automatically become Strong because one document is returned.


Question 6

A query uses:

SELECT *
FROM c
WHERE c.category = "AI"
ORDER BY c.timestamp DESC

The application frequently executes this query and wants to optimize its performance.

Which index type should the developer investigate first?

A. Composite index

B. Spatial index

C. Vector index

D. No index; ORDER BY queries cannot use indexes

Answer: A

Explanation

The query uses multiple properties in filtering and ordering. A composite index can be useful for query patterns involving multiple properties and sorting.

Spatial indexes are designed for geospatial operations. Vector indexes are designed for vector search. Cosmos DB can use indexes for ORDER BY operations.


Question 7

An application currently uses Strong consistency. Performance testing shows that read throughput is insufficient. The application requirements state that users only need read-your-writes behavior within their own sessions.

What should the developer consider?

A. Add a spatial index.

B. Change the partition key to /id without analyzing the workload.

C. Disable all indexes.

D. Use Session consistency if it satisfies the application’s requirements.

Answer: D

Explanation

Session consistency provides read-your-writes behavior and other session-level guarantees while avoiding the stronger coordination requirements of Strong consistency.

Changing the partition key or disabling indexes doesn’t directly address the stated consistency requirement. Spatial indexing is unrelated.


Question 8

A developer removes an indexed path from a Cosmos DB indexing policy because the property is no longer queried. An existing query unexpectedly begins consuming substantially more RUs.

What is the most likely explanation?

A. Removing an indexed path causes all writes to become strongly consistent.

B. The query may no longer be able to use the removed index and may fall back to a less efficient scan.

C. Removing an index automatically converts the container into a different API.

D. Cosmos DB stops supporting partitioning when an index is removed.

Answer: B

Explanation

When an indexed path is removed, queries that relied on that index may no longer be able to use it and can fall back to a full scan or another less efficient execution strategy. This can substantially increase RU consumption.

The other options describe behaviors that don’t occur as a result of removing an indexed path.


Question 9

A company wants to ensure that reads never return a value older than a configured amount of time or number of updates, but it doesn’t require Strong consistency.

Which consistency level should the developer select?

A. Eventual

B. Session

C. Bounded staleness

D. Consistent prefix

Answer: C

Explanation

Bounded staleness is specifically designed for scenarios where the application can tolerate a controlled amount of staleness based on time or the number of versions/operations.

Eventual consistency provides no such bounded staleness guarantee. Session consistency focuses on session-level guarantees, while consistent prefix guarantees write ordering rather than a specific staleness bound.


Question 10

A Cosmos DB account has a workload dominated by read operations. The application doesn’t require Strong or Bounded Staleness consistency. The team wants to maximize read throughput for the same provisioned RU capacity.

Which approach is most appropriate?

A. Use Session, Consistent Prefix, or Eventual consistency according to the application’s requirements.

B. Increase indexing on every possible property.

C. Change every query to SELECT *.

D. Use Strong consistency for all queries.

Answer: A

Explanation

Strong and Bounded Staleness consistency use more replicas for reads and therefore provide approximately half the read throughput of Session, Consistent Prefix, and Eventual consistency for the same RU allocation.

If the application doesn’t require the stronger guarantees, using an appropriate weaker consistency level can improve read scalability.

Increasing indexes can help particular queries but doesn’t address the consistency-related read-throughput issue. SELECT * can actually increase data returned and RU consumption, while Strong consistency would move in the opposite direction from the desired optimization.


Final Exam Takeaways

For AI-200, the most important concepts to remember are:

  1. RUs represent the resources consumed by Cosmos DB operations.
  2. Indexes can make queries substantially more efficient, but maintaining indexes has a cost.
  3. The default indexing policy indexes properties automatically.
  4. Custom indexing policies can include or exclude property paths.
  5. Range indexes support many common equality, range, and ordering operations.
  6. Composite indexes are important for appropriate multi-property query patterns.
  7. A partition-key-aware query is generally more efficient than an unnecessary cross-partition query.
  8. The partition key should be considered separately from indexing.
  9. Returning unnecessary data, such as with SELECT *, can increase RU consumption.
  10. Strong consistency provides the strongest read guarantee but has performance and availability tradeoffs.
  11. Bounded staleness provides a controlled staleness guarantee.
  12. Session consistency provides important read-your-writes behavior for interactive applications.
  13. Consistent prefix preserves write ordering.
  14. Eventual consistency provides the weakest guarantees but can maximize scalability and availability.
  15. Strong and bounded staleness provide lower read throughput for the same RU allocation than Session, Consistent Prefix, and Eventual consistency.
  16. Index transformations consume RUs and occur asynchronously.
  17. When optimizing Cosmos DB, consider the combination of partitioning, indexing, query design, returned data, and consistency—not any one factor in isolation.

Go to the AI-200 Exam Prep Hub main page

Identify and resolve query performance issues, including blocking and deadlocks – Part 2 (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

In Part 1, we discussed locking, blocking, deadlocks, transaction isolation levels, and concurrency controls. In this section, we will focus on the tools and techniques that SQL developers use to diagnose and resolve performance issues. These tools are frequently referenced throughout Microsoft documentation and are highly relevant for the DP-800 certification exam.

After completing this article, you should be able to:

  • Interpret query execution plans.
  • Use Query Store to analyze historical query performance.
  • Leverage Dynamic Management Views (DMVs) to monitor database activity.
  • Capture performance issues with Extended Events.
  • Interpret wait statistics.
  • Optimize indexes.
  • Address parameter sniffing issues.
  • Maintain statistics.
  • Follow a structured performance tuning methodology.
  • Recognize common DP-800 exam scenarios.

A Structured Performance Tuning Process

Performance tuning should follow a systematic approach rather than relying on guesswork.

A recommended workflow is:

  1. Identify the slow query.
  2. Capture the execution plan.
  3. Examine wait statistics.
  4. Check index usage.
  5. Review Query Store history.
  6. Examine DMVs.
  7. Optimize the query or indexes.
  8. Test the improvement.
  9. Monitor ongoing performance.

Following a structured process helps avoid unnecessary changes that may introduce new problems.


Understanding Query Execution Plans

An execution plan is a roadmap that shows how SQL Server processes a query.

It displays:

  • Order of operations
  • Index usage
  • Join methods
  • Estimated and actual row counts
  • Operator costs
  • Memory grants
  • Parallelism decisions

Execution plans help identify why a query is slow.


Estimated vs. Actual Execution Plans

Estimated Execution Plan

Generated before execution.

Advantages:

  • No query execution required
  • Useful during development
  • Quick to generate

Limitations:

  • Uses estimated statistics
  • Does not show runtime behavior

Actual Execution Plan

Generated while the query executes.

Advantages:

  • Shows actual row counts
  • Displays actual execution times
  • More accurate for troubleshooting

Requires executing the query.


Reading Execution Plans

Several operators frequently appear in execution plans.

Index Seek

The optimizer directly locates matching rows.

Characteristics:

  • Fast
  • Efficient
  • Low I/O
  • Preferred operation

Index Scan

Reads most or all index pages.

May be acceptable when:

  • Returning many rows
  • Small tables

May indicate missing indexes if unexpected.


Table Scan

Reads the entire table.

Usually indicates:

  • Missing indexes
  • Poor filtering
  • Small tables

Large table scans often create significant I/O.


Nested Loop Join

Efficient when one input is small.

Ideal for:

  • Primary key lookups
  • Highly selective joins

Merge Join

Efficient when both inputs are sorted.

Often used with:

  • Clustered indexes
  • Ordered datasets

Hash Match

Builds hash tables.

Common for:

  • Large joins
  • Large aggregations

Requires considerable memory.


Cost Percentages

Execution plans assign estimated costs.

Example:

OperatorCost
Index Seek5%
Nested Loop10%
Sort35%
Hash Match50%

These percentages are estimates, not actual elapsed time.

Focus on expensive operators as starting points for optimization.


Warning Indicators

Execution plans may include warnings such as:

  • Missing indexes
  • Implicit conversions
  • Hash spills
  • Sort spills
  • Excessive memory grants
  • Parallelism skew

Warnings deserve investigation but should not automatically be implemented without testing.


Query Store

Query Store records query history over time.

It stores:

  • Query text
  • Execution plans
  • Runtime statistics
  • Resource consumption
  • Plan history
  • Wait statistics (supported versions)

Unlike the plan cache, Query Store persists across restarts.


Benefits of Query Store

Query Store enables developers to:

  • Identify regressed queries
  • Compare historical execution plans
  • Detect parameter-sensitive plan changes
  • Force a known good execution plan
  • Analyze workload trends

Query Store is one of the most valuable performance troubleshooting features available in SQL Server and Azure SQL.


Common Query Store Reports

Useful reports include:

  • Top Resource Consuming Queries
  • Queries with High Duration
  • Queries with High CPU
  • Query Wait Statistics
  • Regressed Queries
  • Plan Comparison

These reports quickly identify problematic queries.


Forcing Execution Plans

Occasionally, SQL Server chooses a poor plan.

Query Store allows administrators to force a previous stable plan.

Advantages:

  • Quick recovery
  • No code modification
  • Useful after upgrades
  • Helps address parameter-sensitive regressions

Forced plans should be monitored to ensure they remain optimal as data changes.


Dynamic Management Views (DMVs)

DMVs provide real-time information about SQL Server activity.

They are essential for performance troubleshooting.

Examples include:

  • Active requests
  • Sessions
  • Index usage
  • Missing indexes
  • Wait statistics
  • Cached execution plans
  • Memory usage

Frequently Used DMVs

sys.dm_exec_requests

Displays currently executing requests.

Useful columns include:

  • session_id
  • status
  • wait_type
  • blocking_session_id
  • cpu_time
  • logical_reads

Example:

SELECT
session_id,
status,
cpu_time,
logical_reads,
blocking_session_id
FROM sys.dm_exec_requests;

sys.dm_exec_sessions

Displays connected sessions.

Useful for identifying:

  • Login information
  • Application names
  • Client connections
  • Session status

sys.dm_exec_query_stats

Provides cumulative statistics.

Includes:

  • Execution count
  • CPU usage
  • Logical reads
  • Elapsed time

Excellent for identifying expensive queries.


sys.dm_db_index_usage_stats

Shows index usage.

Useful for identifying:

  • Unused indexes
  • Frequently used indexes
  • Missing optimization opportunities

sys.dm_db_missing_index_details

Recommends potential indexes.

Important:

These recommendations should always be evaluated carefully rather than implemented automatically.


Extended Events

Extended Events is SQL Server’s modern monitoring framework.

It replaces SQL Trace and SQL Server Profiler for most workloads.

Advantages:

  • Lightweight
  • Highly configurable
  • Lower overhead
  • Suitable for production environments

Common Extended Event Sessions

Extended Events can capture:

  • Deadlocks
  • Blocking
  • Long-running queries
  • Login failures
  • Wait statistics
  • Query execution
  • Memory grants

The built-in system_health session captures many important diagnostic events by default.


Wait Statistics

Wait statistics show where SQL Server spends time waiting.

Rather than measuring CPU usage alone, waits reveal resource bottlenecks.

Common categories include:

  • CPU
  • Disk I/O
  • Memory
  • Locks
  • Network
  • Parallelism

Common Wait Types

LCK_M_*

Lock waits.

Indicate blocking.

Possible causes:

  • Long transactions
  • Lock contention
  • Missing indexes

PAGEIOLATCH_*

Waiting for data pages from disk.

May indicate:

  • Slow storage
  • Large scans
  • Insufficient memory

CXPACKET / CXCONSUMER

Related to parallel query execution.

May indicate:

  • Large parallel queries
  • Uneven workload distribution

Not always a problem.


WRITELOG

Waiting for transaction log writes.

May indicate:

  • Heavy write activity
  • Slow storage subsystem

SOS_SCHEDULER_YIELD

CPU scheduling wait.

May indicate CPU pressure.


Index Optimization

Indexes greatly influence performance.

Well-designed indexes reduce:

  • Logical reads
  • CPU usage
  • Query duration
  • Blocking

Poor indexes increase maintenance costs.


Clustered vs. Nonclustered Indexes

Clustered Index

  • Determines physical row order.
  • One per table.
  • Ideal for range queries.

Nonclustered Index

  • Separate structure.
  • Many allowed.
  • Ideal for selective lookups.

Covering Indexes

A covering index contains all columns required by a query.

Benefits include:

  • Eliminates key lookups
  • Reduces logical reads
  • Improves performance

Example:

CREATE INDEX IX_Orders_Customer
ON Sales.Orders(CustomerID)
INCLUDE(OrderDate, TotalAmount);

Index Fragmentation

Fragmented indexes reduce performance.

Maintenance options include:

FragmentationRecommended Action
Less than 5%No action
5–30%Reorganize
Greater than 30%Rebuild

Regular maintenance improves read performance.


Parameter Sniffing

SQL Server caches execution plans.

Sometimes the first parameter value generates a plan that performs poorly for later executions.

Example:

A plan optimized for one customer with only a few orders may perform poorly when reused for a customer with millions of orders.

Potential mitigation techniques include:

  • OPTION (RECOMPILE)
  • OPTIMIZE FOR
  • Local variables (used judiciously)
  • Query Store plan forcing
  • Query redesign

Understanding parameter sniffing is an important DP-800 objective.


Statistics Maintenance

Statistics help SQL Server estimate row counts.

Outdated statistics lead to:

  • Poor cardinality estimates
  • Incorrect join selection
  • Poor execution plans

Maintenance options include:

UPDATE STATISTICS Sales.Orders;

or

EXEC sp_updatestats;

Automatic statistics updates are generally sufficient for many workloads, but large or highly volatile databases may benefit from scheduled maintenance.


Intelligent Performance Features

Modern SQL Server and Azure SQL include intelligent features such as:

  • Automatic tuning
  • Automatic plan correction
  • Automatic index recommendations
  • Intelligent Insights (Azure SQL)
  • Automatic statistics updates

These features assist administrators but should complement—not replace—performance analysis and testing.


Common Performance Optimization Techniques

When troubleshooting slow queries:

  • Retrieve only required columns.
  • Avoid SELECT *.
  • Use appropriate indexes.
  • Write SARGable predicates.
  • Keep transactions short.
  • Avoid cursors when set-based operations are possible.
  • Maintain indexes and statistics.
  • Reduce unnecessary sorting.
  • Limit large result sets.
  • Batch large modifications.

Performance Troubleshooting Checklist

When investigating a slow query:

☐ Is an appropriate index available?

☐ Is SQL Server performing an Index Seek or Table Scan?

☐ Are statistics current?

☐ Are implicit conversions occurring?

☐ Is blocking present?

☐ Is parameter sniffing affecting performance?

☐ Are waits indicating CPU, I/O, or locking problems?

☐ Does Query Store show a regression?

☐ Can the query be rewritten more efficiently?

☐ Has the improvement been tested before deployment?


Real-World Scenario 1: Missing Index

A customer search query takes 18 seconds.

Execution plan shows:

  • Table Scan
  • Missing Index recommendation

Resolution:

Create an appropriate nonclustered index and validate the improvement with the actual execution plan.


Real-World Scenario 2: Parameter-Sensitive Plan

A stored procedure runs quickly for most customers but very slowly for one large customer.

Investigation shows a cached plan optimized for a small data set.

Resolution:

Evaluate parameter-sensitive plan optimization techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, Query Store plan forcing, or redesigning the query, depending on the workload.


Real-World Scenario 3: Blocking Chain

Users report intermittent timeouts.

DMVs reveal:

Session 52 blocks Session 63.

Session 63 blocks Session 81.

Session 81 blocks Session 96.

Root cause:

A long-running transaction remained open while waiting for application logic.

Resolution:

Reduce transaction duration and ensure commits occur as quickly as possible.


DP-800 Exam Tips

  • Understand the differences between Estimated and Actual execution plans.
  • Know when Index Seeks are preferred over Table Scans.
  • Be familiar with Query Store, including plan history, runtime statistics, and plan forcing.
  • Recognize the most commonly used DMVs for monitoring active requests, sessions, query performance, and index usage.
  • Understand how Extended Events have largely replaced SQL Trace and SQL Server Profiler for production monitoring.
  • Learn to interpret common wait types such as LCK_M_*, PAGEIOLATCH_*, CXPACKET, WRITELOG, and SOS_SCHEDULER_YIELD.
  • Understand the role of statistics, parameter sniffing, covering indexes, and fragmentation in query performance.
  • Remember that Microsoft recommends making tuning decisions based on evidence from execution plans and monitoring tools, rather than assumptions.

Key Takeaways

Performance tuning is an iterative process that combines analysis, measurement, and optimization. SQL Server provides a rich set of diagnostic tools—including execution plans, Query Store, DMVs, Extended Events, wait statistics, and index analysis—that help developers identify and resolve bottlenecks. For the DP-800 exam, you should be comfortable selecting the appropriate diagnostic tool, interpreting its results, and recommending effective solutions to improve query performance while maintaining scalability and concurrency.


Go to the DP-800 Exam Prep Hub main page

Identify and resolve query performance issues, including blocking and deadlocks – Part 1 (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Efficient query performance is one of the most important responsibilities of a SQL developer. Regardless of whether a database is hosted in SQL Server, Azure SQL Database, Azure SQL Managed Instance, or Microsoft Fabric SQL Database, applications depend on queries executing quickly while maintaining data consistency and supporting concurrent users.

Poor-performing queries can cause excessive CPU usage, memory pressure, storage bottlenecks, long response times, and application outages. Likewise, poorly managed concurrency can result in blocking and deadlocks that significantly impact user productivity.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how SQL Server manages concurrent transactions, recognize common performance issues, detect blocking and deadlocks, and apply best practices to resolve these problems.


Learning Objectives

After completing this article, you should be able to:

  • Explain why query performance optimization is important.
  • Identify common causes of poor query performance.
  • Understand SQL Server locking behavior.
  • Explain blocking and deadlocks.
  • Recognize how transaction isolation levels affect concurrency.
  • Detect blocking sessions.
  • Detect deadlocks.
  • Apply techniques to reduce blocking and deadlocks.
  • Troubleshoot real-world concurrency problems.

Why Query Performance Matters

Every SQL query consumes system resources. Poorly optimized queries consume more resources than necessary and may affect every user connected to the database.

Common consequences include:

  • Slow application response times
  • High CPU utilization
  • Excessive memory consumption
  • Increased disk I/O
  • Long-running transactions
  • Lock contention
  • Blocking
  • Deadlocks
  • Reduced scalability

Database performance is not solely about executing a single query quickly—it is about enabling thousands of users to work simultaneously without interfering with each other.


Common Causes of Poor Query Performance

Many performance problems originate from inefficient query design.

Common causes include:

Missing Indexes

Without appropriate indexes, SQL Server performs table scans rather than index seeks.

Instead of reading a few rows:

CustomerID = 1205

SQL Server may need to scan millions of rows.

Symptoms include:

  • High logical reads
  • High physical reads
  • Increased CPU usage
  • Long execution times

Poor Index Design

Too many indexes can slow writes.

Too few indexes slow reads.

Poor index design includes:

  • Incorrect clustered indexes
  • Missing covering indexes
  • Duplicate indexes
  • Unused indexes
  • Highly fragmented indexes

Returning More Data Than Necessary

Instead of:

SELECT *
FROM Sales.Orders;

Use:

SELECT OrderID,
CustomerID,
OrderDate
FROM Sales.Orders;

Benefits include:

  • Reduced network traffic
  • Less memory usage
  • Faster execution
  • Smaller execution plans

Non-SARGable Queries

SARGable means Search Argument Able.

Bad example:

WHERE YEAR(OrderDate) = 2025

Because SQL Server must calculate YEAR() for every row.

Better:

WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'

Now an index on OrderDate can be used.


Implicit Data Type Conversions

Example:

WHERE CustomerID = '100'

if CustomerID is an integer.

SQL Server may convert every value before comparison.

Better:

WHERE CustomerID = 100

Outdated Statistics

Statistics help the optimizer estimate row counts.

Outdated statistics lead to:

  • Poor cardinality estimates
  • Incorrect join choices
  • Bad execution plans
  • Longer execution times

Parameter Sniffing

Stored procedures reuse cached execution plans.

A plan optimized for:

CustomerID = 1

may perform poorly for:

CustomerID = 999999

DP-800 candidates should understand that parameter sniffing can sometimes degrade performance and that techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, or query hints may be used selectively to address it.


Understanding Locking

SQL Server uses locks to ensure:

  • Data consistency
  • Transaction isolation
  • Integrity during concurrent access

Locks prevent conflicting operations from occurring simultaneously.

Example:

User A updates:

OrderID = 100

Before User A commits,

User B attempts to update the same row.

SQL Server places User B into a waiting state until User A completes.

This waiting is called blocking.


Types of Locks

Several lock types are important for the DP-800 exam.

Shared (S)

Used for reading.

Multiple users may hold Shared locks simultaneously.

Example:

SELECT

Exclusive (X)

Used for modifications.

Example:

UPDATE
DELETE
INSERT

Only one Exclusive lock can exist on a resource.


Update (U)

Used during updates.

Prevents certain deadlock scenarios.

Typically upgraded to an Exclusive lock when data is modified.


Intent Locks

Used internally.

Examples include:

  • IS
  • IX
  • SIX

These indicate SQL Server intends to place locks at lower levels.


Schema Locks

Protect database object definitions.

Examples:

ALTER TABLE
CREATE INDEX

Lock Granularity

SQL Server can lock at multiple levels.

  • Row
  • Key
  • Page
  • Extent
  • Table
  • Database

Smaller locks improve concurrency.

Larger locks reduce overhead but may increase blocking.


Lock Escalation

SQL Server may automatically replace many row locks with a table lock.

Example:

Instead of:

20,000 row locks

SQL Server escalates to:

One table lock

Benefits:

  • Lower memory usage

Drawback:

  • More blocking

Understanding Blocking

Blocking occurs when one session waits for another session to release a lock.

Example

Session 1:

BEGIN TRANSACTION;
UPDATE Products
SET Price = Price * 1.05
WHERE ProductID = 5;

Transaction remains open.

Session 2:

SELECT *
FROM Products
WHERE ProductID = 5;

Session 2 waits.

This is normal behavior.

Blocking protects data consistency.


When Blocking Becomes a Problem

Short blocking is expected.

Long blocking causes:

  • Slow applications
  • Timeouts
  • User frustration
  • Connection pooling issues
  • Increased resource usage

Common causes include:

  • Long-running transactions
  • User interaction inside transactions
  • Large batch updates
  • Missing indexes
  • Table scans
  • Poor query design

Understanding Deadlocks

A deadlock occurs when two or more sessions permanently wait for each other.

Example

Session A

Locks:

Customers

Needs:

Orders

Session B

Locks:

Orders

Needs:

Customers

Neither session can continue.

SQL Server automatically detects the deadlock.

One transaction becomes the deadlock victim.

Its transaction is rolled back.

The other transaction continues.


Deadlock Example

Transaction A

BEGIN TRANSACTION;
UPDATE Customers
SET CreditLimit = 1000
WHERE CustomerID = 1;
UPDATE Orders
SET Status = 'Approved'
WHERE OrderID = 100;
COMMIT;

Transaction B

BEGIN TRANSACTION;
UPDATE Orders
SET Status = 'Pending'
WHERE OrderID = 100;
UPDATE Customers
SET CreditLimit = 900
WHERE CustomerID = 1;
COMMIT;

If both transactions execute simultaneously:

  • Transaction A locks Customers
  • Transaction B locks Orders
  • Each waits for the other’s lock

SQL Server detects the cycle and terminates one transaction.


Blocking vs. Deadlocks

BlockingDeadlock
Temporary waitingCircular waiting
Usually resolves automaticallyRequires SQL Server intervention
No transaction rollbackOne transaction rolled back
Normal behaviorUndesirable behavior
Caused by incompatible locksCaused by cyclic lock dependencies

Transaction Isolation Levels

Isolation levels determine how transactions interact.

They directly affect:

  • Blocking
  • Concurrency
  • Consistency
  • Performance

READ UNCOMMITTED

Lowest isolation.

Allows dirty reads.

Almost no blocking.

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Advantages

  • Very fast

Disadvantages

  • Reads uncommitted data

READ COMMITTED (Default)

Most common.

Prevents dirty reads.

Allows non-repeatable reads.

Balanced performance and consistency.


REPEATABLE READ

Protects rows already read.

Increases locking.

More blocking.


SERIALIZABLE

Highest isolation.

Maximum consistency.

Most locking.

Greatest blocking potential.


SNAPSHOT Isolation

Uses row versioning.

Readers do not block writers.

Writers do not block readers.

Advantages:

  • High concurrency
  • Fewer blocking issues
  • Better scalability

Requires enabling snapshot isolation in the database.


Choosing the Appropriate Isolation Level

Isolation LevelDirty ReadsBlockingConcurrency
READ UNCOMMITTEDYesVery LowVery High
READ COMMITTEDNoModerateGood
REPEATABLE READNoHigherModerate
SERIALIZABLENoHighestLowest
SNAPSHOTNoLowExcellent

Detecting Blocking

Several tools can identify blocking.

Common methods include:

  • SQL Server Management Studio Activity Monitor
  • Dynamic Management Views (DMVs)
  • Extended Events
  • SQL Server Profiler (legacy)
  • Azure SQL monitoring tools
  • Microsoft Fabric monitoring experiences

One useful DMV query is:

SELECT
session_id,
blocking_session_id,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

This displays:

  • Waiting session
  • Blocking session
  • Wait type
  • Wait duration
  • Locked resource

Detecting Deadlocks

SQL Server automatically detects deadlocks.

Detection methods include:

  • Extended Events
  • System Health session
  • SQL Server Profiler (legacy)
  • Azure SQL Intelligent Insights
  • Deadlock graphs
  • SQL Server error logs (when configured)

Deadlock graphs visually display:

  • Victim process
  • Lock owners
  • Waiting processes
  • Resources involved

These graphs are invaluable for identifying the exact sequence of events that caused the deadlock.


Best Practices to Prevent Blocking and Deadlocks

Microsoft recommends several strategies to minimize concurrency issues:

  • Keep transactions as short as possible.
  • Commit or roll back transactions promptly.
  • Access tables in a consistent order across all applications.
  • Create appropriate indexes to reduce scan times.
  • Avoid user interaction while a transaction is open.
  • Use the lowest appropriate isolation level for the workload.
  • Consider Snapshot Isolation or Read Committed Snapshot Isolation (RCSI) for read-heavy environments.
  • Break large updates into smaller batches.
  • Regularly maintain indexes and statistics.
  • Monitor blocking trends and deadlock frequency proactively.

Real-World Troubleshooting Scenarios

Scenario 1: Long-Running Transaction

A reporting application begins a transaction and leaves it open while waiting for user input. Meanwhile, hundreds of users attempting to update the same data experience delays.

Resolution: Redesign the application so that user interaction occurs before the transaction begins or after it commits, minimizing the transaction’s duration.


Scenario 2: Deadlocks During Order Processing

Two stored procedures update the Customers and Orders tables but access them in different sequences.

Resolution: Standardize the order in which tables are accessed (for example, always update Customers before Orders) to eliminate the circular dependency that causes deadlocks.


Scenario 3: Blocking Caused by Table Scans

A frequently executed query scans millions of rows because no suitable index exists. The scan holds locks long enough to block other sessions.

Resolution: Create an appropriate nonclustered index and rewrite the query to be SARGable so that SQL Server can perform index seeks instead of table scans.


DP-800 Exam Tips

  • Understand the difference between blocking and deadlocks.
  • Know how transaction isolation levels affect concurrency and locking behavior.
  • Recognize that blocking is a normal mechanism to preserve consistency, whereas deadlocks are abnormal conditions that SQL Server resolves by selecting a victim transaction.
  • Be familiar with common lock types, including Shared, Exclusive, Update, Intent, and Schema locks.
  • Know that Snapshot Isolation and Read Committed Snapshot Isolation (RCSI) use row versioning to reduce reader-writer blocking.
  • Understand that long-running transactions, missing indexes, inconsistent object access order, and poor query design are common causes of blocking and deadlocks.
  • Be comfortable using DMVs and monitoring tools to identify blocking sessions before moving on to advanced analysis with execution plans and Query Store (covered in Part 2).

Go to the DP-800 Exam Prep Hub main page

Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Optimize database performance
      --> Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

One of the primary responsibilities of a SQL AI Developer is ensuring that database queries execute efficiently. Slow queries can increase response times, consume excessive CPU and memory, cause blocking, reduce scalability, and negatively affect AI-powered applications that rely on timely access to data.

The DP-800 exam expects candidates to know how to:

  • Analyze query execution plans
  • Identify inefficient query operators
  • Interpret estimated and actual execution plans
  • Use Dynamic Management Views (DMVs) to monitor performance
  • Use Query Store to identify and resolve performance regressions
  • Use Query Performance Insight in Azure SQL Database
  • Recommend performance improvements based on collected metrics

Why Query Performance Matters

Database performance directly affects application performance.

Poorly optimized queries can lead to:

  • Slow application response times
  • High CPU utilization
  • Excessive memory consumption
  • Long-running transactions
  • Locking and blocking
  • Deadlocks
  • Reduced scalability
  • Increased Azure SQL costs

For AI-enabled applications, inefficient queries can delay:

  • Retrieval-Augmented Generation (RAG)
  • Semantic searches
  • Vector searches
  • AI model inference
  • Data preparation pipelines

Performance tuning is therefore an essential database development skill.


SQL Server Query Processing

Before SQL Server executes a query, it performs several steps:

  1. Parse the T-SQL statement
  2. Validate syntax and object names
  3. Optimize the query
  4. Generate an execution plan
  5. Execute the plan

The Query Optimizer determines the most efficient execution strategy based on:

  • Statistics
  • Available indexes
  • Estimated row counts
  • Predicate selectivity
  • Join order
  • Available memory
  • Parallelism

What Is an Execution Plan?

An execution plan is a graphical or textual representation of how SQL Server executes a query.

It shows:

  • Operators
  • Join methods
  • Index usage
  • Estimated cost
  • Actual row counts
  • Warnings
  • Parallel operations

Execution plans are among the most valuable tools for diagnosing performance issues.


Estimated vs. Actual Execution Plans

SQL Server can generate two types of execution plans.

Estimated Execution Plan

Generated before execution.

Shows:

  • Estimated row counts
  • Estimated operator costs
  • Chosen indexes
  • Join methods

Does not execute the query.

In SQL Server Management Studio (SSMS):

Display Estimated Execution Plan (Ctrl + L)


Actual Execution Plan

Generated after query execution.

Shows:

  • Actual row counts
  • Actual execution statistics
  • Actual execution time
  • Memory usage
  • Runtime warnings
  • Actual operator behavior

Enable in SSMS:

Include Actual Execution Plan (Ctrl + M)

The DP-800 exam frequently tests the distinction between estimated and actual execution plans.


Understanding Execution Plan Operators

Execution plans contain operators representing individual processing steps.

Common operators include:

OperatorPurpose
Table ScanReads every row in a table
Clustered Index ScanScans an entire clustered index
Index SeekEfficiently locates matching rows
Key LookupRetrieves additional columns from a clustered index
Nested LoopsEfficient join for small result sets
Merge JoinEfficient for sorted data
Hash MatchEfficient for large unsorted datasets
SortOrders rows
Compute ScalarCalculates expressions
FilterApplies predicates

Index Seek vs. Index Scan

One of the most frequently tested concepts.

Index Seek

Efficient.

Reads only qualifying rows.

Example:

SELECT *
FROM Customers
WHERE CustomerID = 125;

If an index exists on CustomerID:

Execution Plan:

Index Seek

Index Scan

Reads many or all index pages.

Example:

SELECT *
FROM Customers
WHERE YEAR(OrderDate)=2025;

Because the function prevents index usage, SQL Server often performs an Index Scan.

A scan is not always bad. If a query retrieves most rows in a table, a scan may be the most efficient choice.


Table Scans

A table scan reads every row.

Usually indicates:

  • Missing indexes
  • Non-selective predicates
  • Small tables
  • Poor query design

Table scans on very large tables often signal optimization opportunities.


Join Operators

SQL Server selects join algorithms based on estimated costs.

Nested Loops

Best for:

  • Small inputs
  • Indexed lookups

Merge Join

Best for:

  • Large sorted datasets

Requires sorted input.


Hash Match

Best for:

  • Large unsorted datasets

Uses more memory but often performs well for analytical workloads.


Cost Percentage

Execution plans display estimated operator costs.

Example:

Hash Match
85%
Index Seek
10%
Sort
5%

Important exam point:

Cost percentages are optimizer estimates—not actual elapsed execution time.


Execution Plan Warnings

Execution plans may display warnings such as:

  • Missing indexes
  • Implicit conversions
  • Spills to tempdb
  • Missing statistics
  • Excessive memory grants

Warnings often identify the root cause of performance issues.


Missing Index Recommendations

Execution plans sometimes recommend indexes.

Example:

Missing Index (Impact 98%)

These recommendations can significantly improve performance but should be evaluated carefully rather than implemented automatically, because they don’t consider overall workload or maintenance costs.


Dynamic Management Views (DMVs)

DMVs expose real-time information about SQL Server’s internal state.

They are invaluable for monitoring:

  • Active requests
  • Query statistics
  • Index usage
  • Wait statistics
  • Sessions
  • Transactions
  • Memory usage
  • Cached execution plans

Common Performance DMVs

sys.dm_exec_query_stats

Provides cumulative statistics for cached query plans.

Useful columns include:

  • Total CPU time
  • Total logical reads
  • Total elapsed time
  • Execution count

Example:

SELECT TOP 10
total_worker_time,
execution_count
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

sys.dm_exec_sql_text()

Returns the SQL text associated with cached plans.

Often joined with:

sys.dm_exec_query_stats

sys.dm_exec_query_plan()

Returns XML execution plans.

Useful for automated analysis.


sys.dm_exec_requests

Shows currently executing requests.

Useful for identifying:

  • Blocking
  • Long-running queries
  • Wait types

sys.dm_exec_sessions

Shows active user sessions.

Useful for monitoring connected users.


sys.dm_os_wait_stats

Displays cumulative wait statistics.

Common waits include:

  • PAGEIOLATCH
  • CXPACKET
  • LCK_M_X
  • WRITELOG

Wait statistics often reveal the primary performance bottleneck.


sys.dm_db_index_usage_stats

Shows how indexes are used.

Helps identify:

  • Unused indexes
  • Frequently used indexes
  • Missing optimization opportunities

Query Store

Query Store is one of SQL Server’s most valuable performance features.

Introduced in SQL Server 2016.

It automatically captures:

  • Query text
  • Execution plans
  • Runtime statistics
  • Wait statistics
  • Plan history

Unlike DMVs, Query Store persists data across server restarts.


Benefits of Query Store

Query Store helps developers:

  • Identify slow queries
  • Detect regressions
  • Compare execution plans
  • Force known-good execution plans
  • Analyze historical performance
  • Monitor workload changes

It is widely used for production performance tuning.


Query Store Architecture

Query Store stores:

  • Query text
  • Multiple execution plans
  • Runtime statistics
  • Wait statistics
  • Historical performance

This historical information makes it much easier to diagnose intermittent issues.


Detecting Query Regressions

A query regression occurs when a query suddenly becomes slower.

Common causes include:

  • Updated statistics
  • New indexes
  • Parameter sniffing
  • Schema changes
  • Data growth

Query Store can compare previous and current execution plans to identify regressions.


Forcing Execution Plans

If SQL Server selects an inefficient plan, Query Store allows administrators to force a previously successful plan.

Benefits include:

  • Immediate performance stabilization
  • Reduced troubleshooting time

Forced plans should still be monitored because future schema or workload changes may make a different plan more appropriate.


Query Store Wait Statistics

Modern versions of SQL Server also capture wait statistics per query.

Examples include:

  • CPU waits
  • Lock waits
  • I/O waits
  • Memory waits

This makes troubleshooting significantly easier.


Query Performance Insight

Query Performance Insight is an Azure SQL Database performance monitoring feature available in the Azure portal.

It provides visual dashboards that display:

  • Top resource-consuming queries
  • CPU utilization
  • Duration
  • Execution count
  • Database workload trends
  • Historical performance

It simplifies performance analysis without requiring T-SQL queries.


Benefits of Query Performance Insight

Advantages include:

  • Visual performance analysis
  • Historical trends
  • Easy identification of expensive queries
  • Azure portal integration
  • Supports Azure SQL Database

It is especially useful for cloud database administrators.


Common Performance Problems

Missing Indexes

Symptoms:

  • Table scans
  • High logical reads

Solution:

Create appropriate indexes after evaluating workload impact.


Outdated Statistics

Symptoms:

  • Poor execution plans
  • Incorrect row estimates

Solution:

Update statistics.

UPDATE STATISTICS Sales;

Parameter Sniffing

Occurs when SQL Server caches an execution plan optimized for one parameter value that performs poorly for others.

Possible solutions include:

  • Query Store plan forcing
  • OPTION (RECOMPILE)
  • OPTIMIZE FOR
  • Query rewriting

Implicit Conversions

Example:

WHERE CustomerID='100'

if CustomerID is an integer.

Implicit conversions may prevent index seeks.

Use matching data types whenever possible.


Excessive Key Lookups

Frequent Key Lookup operators may indicate that a covering index would improve performance.


Best Practices

Use Actual Execution Plans

Actual plans reveal runtime behavior and often expose problems that estimated plans cannot.


Review Missing Index Recommendations Carefully

Evaluate:

  • Existing indexes
  • Maintenance overhead
  • Duplicate indexes

Do not automatically implement every recommendation.


Monitor Query Store Regularly

Review:

  • Regressions
  • Forced plans
  • Runtime statistics
  • Wait statistics

Monitor Wait Statistics

Focus on the largest waits rather than individual slow queries.

Wait analysis often identifies system-wide bottlenecks.


Update Statistics

Accurate statistics enable the optimizer to generate better execution plans.


Remove Unused Indexes

Too many indexes:

  • Increase storage
  • Slow inserts
  • Slow updates
  • Slow deletes

DMVs help identify unused indexes.


Keep Statistics Current

Automatic statistics are helpful but may not always update quickly enough for rapidly changing data.


Performance Tuning Workflow

A common performance tuning process is:

  1. Identify a slow query.
  2. Capture the actual execution plan.
  3. Review Query Store history.
  4. Check DMVs for CPU, I/O, and wait statistics.
  5. Identify inefficient operators.
  6. Evaluate indexing opportunities.
  7. Update statistics if needed.
  8. Test improvements.
  9. Monitor results.

DP-800 Exam Tips

Remember these key points for the exam:

  • Actual execution plans contain runtime statistics, while estimated execution plans do not execute the query.
  • An Index Seek is generally more efficient than an Index Scan when retrieving a small subset of rows.
  • DMVs provide real-time diagnostic information but generally reset when SQL Server restarts or the execution plan cache is cleared.
  • Query Store retains historical query performance information across restarts.
  • Query Store can detect query regressions and force a previous execution plan.
  • Query Performance Insight provides Azure portal dashboards for Azure SQL Database performance analysis.
  • Execution plan cost percentages are optimizer estimates, not measurements of actual elapsed time.
  • Missing index recommendations should be evaluated carefully rather than applied automatically.

Practice Exam Questions

Question 1

A database administrator wants to determine how SQL Server actually executed a query, including runtime row counts and operator statistics. Which tool should be used?

A. Actual Execution Plan
B. Estimated Execution Plan
C. Query Performance Insight
D. sys.dm_db_index_usage_stats

Correct Answer: A

Explanation: The Actual Execution Plan executes the query and records runtime information such as actual row counts, memory usage, and operator performance. Estimated plans only predict how the query will execute.


Question 2

A query retrieves a single customer by using a highly selective indexed column. Which execution plan operator would typically provide the best performance?

A. Table Scan
B. Clustered Index Scan
C. Index Seek
D. Hash Match

Correct Answer: C

Explanation: An Index Seek efficiently navigates directly to the qualifying rows within an index, minimizing I/O and improving performance for selective queries.


Question 3

Which Dynamic Management View (DMV) provides cumulative performance statistics for cached query plans?

A. sys.dm_exec_query_stats
B. sys.dm_exec_sessions
C. sys.dm_db_index_usage_stats
D. sys.dm_exec_requests

Correct Answer: A

Explanation: sys.dm_exec_query_stats stores cumulative statistics such as total worker time, logical reads, elapsed time, and execution count for cached query plans.


Question 4

A developer wants to analyze historical query performance and compare execution plans before and after a deployment. Which feature should be used?

A. Activity Monitor
B. SQL Server Profiler
C. Dynamic Management Views
D. Query Store

Correct Answer: D

Explanation: Query Store stores historical query text, execution plans, runtime statistics, and wait statistics, allowing developers to compare performance across deployments.


Question 5

Which statement about Query Store is true?

A. It only stores data until SQL Server restarts.
B. It automatically captures query history and execution plans.
C. It replaces execution plans entirely.
D. It only works with Azure SQL Database.

Correct Answer: B

Explanation: Query Store automatically captures query text, execution plans, runtime statistics, and historical performance data. Unlike many DMVs, its data persists across restarts.


Question 6

Which Azure SQL Database feature provides graphical dashboards that identify high-resource queries and workload trends?

A. Database Mail
B. Query Performance Insight
C. SQL Trace
D. Extended Events

Correct Answer: B

Explanation: Query Performance Insight provides Azure portal dashboards that visualize CPU usage, query duration, execution counts, and historical performance trends.


Question 7

An execution plan displays a warning indicating a “Missing Index (Impact 96%).” What is the best course of action?

A. Immediately create the recommended index without review.
B. Ignore the recommendation because SQL Server recommendations are unreliable.
C. Evaluate the recommendation alongside the overall workload before deciding whether to implement it.
D. Rebuild every existing index first.

Correct Answer: C

Explanation: Missing index recommendations are useful starting points, but developers should consider existing indexes, maintenance overhead, and workload characteristics before implementation.


Question 8

Which situation most commonly causes an Index Scan instead of an Index Seek?

A. Searching by a primary key value
B. Filtering with a function applied to an indexed column, such as YEAR(OrderDate)
C. Using an equality predicate on an indexed column
D. Retrieving a single row by a unique index

Correct Answer: B

Explanation: Applying functions to indexed columns often makes predicates non-SARGable, preventing efficient index seeks and causing SQL Server to scan the index instead.


Question 9

A developer wants to identify currently executing queries that are waiting on locks or consuming excessive resources. Which DMV is most appropriate?

A. sys.dm_exec_requests
B. sys.dm_exec_query_plan
C. sys.dm_db_index_usage_stats
D. sys.dm_os_wait_stats

Correct Answer: A

Explanation: sys.dm_exec_requests displays currently executing requests, including wait types, blocking information, CPU usage, elapsed time, and execution status.


Question 10

Why are database statistics important for query optimization?

A. They permanently eliminate table scans.
B. They encrypt execution plans.
C. They reduce transaction log size.
D. They help the Query Optimizer estimate row counts and choose efficient execution plans.

Correct Answer: D

Explanation: SQL Server relies on statistics to estimate data distribution and row counts. Accurate statistics allow the Query Optimizer to select efficient join methods, indexes, and execution strategies, resulting in better overall query performance.


Go to the DP-800 Exam Prep Hub main page

OBIEE Performance Tuning

This post describes a few tips and things to keep in mind for OBIEE Performance Tuning.

Be Proactive when possible
The need to performance tune can be proactive (tune before a major issue arises) or reactive (tune after a problem is reported by users for example).  It is best to be proactive – so performance tuning should be built into your OBIEE maintenance schedule. For example, OBIEE’s Usage Tracking functionality should be used regularly to identify reports whose performance can be improved and then performance steps should be carried out on the worst performers.

Iterative Process – change one thing or set of things at a time
One of the first things to keep in mind is that performance tuning is an iterative process.  And there is typically no one silver bullet that will resolve all your performance problems.  You may need to analyze and make changes to multiple parts of the system, but you want to make the changes methodically.  It is best to change one parameter or setting at the same time (or one related set of parameters).  Adjust and test the settings for that one parameter/setting (or set of parameters) before moving on to another.  If you change too much at one time, you may have a difficulty determining what is helping from what is hurting your efforts.

Fix user complaints first, worst performers next, and then the next bad performers down the list
Another thing to keep in mind, tune what users are reporting first, then tune the worst problems second, then move on to the next.

Team Effort – problem could be anywhere along the technology stack
Performance problems could be anywhere along the technology stack:
• OBIEE
• Database
• Server
• Network
Due to that span of technology, performance tuning is a team effort.  OBIEE Admins and Developers, DBAs, and ETL Developers can all be key to solving performance issues.
Logs from all components may need to be reviewed depending on the scenario.

Try to isolate or narrow-down the source of the problem
For example, run the report SQL directly on the database and see if you have the same problem. If there is no issue when run directly on that the database, then you have eliminated the database as the problem.
Determine if other applications have been also been experiencing slowness which could indicate the possibility of a network problem.

If your users have reported an issue, then you need to get as much details as possible about the performance problems they are experiencing.  When did this start happening?  Is it just one report or many?  Is it localized to one business area or multiple?  Is it all the time or sometimes?  Knowing this will help you to know where to focus.

Other questions to ask as you try to identify the source of the problem include but not limited to:
Has anything changed?  If reports were running fine, but are now slow, the first thing to ask is …
When the issue start?  Determining exactly when it started might be helpful when correlating with other system or company activity)
What has changed recently?  Has there been any system changes, data changes, database updates, network changes, etc. (even if they seem unrelated)?  For example, rolling into a new calendar year will cause new “Year” value(s) to be included in the data and can impact performance if statistics are not gathered.
Is there a possibility that an index was dropped and not recreated as expected?

Use OBIEE’s Usage Tracking information to analyze specific reports, analyze long running reports, or frequently run reports.  You will want to capture and analyze the SQL from these reports to determine what can be done to improve their performance.

Database
DBAs can monitor the system in real-time, use various tools, or review logs for information that can be helpful in the tuning effort.  Tools such as Oracle Enterprise Manager (EM) or SQL Tuning Advisor can be used to identify, analyze and tune high-load SQL.
OBIEE Usage Tracking can also be used to identify high-load SQL.
Without getting into much detail, these are some database features that could be used to help improve performance:
• Gather Statistics
• Results Cache database feature
• Partitioning

Servers
The System Admins can monitor the server resources to determine if there is an issue there.
• Use fast disk for the OBIEE cache and/or temporary files.

 

OBIEE-specific performance tuning tips

• OBIEE Caching
Are the tables being used set to cacheable?
Is caching turned on at the application level?
You may consider seeding the cache daily.
CACHE Settings:
o MAX_ROWS_PER_CACHE_ENTRY
o MAX_CACHE_ENTRY_SIZE
o MAX_CACHE_ENTRIES
o ——————-
o USE_ADVANCED_HIT_DETECTION

• Use Aggregation: Aggregate data when applicable
o You can use Aggregate tables or materialized views to realize this benefit.
o Aggregate Fact tables and corresponding Aggregate Dimensions.
o Make sure aggregation rules are applied to Fact table measures.
o Don’t necessarily merge all measures into a single fact.

• Joins and Indexes
o Do not create unnecessary joins.
o Verify that the joins on the tables being investigated are appropriate.
o Performance Indexing could be helpful.  Again, this is an iterative process.

• Prompts and Filters
o Use LOV tables to drive prompt values when possible, instead of building prompts from large transactional data tables.
o Force filter selection / entry by making prompt values required.  Do not allow open ended run of reports.

• Filter out unneeded data.  If there is a significant amount of data that is not being used in one or more tables (especially if they are frequently used), then that data should be filtered out by the ETL before it gets joined in SQL, and then has to be filtered out in the RPD or at the report level.

• Enter the “Number of Elements at this level” value in the logical level in hierarchies.
• Also ensure that all logical level keys are unique.

• Avoid function in the where clause when possible.

• Be careful of sub-queries.

• Check out the features of the OBIEE Performance Monitor
http://server:port/analytics/saw.dll?Perfmon  (enter your OBI server and port)

• When possible, do comparison analysis to determine for example, why is this report running fine, but this other seemingly similar report is not.

• Use fast disk for the OBIEE cache and/or temporary files.

Sometimes a complete overhaul might be required
Review the users’ workflow and determine if new and improved queries can be written or if the number of queries can be reduced.
Present information from a summary level first, and then provide increasing levels of details as requested by users through drill down or navigation.  Basically, present detailed information only when necessary, and minimize the amount of detail provided at a time by filtering on user selections.

Oracle’s OBIEE Performance Tuning Guide
Apply recommendations from the “Best Practices Guide for Infrastructure Tuning Oracle® Business Intelligence Enterprise Edition 11g Release”.  I would recommend applying 1 – 3 changes or set of changes at a time; don’t apply everything at the same time because if there is a problem, it will be more difficult to determine which change caused it.
https://blogs.oracle.com/proactivesupportEPM/entry/wp_obiee_tuning_guide