DOP_FEEDBACK in SQL MI

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

  • 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

  • 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

    • This reply was modified 1 year, 3 months ago by  cooldude001.
  • 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