Background

Earlier touched on reviewing database compatibility level via sys.databases’ compatibility_level column.

That post is here.

Database Scoped Configuration

In v2016, there is also a new component known as “Database Scoped Configuration” that can be investigated and tuned to review and compare database performance.

Areas

Areas currently exposed includes:

1. Clearing procedure cache
   • Legacy
     • DBCC FREEPROCCACHE
       • Works against entire SQL Instance or specific plans
2. MAXDOP parameter for specific databases ( OLTP )
   • Legacy
     • SQL Instance
       • sp_configure ‘max degree of parallelism’
     • Query Hint
       • OPTION ( MAXDOP 1)
3. Set the query optimizer cardinality estimation model independent of the database compatibility level
   • Legacy :- v2008 thru 2012
   • Current :- v2014
4. Enable or disable parameter sniffing at the database level
   • Legacy
     • Trace Flag 4136
       • SQL Server 2008 R2 CU2, SQL Server 2008 SP1 CU7 and SQL Server 2005 SP3 CU9 introduce trace flag 4136 to disable the “parameter sniffing” process
         Link
5. Enable or disable query optimization hotfixes at the database level
   • Legacy
     • Trace Flag
       • SQL Server query optimizer hotfix trace flag 4199 servicing model
         Link
6. Enable or disable the identity cache at the database level.
   • Legacy
     • Trace Flag
       • Trace Flag 272 disables batching of identity values

Read

To read Database Scoped configuration for the current database, please issue

use [dbname]
go

select 
        [database] = db_name()
      , tblSDSC.*

from   sys.database_scoped_configurations tblSDSC

Set LEGACY_CARDINALITY_ESTIMATION

Syntax

Syntax

use [database]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  

Sample

use [hrdb]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  

References

1. Docs / SQL / T-SQL / Statements
   • ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
     Link
2. Docs / SQL / Relational databases / System catalog views /
   • sys.database_scoped_configurations (Transact-SQL)
     Link