SQL Server – Database Scoped Configurations

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

Leave a Reply

Please log in using one of these methods to post your comment:

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