As the continuation of the tradition, started 5 years ago, each year I will be continuing tracing the changes taking place in Database Scoped Configurations.
Database Scoped Configurations in the year 2020
We did not have any new major product releases for the good old SQL Server 2019 in the 2020, and without significant Service Packs or totally reshaping Cumulative Updates (there are no SPs as Microsoft announced since SQL Server 2017), we have 0 changes for the SQL Server since the end of the year 2019. We have the same properties that were described in the blog post Database Scoped Configurations in 2019 – there were 23 for the SQL Server and there is still the same number.
I think that the number will bump up only after next major release, which means we are about to wait for at least 1 year or given that these blog posts are coming each year, maybe it will even mean that the new options will be blogged about in a couple of years.
Database Scoped Configurations in the year 2019 on Azure SQL Database
Instead of 23 options last year we have got 26 this year, with a significant changes of removal of the TSQL_SCALAR_UDF_INLINING, which actually never were available in the first place and an addition of 4 new options, where one of them is Azure Synapse Analytics specific (yeah, it was the time for that!).
So what are the 4 new options that have appeared in 2020 ?
- PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES – this is a cool option which allows us to define the amount of time that Resumable Index operation will stay paused before breaking the suspension. This option is configurable on the minute-basis, with default being set to 1 day (1440 minutes and the maximum number of minutes is 71582 minutes (almost 50 days), with another option of setting it to be equal to 0 (zero) meaning that a paused operation will never automatically abort.
In the mean time, I have to ask again, when does Columnstore Indexes resumable index operation will appear ? - EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS – not documented yet, but sounds like a knob to control the Query Statistics Execution for the Scalar User Functions … A companion for UDF Inlining, I assume …
- ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY – a very cool feature for the asynchronous statistics update, this function (which IS in a preview right now), will invoke a background request to wait on a low priority queue until a SCH-M (Schema Modification) lock is acquired to improve the execution of simultaneous requests – thus avoiding to block the parallel sessions.
I have personally yet to apply it, but most probably I just need to experiment with it. - DW_COMPATIBILITY_LEVEL – this is the very first knob that is known to me that is dedicated to Azure Synapse Analytics and that allows to manipulate Query Processor improvements, just like the generally available, good old, data base compatibility level.
Right now there are just 2 options, besides the AUTO (which is the default and will pick up the latest option):
10 – the original compatibility level before the introduction of this query.
20 – the 1st new compatibility level that includes some new and apparently undocumented T-SQL and Query Processor behaviours.
This looks pretty interesting and I will be hunting for the new information.
Option Number 5
Still missing from the list is the option with an ID of 5. Shall it ever see the light of the day?
I do not think so :)
SSMS & Azure Data Studio
Sql Server Management Studio still stuck in the year 2016 with 4 old options. I still believe we need a proper tab for the Database Scoped Configurations, but I do not expect it to happen. Since Azure Data Studio is primarily focused on the Developers (I have to say that there are expcetions with Big Data Clusters and Azure Arc enabled Data Services thought), there is no support for the database scoped configurations – not on premises nor in the cloud (Azure SQL DB/Azure SQL Managed Instance/Synapse Analytics).
Final Thoughts
It is genuinely interesting and exciting to see how a year without a major new release of the box product (SQL Server) brings the difference in the Database Scoped Configurations available on Azure and the lack of the new options for the box releases. This makes the mission of fast releases even more clear and reasonable to understand, the fast release cycle allows Microsoft to introduce & test new options rather fast instead of waiting for years and then finding out that they need to correct/adjust them for the general market.
Year after year we see the steady growth of the new Database Scoped Configurations and the past 2020 was no exception – following the growth made in 2019 with the very same 4 new options introduced. I am happy to see more knobs, because no 2 environments and even for the same application are exactly the same and almost every environment will need some different adjustment.
Btw, in the sequence the option number 28 is missing, but hopefully it won’t have the destiny of the Mambo option number 5!