Before SQL Server 2016, configuration referred to instance-wide settings. But with Azure SQL Database not giving us access to any instance-level information or giving us any instance-level control, we only can see information and control settings for a specific database. So now, a new SQL Server 2016 feature allows us to have a bit more control of individual databases. And once a feature gets tested and verified in Azure, it’s ported to the SQL Server on-premises code. We now have ALTER DATABASE SCOPED CONFIGURATION which does not take a database name and always only applies to the current database.
Some of the settings available with this command were not configurable from any kind of normal command or procedure prior to the addition of this new command, and some were undocumented. For example, clearing the plan cache was a DBCC command. Changing the cardinality estimation model and opting to use optimizer hotfixes were trace flags.
So far, there are only six options available, but I’m going to guess this number will increase in the next version, if not the next service pack.
The option I’ve used the most often is
1 | ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE |
Note that the name is misleading, as is the name DBCC FREEPPROCCACHE. Ever since SQL Server 2005, plans are cached for other kinds of queries in addition to the plans for stored procedures. These commands, both the old DBCC command and the new ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE actually clear all plans from cache, not just procedure plans. I have no idea why Microsoft didn’t use what would have been a much more accurate name: CLEAR PLAN_CACHE.
Here is the full syntax for the new command, showing all six options. Note that four of the options can be set on a SECONDARY, but CLEAR PROCEDURE_CACHE and SET IDENTITY_CACHE cannot be.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | ALTER DATABASE SCOPED CONFIGURATION { { [ FOR SECONDARY] SET <set_options> } } | CLEAR PROCEDURE_CACHE | SET IDENTITY_CACHE = { ON | OFF } [;] < set_options > ::= { MAXDOP = { <value> | PRIMARY} | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY} | PARAMETER_SNIFFING = { ON | OFF | PRIMARY} | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY} } |
The command is fully documented here: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql
Any guesses what new DATABASE SCOPED CONFIGURATION options will be added next? Which options would you like to see made DATABASE SCOPED?
