Database Scoped Configurations got even better with SQL Server 2016

Working with SQL Server, one has to learn the terms of various configurations and have to understand how these configurations can be done. More important to those configuration mayhem is the need to understand what level of scope these configurations have effect. A typical example in one of the recent customer interaction, they were doing some sort of server consolidation for their application databases which were hosted on SQL Server. The problem statement was some of these applications were legacy but had specific server configurations like MAXDOP = 1 being set. If you literally ask me, there is no rationale at the moment with the DBA team to why this was done, but it is the case. In some of the new generation applications, they were completely written keeping parallelism in mind. Now if you ask the DBA, they are confused in bringing them to the same server because now if they change the MAXDOP, this is going to affect all the databases that are getting hosted on this single server. For administrators, now there is a sigh of relief because SQL Server 2016 brings a number of these settings to database level configuration. If you are new to this, these options can be accessed from the database properties. Here is a snapshot of what can be achieved by the same: Though these are available as UI level, there is a TSQL construct to the same and can be automated in your scripts if that is the requirement.

-- Database Configuration with SQL Server 2016 for:
-- Turning off the Cardinality estimation on Secondary in an AlwaysOn deployment.
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
SET LEGACY_CARDINALITY_ESTIMATION=OFF;
GO
-- Database Configuration with SQL Server 2016 for:
-- to remove the procedure cache for a single database
-- and not the complete cache on the server.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

In the above scenario , I was talking about how we can now play around with the MAXDOP option at a database level. If that is the case, I simulated a call wherein two queries from two DB’s of the exact copy behaved differently. One used parallelism and other didn’t. I used the plan compare feature of SQL Server 2016 to see the effect.

As you can see, this is a powerful tool to use and can be quite useful to DBA’s and developers if they are going to troubleshoot performance problems. Do let me know if you will be using this feature. Do let me know via comments on your views.