DATABASE SCOPED CONFIGURATION – SQL Server 2016

Earlier versions of SQL server does not support database scope configuration level, Options were applicable for the entire instance.

for example if we configure MAXdop 2, This will applicable for entire instance. Thanks Microsoft SQL Server Team for new feature Database scoped configuration.

SQL Server 2016 now supports database level configurations that affect  at the database level.  Now you can set variety of following configurations at database level with business needs and demands.
  1. Clear procedure cache.
  2. Set the MAXDOP parameter to an arbitrary value (1,2, ...) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (e.g. for reporting queries).
  3. Set the query optimizer cardinality estimation model independent of the database to compatibility level.
  4. Enable or disable parameter sniffing at the database level.(previous versions of SQL Server we could disable this feature using trace flag 4136)
  5. Enable or disable query optimization hotfixes at the database level.
configurations can be set from SSMS or TSQL.

On SSMS, Select the database you want to configure. Right click ans select options.

under options there is section called Database Scoped configuration.

Database Scope Configuration
Database Scope Configuration







Syntax :
  • ALTER DATABASE SCOPED CONFIGURATION
    {      
         {  [ FOR SECONDARY] SET <set_options>  }  
    }
    | CLEAR PROCEDURE_CACHE
    [;]  
    
    < set_options > ::=  
    {
        MAXDOP = { <value> | PRIMARY}  
        | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}  
        | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}  
        | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}  
    }
Enable Legacy Cardinality Estimation
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
Disable Parameter Sniffing
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
Enable Query Optimizer Fixes
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
Set MAXDOP Value
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s