Being a futurist, I like new features and SQL Server 2016 and Azure SQL Database have one that’s really caught my eye!
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
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
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.
You can also clear the procedure cache of plans that only originated from a specific database:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
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!