Exam DP-800 Topic 1 Question 86 Discussion
Actual exam question for Microsoft's DP-800 exam
Question #: 86
Topic #: 1
Question #: 86
Topic #: 1
You have an Azure SQL database that supports a customer-facing API. The API calls a stored procedure named dbo.GetCustomerOrders thousands of times per hour.
After a deployment that updated indexes and statistics, users report that the API endpoint backed by dbo.GetCustomerOrders is slower. In Query Store, the same query now has two persisted execution plans. During the last hour, the newer plan had a significantly higher average duration and CPU time than the older plan.
You need to restore the previous performance quickly, without changing the API code.
Which Transact-SQL command should you run?
After a deployment that updated indexes and statistics, users report that the API endpoint backed by dbo.GetCustomerOrders is slower. In Query Store, the same query now has two persisted execution plans. During the last hour, the newer plan had a significantly higher average duration and CPU time than the older plan.
You need to restore the previous performance quickly, without changing the API code.
Which Transact-SQL command should you run?
Suggested Answer: C Vote an answer
We have encountered plan regression. This often happens after maintenance (like updating statistics) because the query optimizer generates a new execution plan that it thinks is better based on the new data distribution, but it ends up being less efficient in practice.
Since you've already identified the Plan ID for the faster plan and the Query ID from Query Store, you can force the "good" plan immediately using:
EXEC sp_query_store_force_plan @query_id = [YourQueryID], @plan_id = [YourFastPlanID]; Use code with caution.
This tells Azure SQL to ignore the new, slower plan and stick to the one that worked, providing an almost instant fix for your API's performance without requiring a code deployment.
Reference:
https://daxsws.com/blog/real-world-dynamics-365-performance-tuning-scenarios-and-fixes
Since you've already identified the Plan ID for the faster plan and the Query ID from Query Store, you can force the "good" plan immediately using:
EXEC sp_query_store_force_plan @query_id = [YourQueryID], @plan_id = [YourFastPlanID]; Use code with caution.
This tells Azure SQL to ignore the new, slower plan and stick to the one that worked, providing an almost instant fix for your API's performance without requiring a code deployment.
Reference:
https://daxsws.com/blog/real-world-dynamics-365-performance-tuning-scenarios-and-fixes
by Hedy at Sep 02, 2026, 10:20 AM
0
0
0
10
Comments
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Report Comment
Commenting
You can sign-up / login (it's free).