SQL 2022 Automatic Tuning, visual impact using S1

So we run a number of SQL 2022 servers that are all monitored using S1.  This morning we had Query Store being disabled by a deployment, which also disables Automatic Plan Choice Correction.

See this article for more on it: MS Books Online article

We got alerted due to high CPU on this server, subsequent investigation turned out that QS was disabled.  Reenabling all of this gave us a good visual of the impact of Query Store and Automatic Plan Choice Correction:

Pretty dramatic.

Query to see if enabled:

select * from sys.database_automatic_tuning_options

Not all systems will have this dramatic difference, but in the 2 servers we have it turned on, it has proved to help us out a lot.

Anders

Parents
  • That's an awesome improvement!  We recently had a P1 that this would have caught and corrected before anybody noticed a problem.  Unfortunately I somehow had equated the auto plan correction setting with the auto-index tuning in Azure, so I've been skipping this setting in my environment.  Time to revisit this one.  

  • This DB was upgraded from 2014 to 2022.

    Ran it in 2014 compatibility level for 2 weeks, with Query Store on
    Changed it to 2022 compatibility level, manually forced a few query plans.  Ran it this way for a week
    Unforced all plans and turned this on.  

    We have not had it make a bad decision yet.  

Reply
  • This DB was upgraded from 2014 to 2022.

    Ran it in 2014 compatibility level for 2 weeks, with Query Store on
    Changed it to 2022 compatibility level, manually forced a few query plans.  Ran it this way for a week
    Unforced all plans and turned this on.  

    We have not had it make a bad decision yet.  

Children
No Data