Shulou(Shulou.com)05/31 Report--

This article shows you what the configuration of SQL Server 2016 database is like, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

SQL Server 2016 really caught people's eyes. Microsoft offered a download of the RCO (candidate release) version a few days ago. I've been watching the RCO version around, and one of the most awesome features is database-wide configuration (Database Scoped Configuration). Add a few words: after installing, unexpectedly found that there is no SSMS, in the crash, it is in the wizard there is an independent installation program, all right!

This color matching is really low-key and connotative.

In addition, if your computer has been installed [Microsoft Visual Studio 2010 Shell (stand-alone) Redistributable Package], uninstall it, it seems to affect the installation of SSMS.

Launch screen.

Login Page.

Database-wide configuration

We know that SQL Server has many configuration options that can only be configured globally at the SQL Server instance level. For example, the MAXDOP option (when you are not using the resource governor). In addition, Microsoft provides us with a large number of different tracking tags (Trace Flags) to modify the internal behavior of SQL Server. Now that Microsoft has finally announced these areas, SQL Server 2016 will provide us with existing configuration options that can be configured at the database level instead of using specific trace tags (Trace Flags).

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

There is no need to explain the MAXDOP option! Finally you can control MAXDOP at the database level! This is really awesome! Especially for Sharepoint. I'll see if future versions of SharePoint will support database-level MAXDOP.... By the way, I've been looking forward to database level 1 of MAXDOP's database, to avoid the abominable CXPACKET wait.

Parameter probe (Parameter Sniffing)! Microsoft calls it feature, I call it BUG, and parameter probing 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 generated query plan is sensitive to providing input parameters and optimizes only those parameters. If you later reuse the cached query plan with different parameter values, it will affect your performance.

With this configuration option alone, you can now control whether the query optimizer uses parameter probing. It can be used to replace the 4136 trace tag. If you disable parameter probing, when you use OPTIMIZE FOR UNKNOWN query prompts, SQL Server uses the same behavior internally-it won't give you optimized performance, but it will give you consistent performance.

Finally, you have a query optimization patch (Query Optimizer Fixes configuration option). This configuration option enables or disables specific query patches instead of trace tag 4199.

The above is what the database-wide configuration of SQL Server 2016 is like. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.