Database-scoped configuration for SQL Server 2016

SQL Server 2016 really makes a person's eyes shine. A few days ago Microsoft provided a download of the RCO (candidate release version). I've been watching a lap RCo version, one of the most drag features is database-scoped configuration (db Scoped configuration), in today's article I want to talk about it. Add a few words: After installed, incredibly found no ssms, crash, the original is in the wizard has a separate installation program, OK!

This color, is really low-key connotation.

Also, if your computer is already installed on the "Microsoft Visual Studio (standalone) redistributable package", uninstall it as if it would affect the installation of SSMS.

Start screen.

Login screen.

Database-scoped Configuration

We know that SQL Server has many configuration options that can be configured globally only at the SQL Server instance level. For example, the MAXDOP option (when you are not using resource Governor). In addition Microsoft provides us with a large number of different trace flags (trace flag) that can modify the internal behavior of SQL Server. Microsoft is now finally announcing these areas, and SQL Server 2016 provides us with the existing configuration options that can be configured at the database level instead of using a specific trace flag (trace flags).

Let's take a look at them in detail. Using the early parameter estimation (Legacy cardinality estimation) option, you can control whether SQL Server uses the old or new parameter estimates introduced in SQL Server 2014. This is a new configuration option that was previously possible with 2312 and 9481 trace flags.

The MAXDOP option doesn't have to be explained! Finally you can control maxdop! at the database level This is really too drag! Especially for SharePoint. I'll see if later versions of SharePoint will support database-level MAXDOP ... Yes, I've been looking at database level 1 MAXDOP database to avoid nasty cxpacket waiting.

parametric sniffing (Parameter sniffing)! Microsoft calls it a feature, and I call it a bug, which is usually a good thing because the query optimizer compiles the query plan based on the input values you provide for the first time. This is great, but the disadvantage is that the resulting query plan is sensitive to providing input parameters and is optimized only for those parameters. If you later reuse the cached query plan with different parameter values, it will affect your performance.

With this configuration option only, you can now control whether the query optimizer uses parameter sniffing. It can be used to replace 4136 trace flags. If you disable parameter sniffing, when you use the Optimize for unknown query hint, SQL server uses the same behavior internally-it does not give you optimized performance, but gives you consistent performance ...

Finally you have the query optimizer patch (query Optimizer Fixes configuration option). This configuration option enables or disables some specific query patches, which are used instead of trace flag 4199.

Summary

I really like the database-scoped configuration! Especially the MAXDOP option. It's a huge advantage for SharePoint, and if we only use 1 of MAXDOP for a particular database running SharePoint, the rest will be much better than the MAXDOP of the configuration instance.

What do you think of these new configuration options? Do you like them? Please leave a message.

Thanks for your attention!

Reference article:

https://www.sqlpassion.at/archive/2016/03/14/database-scoped-configuration-in-sql-server-2016/

Database-scoped configuration for SQL Server 2016