DP-800: Developing AI-Enabled Database Solutions

Question

You have an Azure SQL database with Query Store enabled. Query Performance Insight identifies one stored procedure as having the longest runtime. The procedure executes the following parameterized query.

Question Image

The dbo.Orders table contains approximately 120 million rows. CustomerId is highly selective, while OrderDate is used for range filtering and sorting.

You have these indexes:

Clustered index: PK_Orders on (OrderId)
Nonclustered index: IX_Orders_OrderDate on (OrderDate) with no included columns

An actual execution plan from Query Store for slow executions shows the following:

An index seek on IX_Orders_OrderDate, followed by a Key Lookup (Clustered) on PK_Orders for CustomerId, Status, and TotalAmount
A Sort operator before Top (50) because the results are ordered by OrderDate DESC

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Yes or No

Statements	Yes	No
To avoid the explicit sort for the query, create a nonclustered index on (CustomerId, OrderDate DESC) that includes (Status, TotalAmount).
To eliminate the sort and make the query use an ordered seek, add CustomerId as an included column to IX_Orders_OrderDate.
The plan indicates a bottleneck from a suboptimal query plan, rather locking or blocking.

Explanation

A (CustomerId, OrderDate DESC) nonclustered index supports the selective customer seek and returns rows in the required date order. Including Status and TotalAmount makes the index cover those output columns, eliminating the clustered key lookup. An included column is non-key data and does not establish index ordering or provide a seek key, so adding CustomerId as an included column cannot produce the required ordered seek. The Sort and Key Lookup show a query-plan/index-design bottleneck, not locking or blocking.

Learn more

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ28

Community Discussion