-1

We are currently proactively correcting performance issues with a specific SQL Server by clearing the procedure cache nightly.

In the near future, we are intending to consolidate many databases onto a more powerful production SQL Server computer, and as such want to avoid clearing the procedure cache nightly.

What steps can we take to mitigate the plan issue without negatively impacting the consolidated server?

We have scheduled performance scripts on the weekend, however that’s not the solution either.

Improve this question
4
  • 3
    Start by identifying the query that gets a bad plan. Then paste its execution plan XML here for analysis. Including query statement and table definitions are useful too. You likely have a parameter sniffing issue, bad statistics or poor indexing. These can be fixed, but good solution requires knowledge on what really goes on on the system.
    – vonPryz
    Apr 21, 2022 at 7:43
  • 1
    And in the worst case, you can still remove plans from the cache at a more granular level, either per database or for a specific individual plan itself. But this is usually not the right solution to fix performance issues, and rather is a bandaid workaround. You should identify and address the root issues of your execution plans such as vonPryz mentions.
    – J.D.
    Apr 21, 2022 at 11:35
  • 3
    Duplicate
    – mustaccio
    Apr 21, 2022 at 11:43
  • Does this answer your question? Why is my query suddenly slower than it was yesterday?
    – Anthony Genovese
    Apr 25, 2022 at 15:17

1 Answer 1

Reset to default
1

To clear the procedure cache for a single database use this:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

But this is only a workaround. On the new server you should use the Query Store to monitor for bad plans, and either remediate them with changes to the queries, stats, or indexes, or use the Query Store to force good plans.

Improve this answer

Your Answer

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

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