Skip to content

alter-database-scoped-configuration-transact-sql.md

Latest commit

 

History

History
287 lines (189 loc) · 15.5 KB

alter-database-scoped-configuration-transact-sql.md

File metadata and controls

287 lines (189 loc) · 15.5 KB
title ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) | Microsoft Docs
ms.custom
SQL2016_New_Updated
ms.date 2016-11-03
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
f1_keywords
ALTER_DATABASE_SCOPED_CONFIGURATION
ALTER_DATABASE_SCOPED_CONFIGURATION_TSQL
DATABASE_SCOPED_CONFIGURATION_TSQL
SCOPED_CONFIGURATION_TSQL
SCOPED_TSQL
ALTER_DATABASE_SCOPED_TSQL
DATABASE_SCOPED_TSQL
helpviewer_keywords
ALTER DATABASE SCOPED CONFIGURATION statement
configuration [SQL Server], ALTER DATABASE SCOPED CONFIGURATION statement
ms.assetid 63373c2f-9a0b-431b-b9d2-6fa35641571a
caps.latest.revision 32
author CarlRabeler
ms.author carlrab
manager jhubbard

ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx_md]

This statement enables several database configuration settings at the individual database level. This statement is available in both [!INCLUDEsqldbesa] and in [!INCLUDEssSQL15]. Those settings are:

  • Clear procedure cache.

  • Set the MAXDOP parameter to an arbitrary value (1,2, ...) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (e.g. for reporting queries).

  • Set the query optimizer cardinality estimation model independent of the database to compatibility level.

  • Enable or disable parameter sniffing at the database level.

  • Enable or disable query optimization hotfixes at the database level.

Topic link icon Transact-SQL Syntax Conventions

Syntax

  
ALTER DATABASE SCOPED CONFIGURATION  
{        
     {  [ FOR SECONDARY] SET <set_options>  }    
}  
| CLEAR PROCEDURE_CACHE  
[;]    
  
\< set_options > ::=    
{  
    MAXDOP = { <value> | PRIMARY}    
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}    
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}    
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}    
}

Arguments

FOR SECONDARY
Specifies the settings for secondary databases (all secondary databases must have the identical values).

MAXDOP = {<value> | PRIMARY }
<value>

Specifies the default MAXDOP setting that should be used for statements. 0 is the default value and indicates that the server configuration will be used instead. The MAXDOP at the database scope overrides (unless it is set to 0) the ‘max degree of parallelism’ set at the server level by sp_configure. Query hints can still override the DB scoped MAXDOP in order to tune specific queries that need different setting. All these settings are limited by the MAXDOP set for the Workload Group.

You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collectiuon, and static and keyset-driven cursor population.

To set this option at the instance level, see Configure the max degree of parallelism Server Configuration Option. To accomplish this at the query level, add the QUERYTRACEON query hint

PRIMARY

Can only be set for the secondaries and indicates that the configuration will be the one set for the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481. To set this at the instance level, see Trace Flags (Transact-SQL). To accomplish this at the query level, add the QUERYTRACEON query hint.

ON

Sets the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version of the cardinality estimation model.

OFF

Sets the query optimizer cardinality estimation model based on the compatibility
level of the database.

PRIMARY

This value is only valid on secondaries and specifies that the query optimizer cardinality estimation model setting on all secondaries will be the value set for the primary. If the configuration on the primary for the query optimizer cardinality estimation model changes, the value on the secondaries will change accordingly. PRIMARY is the default setting for the secondaries.

PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
Enables or disables parameter sniffing. This is equivalent to Trace Flag 4136. To set this at the instance level, see Trace Flags (Transact-SQL). To set this at the query level, see the OPTIMIZE FOR UNKNOWN query hint.

ON

Enables parameter sniffing. The default is ON.

OFF

Disables parameter sniffing.

PRIMARY

This value is only valid on secondaries and specifies that the value for this setting on all secondaries will be the vale set for the primary. If the configuration for the primary changes, the value on the secondaries will change accordingly. This is the default setting for the secondaries.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }
Enables or disables query optimization hotfixes regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199.
To set this at the instance level, see Trace Flags (Transact-SQL). To accomplish this at the query level, add the QUERYTRACEON query hint.

ON

Enables the use of the latest hotfixes query optimization hotfixes regardless of the compatibility level of the database.

OFF

Disables the use of the latest hotfixes query optimization hotfixes regardless of the compatibility level of the database. The default is OFF.

PRIMARY

This value is only valid on secondaries and specifies that the value for this setting on all secondaries will be the vale set for the primary. If the configuration for the primary changes, the value on the secondaries will change accordingly. This is the default setting for the secondaries.

CLEAR PROCEDURE_CACHE
Clears the procedure cache for the database. This can be executed both on the primary and the secondaries.

Permissions

Requires ALTER ANY DATABASE SCOPE CONFIGURATION
on the database. This permission can be granted by a user with CONTROL permission on a database

General Remarks

While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.

Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.

For 3-part name queries, the settings for the current database connection for the query will be honored, other than for SQL modules (e.g. procedures, functions, and triggers) that are compiled in the current database context and therefore will use the options of the database in which they reside.

The ALTER_DATABASE_SCOPED_CONFIGURATION event is added as a DDL event that can be used to fire a DDL trigger. This is a child of the ALTER_DATABASE_EVENTS trigger group.

Limitations and Restrictions

MAXDOP

The granular settings can override the global ones and that resource governor can cap all other MAXDOP settings. The logic for MAXDOP setting is the following:

  • Query hint overrides both the sp_configure and the database scoped setting. If the resource group MAXDOP is set for the workload group:

    • If the query hint is set to 0 it is overridden by the resource governor setting.

    • If the query hint is not 0, it is capped by the resource governor setting.

  • The DB scoped setting (unless it’s 0) overrides the sp_configure setting unless there is a query hint and is capped by the resource governor setting.

  • The sp_configure setting is overridden by the resource governor setting.

QUERY_OPTIMIZER_HOTFIXES

When QUERYTRACEON hint is used to enable the legacy query optimizer or query optimizer hotfixes, it would be an OR condition between the query hint and the database scoped configuration setting, meaning if either is enabled, the options will apply.

GeoDR

Readable secondary databases, e.g. Always On Availability Groups and GeoReplication, use the secondary value by checking the state of the database. Even though we don’t recompile on failover and technically the new primary has queries that are using the secondary settings, the idea is that the setting between primary and secondary will only vary when the workload is different and therefore the cached queries are using the optimal settings, whereas new queries will pick the new settings which are appropriate for them.

DacFx

Since ALTER DATABASE SCOPED CONFIGURATION is a new feature in Azure SQL Database and SQL Server 2016 that affects the database schema, exports of the schema (with or without data) will not be able to be imported into an older version of SQL Server e.g. SQL Server 2012 or SQL Server 2014. For example, an export to a DACPAC or a BACPAC from an Azure SQL Database or SQL Server 2016 database that used this new feature would not be able to be imported into a down-level server.

Metadata

The sys.database_scoped_configurations (Transact-SQL) system view provides information about scoped configurations within a database. Database-scoped configuration options only show up in sys.database_scoped_configurations as they are overrides to server-wide default settings. The sys.configurations (Transact-SQL) system view only shows server-wide settings.

Examples

These examples demonstrate the use of ALTER DATABASE SCOPED CONFIGURATION

A. Grant Permission

This example grant permission required to execute ALTER DATABASE SCOPED CONFIGURATION
to user [Joe].

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;

B. Set MAXDOP

This example sets MAXDOP = 1 for a primary database and MAXDOP = 4 for a secondary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;  
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ;

This example sets MAXDOP for a secondary database as it is for its primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY

C. Set LEGACY_CARDINALITY_ESTIMATION

This example sets LEGACY_CARDINALITY_ESTIMATION to ON for a secondary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY  
SET LEGACY_CARDINALITY_ESTIMATION=ON ;

This example sets LEGACY_CARDINALITY_ESTIMATION for a secondary database as it is for its primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY      
SET LEGACY_CARDINALITY_ESTIMATION=PRIMARY ;

D. Set PARAMETER_SNIFFING

This example sets PARAMETER_SNIFFING to OFF for a primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ;

This example sets PARAMETER_SNIFFING to OFF for a primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY      
SET PARAMETER_SNIFFING=OFF  ;

This example sets PARAMETER_SNIFFING for secondary database as it is on primary database
in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY      
SET PARAMETER_SNIFFING =PRIMARY  ;

E. Set QUERY_OPTIMIZER_HOTFIXES

Set QUERY_OPTIMIZER_HOTFIXES to ON for a primary database
in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON ;

F. Clear Procedure Cache

This example clears the procedure cache (possible only for a primary database).

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;

Additional Resources

MAXDOP Resources

LEGACY_CARDINALITY_ESTIMATION Resources

PARAMETER_SNIFFING Resources

QUERY_OPTIMIZER_HOTFIXES Resources

More information

sys.database_scoped_configurations (Transact-SQL)
sys.configurations (Transact-SQL)
Databases and Files Catalog Views (Transact-SQL)
Server Configuration Options (SQL Server)
Trace Flags (Transact-SQL)
sys.configurations (Transact-SQL)