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:
- Clearing procedure cache
- Legacy
- DBCC FREEPROCCACHE
- Works against entire SQL Instance or specific plans
- DBCC FREEPROCCACHE
- Legacy
- MAXDOP parameter for specific databases ( OLTP )
- Legacy
- SQL Instance
- sp_configure ‘max degree of parallelism’
- Query Hint
- OPTION ( MAXDOP 1)
- SQL Instance
- Legacy
- Set the query optimizer cardinality estimation model independent of the database compatibility level
- Legacy :- v2008 thru 2012
- Current :- v2014
- 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
- 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
- Trace Flag 4136
- Legacy
- Enable or disable query optimization hotfixes at the database level
- Legacy
- Trace Flag
- SQL Server query optimizer hotfix trace flag 4199 servicing model
Link
- SQL Server query optimizer hotfix trace flag 4199 servicing model
- Trace Flag
- Legacy
- Enable or disable the identity cache at the database level.
- Legacy
- Trace Flag
- Trace Flag 272 disables batching of identity values
- Trace Flag
- Legacy
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