March 23, 2023 at 10:06 am
I'm changing the DB Compatibility mode to 2022 in SQL Managed Instance.
below features are missing in SQL MI and were present only in Onpremise (from sys.database_scoped_configurations table)
DOP_FEEDBACK
MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT
OPTIMIZED_PLAN_FORCING
I couldnt find any MS documentation about these features being not available in Azure, Can anyone please provide more info why they are missing or how to enable DOP feedback.
March 23, 2023 at 11:33 am
In fact, the Microsoft documentation says just the opposite: "All IQP features are available in Azure SQL Managed Instance and Azure SQL Database." You can read about it here.
So, what else is going on? Have you disabled, or not enabled, query store for the database(s) in question? That's a big part of a lot of the IQP functionality. DOP feedback is enabled/disabled through database scoped configuration. You can read about that here. That applies to several of the IQP functions.
"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
March 24, 2023 at 5:22 pm
Thanks Grant.
Yes, Querystore was enabled.
I have gone through the link you shared, it's actually listed as not supported for SQL MI and SQL Database.
For some reason, Microsoft doesn't allow these features in Azure.
DOP_FEEDBACK, MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT, OPTIMIZED_PLAN_FORCING
March 24, 2023 at 6:13 pm
I'm sorry. I guess I completely misread that. I tracked down the DOP_FEEDBACK page, and yeah, it's not in Azure. Interesting.
Looking into it some more, the basic batch & row mode memory grant feedback is available in Azure, but percentile and persistence mode feedback are not.
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply