August 31, 2021 at 9:33 am
Hello ,
Is the activation of the Automatic tuning function on an Azure SQL manager instance equivalent to the recompile operation
If I activate it SQL will calculate the execution plan for all the ad-hoc queries suddenly will generate more consumption on the CPU side
Is it recommended to activate this function on the Azure side?
thank you
September 1, 2021 at 10:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 1, 2021 at 12:59 pm
Hello ,
Is the activation of the Automatic tuning function on an Azure SQL manager instance equivalent to the recompile operation
Not even a little bit. A recompile is simply the process of removing a plan from cache and then going through the compilation process again to create a new plan. Automatic tuning within Azure involves quite a bit of background work on the Azure platform. It gathers metrics on your query behaviors and the various plans being generated. It identifies a likely "best" plan and then will use plan forcing from Query Store to put the best plan in place. Further, depending on what you've enabled, you can see the creation, and/or drop, of nonclustered indexes in an automated fashion. Again, completely different in every possible way from a plan recompile.
If I activate it SQL will calculate the execution plan for all the ad-hoc queries suddenly will generate more consumption on the CPU side
You may see more CPU use, sometimes. In my testing, it hasn't been radical, but it is possible. Once the recommended plan is forced though, no, no additional CPU use occurs.
Is it recommended to activate this function on the Azure side?
So, I would absolutely recommend that people test these features in their environment and then evaluate the results. However, no, I wouldn't recommend that you simply turn them on by default.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply