Mike Kiev - Fotolia


Configure SQL Server at database level with new scoping syntax

SQL Server 2016 adds ALTER DATABASE SCOPED CONFIGURATION to its new T-SQL statements. The statement lets DBAs control database configuration at the level of a single database.

SQL Server 2016 introduces an exciting new feature called database scoped configuration that can improve and simplify efforts to configure SQL Server. Using the ALTER DATABASE SCOPED CONFIGURATION T-SQL statement, a database administrator can now control various database configuration settings at the level of the individual database. The statement is currently supported in both SQL Server 2016 and Azure SQL Database V12. It's especially useful for Azure SQL Database, since, previously, some of the setting options couldn't be configured at the database level at all in the cloud platform, according to a Microsoft blog post about the new feature.

In earlier versions of SQL Server, database administrators could only enable configuration settings like MAXDOP, CLEAR PROCEDURE CACHE, PARAMETER SNIFFING and LEGACY CARDINALITY ESTIMATION at the SQL Server instance level using relevant trace flags. However, starting with SQL Server 2016, you can configure them directly through T-SQL scripts.

ALTER DATABASE SCOPED CONFIGURATION allows you to set different configuration options separately for each database on the same instance of SQL Server based on application requirements. It also lets you do the same thing for primary databases and secondary replicas set up with SQL Server's AlwaysOn Availability Groups disaster recovery software, depending upon their intended workloads. In addition, you can control which database users or groups of users are able to set these configuration options on a SQL Server instance, via the ALTER ANY DATABASE SCOPE CONFIGURATION permission.

Database scoped configuration options in SQL Server 2016

Let's look in more detail at some of the options for configuring SQL Server at the database level that are available in SQL Server 2016 and Azure SQL Database V12, starting with MAXDOP. You can use database scoped configuration to set the MAXDOP parameter to a number value to control the maximum degree of parallelism for processing database queries running against a specific database. The Microsoft blog post said this lets you avoid using SP_CONFIGURE at the server level, which can be done in SQL Server, but isn't supported in Azure SQL Database.

If you're using AlwaysOn Availability Groups, you can establish different MAXDOP values for the primary online transaction processing database and a secondary database, which can be used to run reports. Here's a sample script for accomplishing that:

Figure one: Sample T-SQL script to set MAXDOP values for primary and secondary databases.

CLEAR PROCEDURE CACHE clears the procedure cache of a specific user database without affecting other databases residing on the same instance of SQL Server. Here's how to use it to clear the procedure cache of a primary database:

Clear procedure cache
Figure two: Sample T-SQL script to clear the procedure cache of a primary database.

Cardinality estimation predicts the number of rows that will likely be returned by database queries, for use in optimizing queries. LEGACY_CARDINALITY_ESTIMATION is a syntax that lets you configure SQL Server 2016 to use the cardinality estimator from SQL Server 2012 and earlier database versions instead of a newer one introduced in SQL Server 2014. The legacy estimation option, which could help avoid query performance slowdowns in some cases, can also be accomplished by using a trace flag. But here's how to do it in a more straightforward way via T-SQL code:

Figure three: Sample T-SQL scripts to enable LEGACY_ CARDINALITY_ESTIMATION on primary and secondary databases.

PARAMETER_SNIFFING enables or disables the use of input parameters and variable values when a query is compiled and optimized by the database engine. If you decide to disable the sniffing option completely, the SQL Server query optimizer will instead use statistical data to do the optimization. You previously could achieve the same outcome by using trace flag 4136 or the OPTIMIZE FOR UNKNOWN query hint.

Disable parameter sniffing
Figure four: Disable parameter sniffing at the database level.

QUERY_OPTIMIZER_HOTFIXES lets you enable or disable the use of query optimizer hotfixes at the database level. Query optimizer hotfixes are packaged bug fixes released by Microsoft to deal with problems related to the query optimizer. Since Microsoft made changes to the query optimizer between SQL Server 2012 and SQL Server 2014, it's important that outdated bug fixes do not interfere with the current running of the query optimizer. QUERY_OPTIMIZER_HOTFIXES allows you to decide database by database whether you want to take advantage of the latest optimizer-related hotfixes regardless of a database's compatibility level. Hotfixes typically are turned off by default when they're delivered -- you previously had to enable or disable them by using trace flag 4199. Here's how that can be done now:

Figure five: Sample T_SQL script to enable QUERY_OPTIMIZER_HOTFIXES at the database level.

SQL Server 2016 also introduces a new dynamic management view (DMV), sys.database_scoped_configurations, which allows you to view the current database scoped configurations for a database to aid in monitoring and managing its performance. Using the DMV, you can also retrieve the configured values for primary and secondary replicas if the database is part of an AlwaysOn Availability Group. To access it, use these commands:

Use sys.database_scoped_configurations
Figure six: Sample T-SQL script to use sys.database_scoped_configurations.

Next Steps

Find the weak links in query performance with T-SQL

SQL Server 2016 simplifies tempdb configuration

Test your knowledge of SQL Server 2016

Dig Deeper on SQL-Transact SQL (T-SQL)