What would be a good candidate for the very first blog post in 2019? The blog post I started writing in 2018 and did not finish. To be more precise I was planning & opened a draft with some notes for this blog post on the 10th of May 2018.
The blog post is about the huge number of changes and improvements that made into one of the most significant configuration feature of SQL Server & Azure SQL Database – the Database Scoped Configurations. I have already blogged about the first version of the Database Scoped Configurations for SQL Server 2016, with 4 visible options plus the procedure cache cleaning option, but we have followed in SQL Server 2017 with 5 (listed) & 9 (in practice – DISABLE_INTERLEAVED_EXECUTION_TVF, DISABLE_BATCH_MODE_ADAPTIVE_JOINS, BATCH_MODE_MEMORY_GRANT_FEEDBACK, BATCH_MODE_ADAPTIVE_JOINS are visible and functioning), and in just another year we have received a huge upgrade to the currently available 21 for SQL Server 2019.
Database Scoped Configurations in the year 2018
I have already blogged about ELEVATE_RESUMABLE & SET ELEVATE_ONLINE options in Resumable Online Index Creation for Azure SQL Database & SQL Server 2019, but all the other options are new and definitely need to be explored.
Let’s explore the new options and try to understand them, especially since Microsoft keeps publishing the details on them for almost 1 year:
– TSQL_SCALAR_UDF_INLINING – one of the most exciting features in SQL Server 2019 & Azure SQL Database that will push a lot of people to migrate to it is the option of doing an inlining User Defined Functions, a ground-breaking feature that I will be blogging about in the nearest future.
– OPTIMIZE_FOR_AD_HOC_WORKLOADS – after so many years the option to optimize for Ad-Hoc Workloads is available on the database level and while I have not seen in the past 8 years a server where enabling the Server-Side equivalent would not justify itself, I see the possibility of having this option on Azure SQL Database in the first place, and in those situations where execution plans need to be reusable right away from the very first execution can get a special treatment on the database level. Yahoo!
Joe Sack blogged about this option about a year ago at the SQL Server Storage Engine blog.
XTP_PROCEDURE_EXECUTION_STATISTICS & XTP_QUERY_EXECUTION_STATISTICS – Super-Exciting option for all the Hekaton (aka In-Memory) users, where we finally can enable collection of the statistics and execution plans with on a database level as described in the original blog post by Jos de Bruine. Really love the option that avoids the usage of set showplan_xml on; on every statement.
ROW_MODE_MEMORY_GRANT_FEEDBACK – a moral equivalent to the Batch Mode Memory Grant Feedback, for some of the details consult Columnstore Indexes – part 93 (“Batch Mode Adaptive Memory Grant Feedback”)
ISOLATE_SECURITY_POLICY_CARDINALITY – unknown and undocumented. Sounds very interesting though and I expect the year 2019 to reveal this feature.
BATCH_MODE_ON_ROWSTORE – described in Batch Mode – part 2 (“Batch Mode on Rowstore in Basics”), this option serves to enable or disable the Batch Execution Mode on the Rowstore Indexes.
DEFERRED_COMPILATION_TV – another member of the Intelligent Query Processing (QP) feature family, this feature serves for table variable deferred compilations and should be another huge item pushing for SQL Server 2019 migrations. (LOL to the TV part :)) Described in
Public Preview of Table Variable Deferred Compilation in Azure SQL Database already in July of this year, this will be a hugely important feature for all those solutions who grew incredibly while using Table Variables without realising the impact on bigger sets of data. Oh yeah, I know a lot of them …
ACCELERATED_PLAN_FORCING – unknown and undocumented. Sounds very interesting though and I expect the year 2019 to reveal this feature. It sound to me like a Query Store option … We shall see ;)
GLOBAL_TEMPORARY_TABLE_AUTO_DROP – available since over a year, Global temporary table that are scoped on the database level at Azure SQL Database can be adutotimacally dropped with this new option. I understand s that there an internal request from one of the Microsoft Applications using Azure SQL Database to have a possibility to drop database scoped temporary tables.
LIGHTWEIGHT_QUERY_PROFILING – this will be a huge item! Since SQL Server 2019 we shall have the Lightweight Query Profiling switched on automatically on the server level, allowing us to attach and observe live query execution by default, without any additional trace flags (TF 7412 for SQL Server 2016 & 2017 was the default way to get running). With this option (NOTE: currently for SQL Server 2019 CTP 2.2 non-functioning) we shall be able to avoid spending resources on the databases where it is not needed and in Azure SQL Database it will allow us to make a choice however we want. I am really happy about this upcoming option.
Mambo Nr 5
If you look with a lot of attention in the sequence of the configuration_ids, you will notice that the configuration_id number 5 is missing. It is being like this for way too long and I think all of us would love to find out about the destiny of this item. Otherwise I do not want to start just another conspiracy theory … :)
Between Other Things
Given how powerful & hopefully popular the Database Scoped Configurations became, I am truly interested in a possibility of a couple of database settings becoming more visible and prominent because of their importance, even though in some cases they are already visible & accessible in the SSMS:
– TARGET RECOVERY TIME (see https://docs.microsoft.com/en-us/sql/relational-databases/logs/change-the-target-recovery-time-of-a-database-sql-server?view=sql-server-2017) the option for the database recovery time. Even though it is quite visible in the SQL Server Management Studio right now, right near the PAGE VERIFY option, I would love it to gain a more visible space, especially since most people migrating to SQL Server 2016 and later versions do not pay any attention to it, potentially loosing the possibility of the indirect checkpoints.
– Mixed Page allocation, known traditionally as a Trace Flag 1118, and being a database option since SQL Server 2016. It is now possible to enable the mixed page allocation on the database level with the following command for the imaginary database [MyDb]: ALTER DATABASE [MyDb] SET MIXED_PAGE_ALLOCATION ON;
SSMS
Looking at the SQL Server Management Studio settings in the latest available release of the SQL Server Management Studio, which is 18 beta 6 by the time I am writing this article, one can see an extremely limited set of options, which does not include even the ones from the SQL Server 2017. Given how awesome and how important they are becoming I think the missing items is a HUGE miss by the SSMS team.
To be honest right now I would even give a separate tab/page at the database option for the Database Scoped Configurations so that they become more visible for the final users (yes, a lot of shops simply have developers instead of the DBAs and even a lot of DBAs have no idea about Database Scoped Configurations). This would allow easier and more efficient management of the options. I also guess that there are less people using Extended Properties or Change Tracking (ha!) as the Database Scoped Configurations and these number will grow hugely apart in the bigger favor of Database Scoped Configurations over the next years.
Final Thoughts
I truly love the Database Scoped Configurations but it is time for them to start maturing in the terms of visibility, accessibility and documentation (lacking very much of it).
I am looking forward to the next years to discover more options and get the benefits of them for the clients I am helping.