Hi Friends,
As a DBA, we know about various Instance level settings in SQL Server like MAXDOP, Cardinality Estimator related changes and various Trace Flags etc. There may be some scenarios where you want to enable these kind of settings at the database level rather than instance level. For Example, I want to set MAXDOP to 1 only for a specific database rather than all the databases. Another example, the New cardinality estimator is working fine for all of the databases, but not working well for a specific database.
In SQL Server 2016 Microsoft has provided some of the features under the scope of the database. That means you can change these settings at database level as per the requirement rather than changing these settings at instance level or using trace flags.
These database scoped settings are :
- Legacy Cardinality Estimation: By default, this setting is off. if you want to use legacy cardinality estimator then you can enable it for the database by changing this from off to on. If you want to know about the new cardinality estimator then you can click here.
- MAXDOP: This is a very good known setting in SQL Server world. In SQL Server 2016, you can change the setting of MAXDOP for a specific database. Yes, you read it correct. By default the value of MAXDOP is 0. You can change this value as per the application requirement.
- Parameter Sniffing: Generally, we use this term to explain the problem during performance troubleshooting when people come to us and say our stored procedure is running fine for some parameters but for others it is taking too much time. By default, this setting is on i.e. optimizer will use a histogram to create the execution plan based on initially passed parameters. If you will change this setting to off, then the optimizer will behave similar like using trace flag 4136. If you don’t know about trace flag 4136 then you can click here.
- Query Optimizer Fixes: By default, this setting is off. This feature generally used to enable and disable the behavior of trace flag 4199. You can click here to know about trace flag 4199.
* for secondary: if you are using the setup of always on then you can change all above specified setting for the secondary databases using these settings.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
