Free Microsoft Azure DP-300 Actual Exam Questions - Question 11 Discussion
HOTSPOT You have an Azure SQL database. You are reviewing a slow performing query as shown in the following exhibit. 
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. 
I’m with those saying the scan points to a missing index on OrderDate alone. Adding other columns might help, but the biggest gain is from eliminating that scan first. Also, the sort looks like a Top N Sort since it’s just taking the first few results after ordering. If we fix the scan, the sort will become cheaper too. So picking B for the missing index and A for the sort type makes sense here.
The query’s slow because it’s doing a scan, so the right missing index is probably on OrderDate alone (B). The sort operation is likely a Top N Sort (A) since it’s limiting results. That fits the plan shape.
I think the key here is that the query is scanning a large number of rows because it's filtering on OrderDate, but there's no efficient seek possible. The plan shows a scan, so some index must be missing or inadequate. Adding an index on OrderDate should definitely help, but I wonder if including other columns in the index, like CustomerID or OrderID, might speed up the query further by covering it entirely. Also, maybe statistics are outdated, which can make the optimizer choose a scan over a seek. So updating stats could be another step besides just adding an index.
That scan screams missing index on OrderDate, nothing else fits better.
Index fragmentation might be causing the scan, so adding a filtered index could help.
Looks like the query’s missing an index on OrderDate which is causing the scan, so I'd pick options pointing to that inefficiency.