Continuing the tradition that I started with Sql Server 2016 and the original appearance of the Database Scoped Configurations (an incredible feature, I have to repeat) and that last year precisely in January I have blogged about the Database Scoped Configurations in 2018.
Growing every couple of months the DMV sys.database_scoped_configurations is allowing us to configure the most important details of our database functioning. Focusing naturally almost exclusively on the newest features, these options have brought enormous joy and facility in solving so many things to me & my clients so far. Disabling hurting feature or enabling something that is disabled by default on the database level.
Database Scoped Configurations in the year 2019
Looking at the picture on the left you can see the Database Scoped Configurations available in Sql Server 2019 that I took at the end of December 2019. Mainly we can see a difference of 2 new items in the configurations since the end of the last year – the VERBOSE_TRUNCATION_WARNINGS & LAST_QUERY_PLAN_STATS.
VERBOSE_TRUNCATION_WARNINGS – announced by the end of 2018, this configuration controls the feature
String or Binary data would be truncated: replacing the infamous error 8152, that was and in older version still prevents people from seeing which column and which value caused.
Every single ETL developer lost their temper with this error with all the power & energy. :) As announced in March of 2019 this is a by default enabled feature and if some process depends on the old error 8152 – they can disable it on the database level with this option.
One of the most exciting thing is that this feature has been backported to SQL Server 2017 CU12 and in SQL Server 2016 SP2 CU6, allowing older version of the product to take advantage of it with the help of the Trace Flag 460.
LAST_QUERY_PLAN_STATS – this option controls the returns the equivalent of the last known actual execution plan for a previously cached query plan. Pointing to the DMV sys.dm_exec_query_plan_stats where the column [xml] CAN contains the last known runtime Showplan representation. This system function works under the lightweight query execution statistics profiling infrastructure and thus promises to have a very low impact on the system. I am wondering how useful it will be on the real production system where sometimes the actual execution plan change a number of times per day (and sometimes a couple of times per hour), but this is a right step in the right direction and I really hope that we might be able to get more then just one last plan in the future.
Interesting is that there is still no public documentation on the ISOLATE_SECURITY_POLICY_CARDINALITY configuration.
What I also find interesting is that only the following options are disabled by default:
OPTIMIZE_FOR_AD_HOC_WORKLOADS, XTP_PROCEDURE_EXECUTION_STATISTICS, XTP_QUERY_EXECUTION_STATISTICS, ISOLATE_SECURITY_POLICY_CARDINALITY, LAST_QUERY_PLAN_STATS. While I am agreeing with the most options (especially with the one that I still do not understand), OPTIMIZE_FOR_AD_HOC_WORKLOADS seems that should have been set to ON.
Database Scoped Configurations in the year 2019 on Azure SQL Database
This little research would not be complete if would not check on the Azure SQL Database and after creating a new database you can see the output on the left side of this text. While it seems that they are the same as for the Sql Server (the total number of the options is 23), in the reality there is one missing and one option is totally new.
TSQL_SCALAR_UDF_INLINING – missing the UDF Inlining, which apparently looks to have some difficulties of appearing on Azure SQL Database. There must be still some cases that need to be fixed, before allowing millions of users to take advantage of it. Naturally it is a pity, but knowing that building a good enough service is an incredibly difficult task, all we can do is reserve the patience.
PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES – this new option allows to abort a Paused Resumable Index operation (Creation or Rebuild). With a default of 1 day it allows the granularity of 1 minute and the maximum duration of over 49 days. Setting this option on 0 will disable the abort operation and by default on Azure this is not what wanted – think impact on the data files. I like the idea of this option, though the default of just 1 day used currently is not my cup of tea, I would start with a week – for those who do a weekly maintenance.
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?
SSMS
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. Sooner Azure Data Studio will have a plugin for it.
Final Thoughts
Pretty impressive growth over the last 4 years.
Not many things have changed since the last year, but it was the year of Sql Server 2019 RTM and finishing it off was definitely the highest priority. I do not have any particular option right on the top of my head that I wish to have as a Database Scoped Configuration. I do not believe we need to have multiple ways of disabling/enabling the configurations. :)