SQL Server 2016 database configuration details, sql2016

SQL Server 2016 database configuration details, sql2016

SQL Server 2016 is truly eye-catching. A few days ago, Microsoft provided the RCO (candidate release) version for download. I have already taken a closer look at the RCO version. One of the most annoying features is Database Scoped Configuration. I want to talk about it in today's article. After the installation, I found that there was no SSMS and the system crashed. It turned out that there was an independent installation program in the Wizard. Okay!

This color scheme has a low profile.

In addition, if your computer has already been installed, uninstall [Microsoft Visual Studio 2010 Shell (independent) Redistributable Package]. It seems that the installation of SSMS will be affected.

Start Screen.

Logon page.

Database Configuration

We know that SQL Server has many configuration options that can only be globally configured at the SQL Server instance level. For example, MAXDOP option (when you do not use Resource Controller ). In addition, Microsoft provides a large number of Trace Flags to modify the internal behaviors of SQL Server. Microsoft now finally announced that SQL Server 2016 will provide us with existing configuration options that can be configured at the database level without using specific Trace Flags ).

Let's take a closer look at them. With the Legacy Cardinality Estimation option, you can control whether SQL Server uses the new parameter estimates introduced in SQL Server 2014. This is a new configuration option, which can be previously implemented through the 2312 and 9481 trace tags.

The MAXDOP option does not need to be explained! Finally, you can control MAXDOP at the database level! This is really awkward! Especially for Sharepoint. Let's see if the later version of SharePoint will support MAXDOP at the database level... By the way, I am very much looking forward to the MAXDOP database at database level 1 to avoid the hateful CXPACKET wait.

Parameter Sniffing )! Microsoft calls it a special feature, and I call it a BUG. Generally, parameter profiling is a good thing, because the query optimizer compiles a query plan based on the input value you provided for the first time. This is great, but the disadvantage is that the generated query plan is sensitive to providing input parameters and only optimizes those parameters. If you later use different parameter values to reuse the cache query plan, it will affect your performance.

With this configuration option, you can now control whether the query optimizer uses parameter profiling. It can be used to replace the 4136 trace tag. If you disable parameter profiling, when you use the optimize for unknown query prompt, SQL Server uses the same behavior internally-it does not give you optimized performance, but it will give you consistent performance ......

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

Summary

I really like Database Configuration! In particular, MAXDOP options. For Sharepoint, it is a huge advantage. If we only use MAXDOP for a specific database running Sharepoint, the rest is much better than configuring MAXDOP for the instance.

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

Articles you may be interested in:

• Summary of key features of SQL Server 2016 CTP2.3
• Significant improvements in SQL Server 2016 TempDb
• SQL Server 2016 query storage performance optimization Summary
• The progress of TempDb in SQL Server 2016