Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Server's New Database Configuration Options

DZone's Guide to

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.

· Database Zone ·
Free Resource

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

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.

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

Topics:
database ,database performance ,parameter sniffing ,database configuration ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}