SQL Server 2016 database-scoped configuration _mssql

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

This color, really low-key have connotation.

Also, if your computer has been installed on the "Microsoft Visual Studio Shell (standalone) redistributable Package", uninstall it as if it will affect the SSMS installation.

Start the screen.

Login interface.

Database-wide Configuration

We know that SQL Server has many configuration options that can be configured globally only at the SQL Server instance level. such as the MAXDOP option (when you do not use the resource Governor). In addition, Microsoft provides us with a large number of different tracking flags (trace flags) that can modify the internal behavior of SQL Server. Microsoft is now finally announcing these areas, and SQL Server 2016 will provide us with existing configuration options that can be configured at the database level without the use of specific tracking flags (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 old or new parameter estimates introduced in SQL Server 2014. This is a new configuration option that was previously available through 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 a drag! Especially for SharePoint. Let me see if later versions of SharePoint will support database-level MAXDOP ... Yes, I'm already looking at database level 1 MAXDOP database to avoid the hateful cxpacket wait.

Parameter probing (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 provided for the first time. This is great, but the disadvantage is that the resulting query plan is sensitive to providing input parameters and only optimizes those parameters. If you later reuse a cached query plan with a different parameter value, your performance will be affected.

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, the same behavior is used internally by SQL Server when you use the optimize for unknown query prompt-it does not give you optimized performance but will give you consistent performance ...

Finally you have query optimization patch (Optimizer fixes configuration option). This configuration option enables or disables some specific query patches, which are used in place of trace flag 4199.

Summary

I really like the database-wide configuration! Especially the MAXDOP option. It's a huge advantage for SharePoint, and if we only use 1 MAXDOP for a particular database running SharePoint, everything else 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.