When I look at the Query Store reports for Top Resource Consuming Queries, there is one query that skews the report so much that I can barely see the next query because this one is so high.

Highest one: Total logical reads: 12956022792 execution count: 2458 plan count: 6

The next highest one: Total logical reads: 76670280 execution count: 106 plan count: 1

What are some recommendations for improving this query?

Here is the plan: https://www.brentozar.com/pastetheplan/?id=B1AlsOMn-

1 Answer 1

  • Remove the OPTION(FORCE ORDER). This greatly limits the optimizer's freedom to find a good plan.
  • Modify the Index Seek indexes to include the columns referenced in the following Key Lookup operators (Output List and Predicate)
  • Capture actual (post-execution) plans for representative executions.
  • Consider adding OPTION (RECOMPILE) if different executions require different plan shapes to run well, or OPTIMIZE FOR hints for the main cases.

If nothing else, attempt to capture a post-execution plan for a problematic case, as the basis for your tuning efforts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.