Slow database actions - moving in consolidated server



  • 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.



  • To clear the procedure cache for a single database use https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15#f-clear-procedure-cache :

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    

    But this is only a workaround. On the new server you should use the https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 to monitor for bad plans, and either remediate them with changes to the queries, stats, or indexes, or use the Query Store to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-ver15 good plans.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2