SQL Server 2016 will be really impressive. Since a few days ago Microsoft has made the RC0 version available for download. I have already done a quick tour through the RC0 version, and one of the nicest new features is the Database Scoped Configuration which I want to cover in today’s blog post.

Database Scoped Configuration

We all know that SQL Server has a lot of configuration options that can be only enabled globally at the SQL Server Instance level. Just think about the MAXDOP option (when you don’t use Resource Governor). Also Microsoft provided us with a huge number of different Trace Flags that change the internal behavior of SQL Server. Microsoft has now finally addressed these areas and SQL Server 2016 provides us with a lot of existing configuration options that we can configure at the database level without using any specific Trace Flags.

Let’s have a more detailed look at them. With the Legacy Cardinality Estimation option you can control whether SQL Server uses the old or the new Cardinality Estimator that was introduced back in SQL Server 2014. It’s just a new configuration option that was previously controlled through the Trace Flags 2312 and 9481.

The option MAXDOP doesn’t need any explanation! Finally you are able to control the MAXDOP at the database level! This is really awesome! Especially for Sharepoint. Let’s see if a future version of Sharepoint will support the MAXDOP at the database level… Oh, and I’m already expecting to see a lot of databases with a MAXDOP of 1 at the database level to get rid of those nasty CXPACKET waits…

The Parameter Sniffing option! Microsoft calls it a feature, I call it a bug. In general Parameter Sniffing is a good thing, because the Query Optimizer compiles a query plan for you based on the initially provided input values. That’s great, but the drawback is that the generated query plan is sensitive to the input parameters that were provided, and is only optimized for those provided input parameters. If you later reuse the cached query plan with different input parameter values it can be the case that your performance will suffer.

With this configuration option you can now control whether the Query Optimizer uses Parameter Sniffing or not. It’s a replacement for the Trace Flag 4136. If you disable Parameter Sniffing, SQL Server internally uses the same behavior as when you use the Query Hint OPTIMIZE FOR UNKNOWN – it will not give you a optimal performance, but will give you consistent performance…

And finally you have the Query Optimizer Fixes configuration option. This configuration option enables and disables some specific Query Optimizer Fixes. It’s a replacement for Trace Flag 4199.

Summary

I *really* love the Database Scoped Configuration options! Especially the MAXDOP option. It would be a huge advantage for Sharepoint, if we could just run the Sharepoint specific databases with a MAXDOP of 1, and everything else with a better configured instance DOP.

What do you think about these new configuration options? Do you love them? Please feel free to leave a comment.

Thanks for your time,

-Klaus