Being a futurist, I like new features and SQL Server 2016 and Azure SQL Database have one that’s really caught my eye!

ALTER DATABASE SCOPED CONFIGURATION

This command lets you set what used to be server level configuration settings on a per-database level.  It  also lets you set different values for the primary and secondary replicas in an Availability Group.

ALTER DATABASE SCOPED CONFIGURATION

MAXDOP = { <value> | PRIMARY}

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}

PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}

Four settings that used to sp_configure, database options or trace flag settings can be now be enabled on a per-database basis and set using T-SQL.

More information is available here about what each of the settings do, but they should be relatively self-explanatory: https://msdn.microsoft.com/en-gb/library/mt629158.aspx

Different settings for primary and secondary replicas

Also with this new command, you can set different configuration values for the primary and secondary replicas in an Availability Groups deployment.  This lets you automatically tune the primary replica for transactional workloads and the secondary replicas for analytics queries.

In the example below, you’d set an primary Availability Group replica to have a MaxDOP setting of 4 and its secondary replicas to have MaxDOP values of 32:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=32

Parallel queries in Azure SQL Database

Now that the Premium tier of Azure SQL Database supports parallel queries, the same commands can be used to change the MaxDOP of queries that run there too as it doesn’t support the traditional sp_configure method.  This is useful for forcing highly concurrent transactional queries to execute with serial (MaxDOP=1) plans.

Clearing the plan cache

You can also clear the procedure cache of plans that only originated from a specific database:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Querying the a database’s configuration state

To see the current configuration of a database’s database scope settings, you can use the DMV

sys.database_scoped_configurations

Hopefully of interest to some!