Database Scoped Configuration

Mirek Sztajno introduces us to new database-scoped configurations in SQL Server 2016:

  • Enable or disable PARAMETER_SNIFFING at the database level. Disable this option to instruct the query optimizer to use statistical data instead of the initial values for all local variables and parameters when the query is compiled and optimized. This is equivalent toTrace Flag 4136 or the OPTIMIZE FOR UNKNOWN query hint

  • Enable or disable QUERY_OPTIMIZER_HOTFIXES at the database level, to take advantage of the latest query optimizer hotfixes, regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199

  • CLEAR PROCEDURE_CACHE which allows to clear procedure cache at the database level without impacting other databases and without requiring sysadmin permission. This command can be executed using ALTER ANY DATABASE SCOPE CONFIGURATION permission on the database, and the operation can be executed on the primary and/or the secondary

This is an early implementation of functionality, but I think this is a step in the right direction.  Getting finer-grained and database-level configuration settings gets us one step closer to that 2012 dream of containerized databases.

Related Posts

Checking Spark Config on Windows

Ed Elliott has a Powershell script to tell you if your Spark configuration on Windows is incorrect: There are some pretty common mistakes people make (myself included!), most common I have seen recently have been having a semi-colon in JAVA_HOME/SPARK_HOME/HADOOP_HOME or having HADOOP_HOME not point to a directory with a bin folder which contains winutils. […]

Read More

Configuring dbatools

Claudio Silva shares some tips on configuring dbatools: Set a new configuration valueTo update a value you need to use the Set-DbatoolsConfig command. Unfortunately, you will not find documentation for this command on our docs page. This is a known issue and it happens because that command is a cmdlet so the help is in the dbatools library […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031