Overlooked
In my quest to tell you about new stuff in SQL Server 2022, I forgot to take a look at new things in sys.database_scoped_configurations.
It turns out that’s important, because some stuff has to be turned on if you want it to work. Here’s a list of new database scoped configurations for SQL Server 2022:
+------------------+-----------------------------------------------+-------+------------------+ | configuration_id | name | value | is_value_default | +------------------+-----------------------------------------------+-------+------------------+ | 29 | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY | 0 | 1 | | 31 | CE_FEEDBACK | 1 | 1 | | 37 | DOP_FEEDBACK | 1 | 0 | | 26 | DW_COMPATIBILITY_LEVEL | 0 | 1 | | 27 | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS | 1 | 1 | | 34 | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT | 1 | 1 | | 33 | MEMORY_GRANT_FEEDBACK_PERSISTENCE | 1 | 1 | | 35 | OPTIMIZED_PLAN_FORCING | 1 | 0 | | 28 | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION | 1 | 1 | | 25 | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES | 1440 | 1 | +------------------+-----------------------------------------------+-------+------------------+
Some stuff is on by default, some stuff isn’t. You might notice DOP_FEEDBACK and OPTIMIZED_PLAN_FORCING are both off by default, here (even though I turned them on).
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON; ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
Invitation Only
You might think that Microsoft is so excited about new features (especially in CTP builds) that they want them to be easy to preview and test by people who care enough to do that stuff.
No so much, it turns out.
Heck, I’ve seen several presentations that said Query Store would be on by default in new databases created on SQL Server 2022 instances, but I haven’t had that happen yet and that’s not even a new feature.
Perhaps, like the daftly-hidden Azure Nonsense© in the installer, this will be changed in a future version, but no one tells me anything.
Anyway, not much here beyond this. Mind your database scoped database configurations when you’re testing out new features. Also make sure to check for extended events that help you track down if/when heuristics for those features kick in.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.