SQL Server's New Database Configuration Options
SQL Server 2016 Service Pack One release has made some important changes to database configuration, including using one alter command that can boost performance.
Join the DZone community and get the full member experience.Join For Free
It’s amazing just how much the landscape changed with the release of SQL Server 2016 SP1. For example, I just found out that you can disable parameter sniffing at the database level using the database configuration. Not only does this work for SQL Server 2016 SP1, but it’s enabled for Azure SQL Database.
How Database Configuration Works
The syntax is very simple and documented here. So, if I want to disable parameter sniffing for a single database, I can do this:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
That’s it. Done. It works from within the database and doesn’t require rebooting or anything else. Changing this setting does flush the cache of all the execution plans for that database. No other actions are necessary. You can control parameter sniffing at the database level.
Finally, turning it back on, just modify the code above to set it to ON.
But Wait… Now How Much Would You Pay
There’s more. The database configuration supports a bunch of other database specific settings that are pretty interesting. You can now set the MAXDOP at the database level. That’s without any need for setting up Resource Governor. You can modify the cardinality estimation engine independently from the compatibility level. In addition, you can also control the implementation of the query optimizer hot fixes, again at the database level.
All this makes for pretty exciting modifications at the database level to affect how your queries behave. However, Database Configuration creates additional things you’re going to want to check when you’re seeing differences in behavior between databases running on different servers. For example, you may see differences in execution plans between two servers which because different database settings for the cardinality estimator exist.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.