Query STore and autotuning

• abdalah.mehdoini

  Ten Centuries

  Points: 1023

  More actions

  April 15, 2025 at 4:32 am

  #4579106

  Hello Everyone ,

  I have an instance of SQL Server Manager on Azure with autotuning enabled, including the Last Good Plan = ON option. However, I feel like SQL Server is not properly applying this feature. When reviewing the Query Store on my instance, I noticed multiple different execution plans, despite the Force Plan option being enabled. Do you have any idea about the cause of this situation and possible solutions?

  <!--more-->Thanks

• Grant Fritchey

  SSC Guru

  Points: 399020

  More actions

  April 15, 2025 at 2:24 pm

  #4579355

  You'll notice that none of those plans have a check mark, meaning, they're forced. So, what's likely is that the machine learning algorithm doesn't see a significant difference in execution based on the measurements it has. It won't automatically force any old query into a behavior. It takes quite a bit of execution counts and measurements. There's a minimum number of executions needed as well. Query sys.dm_db_tuning_recommendations to get an idea of possible suggestions. It probably won't implement all of what you see there. Hope that helps a little.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning