ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

SE APLICA A: síSQL Server (a partir de 2016) síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Esta instrucción permite varios valores de configuración de base de datos en el nivel de base de datos individual.This statement enables several database configuration settings at the individual database level. Esta instrucción está disponible en Base de datos SQL de AzureAzure SQL Database y en SQL ServerSQL Server a partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x).This statement is available in Base de datos SQL de AzureAzure SQL Database and in SQL ServerSQL Server beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x). Los valores son:Those settings are:

  • Borrar la caché de procedimientos.Clear procedure cache.
  • Establecer el parámetro MAXDOP en un valor arbitrario (1, 2, ...) para la base de datos principal en función del valor que funciona mejor para esa base de datos concreta y establecer un valor distinto (por ejemplo, 0) para todas las bases de datos secundarias que se usan (por ejemplo, para notificar consultas).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 (such as for reporting queries).
  • Definir el modelo de estimación de la cardinalidad del optimizador de consultas independiente de la base de datos en el nivel de compatibilidad.Set the query optimizer cardinality estimation model independent of the database to compatibility level.
  • Habilitar o deshabilitar el examen de parámetros en el nivel de base de datos.Enable or disable parameter sniffing at the database level.
  • Habilitar o deshabilitar las revisiones de optimización de consulta en el nivel de base de datos.Enable or disable query optimization hotfixes at the database level.
  • Habilitar o deshabilitar la caché de identidad en el nivel de base de datos.Enable or disable the identity cache at the database level.
  • Habilitar o deshabilitar un código auxiliar de plan compilado que se almacenará en caché cuando se compile un lote por primera vez.Enable or disable a compiled plan stub to be stored in cache when a batch is compiled for the first time.
  • Habilitar o deshabilitar la recolección de estadísticas de ejecución para los módulos de T-SQL compilados de forma nativa.Enable or disable collection of execution statistics for natively compiled T-SQL modules.
  • Habilitar o deshabilitar ONLINE mediante opciones predeterminadas para las instrucciones de DDL que admiten la sintaxis ONLINE=.Enable or disable online by default options for DDL statements that support the ONLINE= syntax.
  • Habilitar o deshabilitar RESUMABLE mediante opciones predeterminadas para las instrucciones de DDL que admiten la sintaxis RESUMABLE=.Enable or disable resumable by default options for DDL statements that support the RESUMABLE= syntax.

    Icono de vínculo Convenciones de sintaxis de Transact-SQLlink icon Transact-SQL Syntax Conventions

SintaxisSyntax

ALTER DATABASE SCOPED CONFIGURATION  
{        
     {  [ FOR SECONDARY] SET <set_options>  }    
}  
| CLEAR PROCEDURE_CACHE  
| SET < set_options >
[;]    

< set_options > ::=    
{  
    MAXDOP = { <value> | PRIMARY}    
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}    
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}    
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
    | IDENTITY_CACHE = { ON | OFF }
    | OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
    | XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF } 
    | XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }    
    | ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED } 
    | ELEVATE_RESUMABLE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }  
}  

ArgumentosArguments

FOR SECONDARYFOR SECONDARY

Especifica la configuración de las bases de datos secundarias (todas las bases de datos secundarias deben tener valores idénticos).Specifies the settings for secondary databases (all secondary databases must have the identical values).

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

Especifica el valor predeterminado MAXDOP que se debe usar para las instrucciones.Specifies the default MAXDOP setting that should be used for statements. 0 es el valor predeterminado e indica que en su lugar se usará la configuración del servidor.0 is the default value and indicates that the server configuration will be used instead. En el ámbito de la base de datos, MAXDOP reemplaza (a menos que se establezca en 0) el grado máximo de paralelismo establecido en el nivel de servidor mediante sp_configure.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. Las sugerencias de consulta aún pueden reemplazar el valor MAXDOP con ámbito de base de datos con el fin de optimizar las consultas específicas que requieran otra configuración.Query hints can still override the DB scoped MAXDOP in order to tune specific queries that need different setting. Todas estas configuraciones están limitadas por el valor MAXDOP establecido para el grupo de cargas de trabajo.All these settings are limited by the MAXDOP set for the Workload Group.

Puede utilizar la opción Grado máximo de paralelismo para limitar el número de procesadores que debe utilizarse en la ejecución de planes en paralelo.You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. SQL Server considera los planes de ejecución en paralelo para las consultas, las operaciones de lenguaje de definición de datos (DDL) de índice, la inserción en paralelo, la modificación de columna en línea, la colección de estadísticas en paralelo y el rellenado de cursor estático y controlado por conjuntos de claves.SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and keyset-driven cursor population.

Para establecer esta opción en el nivel de instancia, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo.To set this option at the instance level, see Configure the max degree of parallelism Server Configuration Option.

Sugerencia

Para realizar esta acción en el nivel de consulta, agregue la sugerencia de consulta MAXDOP.To accomplish this at the query level, add the MAXDOP query hint.

PRIMARYPRIMARY

Solo se puede establecer para las secundarias, mientras la base de datos está en la principal, e indica que la configuración será la definida para la principal.Can only be set for the secondaries, while the database in on the primary, and indicates that the configuration will be the one set for the primary. Si cambia la configuración de la principal, el valor en las secundarias cambiará en consecuencia sin necesidad de establecer explícitamente el valor de las secundarias.If the configuration for the primary changes, the value on the secondaries will change accordingly without the need to set the secondaries value explicitly. PRIMARY es la configuración predeterminada para las secundarias.PRIMARY is the default setting for the secondaries.

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Permite establecer el modelo de estimación de la cardinalidad del optimizador de consultas en SQL Server 2012 y versiones anteriores, independientemente del nivel de compatibilidad de la base de datos.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. El valor predeterminado es OFF, que establece el modelo de estimación de la cardinalidad del optimizador de consultas en función del nivel de compatibilidad de la base de datos.The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database. Establecer este valor en ON es equivalente a habilitar la marca de seguimiento 9481.Setting this to ON is equivalent to enabling Trace Flag 9481.

Sugerencia

Para realizar esta acción en el nivel de consulta, agregue la sugerencia de consulta QUERYTRACEON.To accomplish this at the query level, add the QUERYTRACEON query hint. A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, para realizar esta acción en el nivel de consulta, agregue la sugerencia de consulta USE HINT en lugar de usar la marca de seguimiento.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT query hint instead of using the trace flag.

PRIMARYPRIMARY

Este valor solo es válido en las bases de datos secundarias mientras la base de datos se encuentra en la principal y especifica que la configuración del modelo de estimación de cardinalidad del optimizador de consultas en todas las bases de datos será el valor establecido para la principal.This value is only valid on secondaries while the database in on the primary, and specifies that the query optimizer cardinality estimation model setting on all secondaries will be the value set for the primary. Si cambia la configuración en la base de datos principal para el modelo de estimación de cardinalidad del optimizador de consultas, el valor en las bases de datos secundarias cambiará en consecuencia.If the configuration on the primary for the query optimizer cardinality estimation model changes, the value on the secondaries will change accordingly. PRIMARY es la configuración predeterminada para las secundarias.PRIMARY is the default setting for the secondaries.

PARAMETER_SNIFFING = { ON | OFF | PRIMARY}PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

Habilita o deshabilita el examen de parámetros.Enables or disables parameter sniffing. El valor predeterminado es ON.The default is ON. Es equivalente a la Marca de seguimiento 4136.This is equivalent to Trace Flag 4136.

Sugerencia

Para realizar esta acción en el nivel de consulta, vea la sugerencia de consulta OPTIMIZE FOR UNKNOWN.To accomplish this at the query level, see the OPTIMIZE FOR UNKNOWN query hint. A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, para realizar esta acción en el nivel de consulta, también está disponible la sugerencia de consulta USE HINT.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, the USE HINT query hint is also available.

PRIMARYPRIMARY

Este valor solo es válido en las bases de datos secundarias mientras la base de datos se encuentra en la principal y especifica que el valor de esta configuración en todas las bases de datos secundarias será el valor establecido para la principal.This value is only valid on secondaries while the database in on the primary, and specifies that the value for this setting on all secondaries will be the value set for the primary. Si cambia la configuración de la principal para usar el examen de parámetros, el valor en las secundarias cambiará en consecuencia sin necesidad de establecer explícitamente el valor de las secundarias.If the configuration on the primary for using parameter sniffing changes, the value on the secondaries will change accordingly without the need to set the secondaries value explicitly. Esta es la configuración predeterminada para las secundarias.This is the default setting for the secondaries.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Habilita o deshabilita las revisiones de optimización de consulta independientemente del nivel de compatibilidad de la base de datos.Enables or disables query optimization hotfixes regardless of the compatibility level of the database. El valor predeterminado es OFF, que deshabilita las revisiones de optimización de consulta que se publicaron después de que se introdujo el máximo nivel de compatibilidad disponible para una versión específica (posterior a RTM).The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM). Establecer este valor en ON es equivalente a habilitar la marca de seguimiento 4199.Setting this to ON is equivalent to enabling Trace Flag 4199.

Sugerencia

Para realizar esta acción en el nivel de consulta, agregue la sugerencia de consulta QUERYTRACEON.To accomplish this at the query level, add the QUERYTRACEON query hint. A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, para realizar esta acción en el nivel de consulta, agregue la sugerencia de consulta USE HINT en lugar de usar la marca de seguimiento.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT query hint instead of using the trace flag.

PRIMARYPRIMARY

Este valor solo es válido en las bases de datos secundarias mientras la base de datos se encuentra en la principal y especifica que el valor de esta configuración en todas las bases de datos secundarias es el valor establecido para la principal.This value is only valid on secondaries while the database in on the primary, and specifies that the value for this setting on all secondaries is the value set for the primary. Si cambia la configuración de la principal, el valor en las secundarias cambia en consecuencia sin necesidad de establecer explícitamente el valor de las secundarias.If the configuration for the primary changes, the value on the secondaries changes accordingly without the need to set the secondaries value explicitly. Esta es la configuración predeterminada para las secundarias.This is the default setting for the secondaries.

CLEAR PROCEDURE_CACHECLEAR PROCEDURE_CACHE

Borra la caché de procedimientos (plan) para la base de datos.Clears the procedure (plan) cache for the database. Esto se puede ejecutar en la base de datos principal y en las secundarias.This can be executed both on the primary and the secondaries.

IDENTITY_CACHE = { ON | OFF }IDENTITY_CACHE = { ON | OFF }

Se aplica a: SQL Server 2017 (14.x)SQL Server 2017 (14.x) y Base de datos SQL de AzureAzure SQL DatabaseApplies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Base de datos SQL de AzureAzure SQL Database

Habilita o deshabilita la caché de identidad en el nivel de base de datos.Enables or disables identity cache at the database level. El valor predeterminado es ON.The default is ON. El almacenamiento en caché de la identidad se usa para mejorar el rendimiento de INSERT en tablas con columnas de identidad.Identity caching is used to improve INSERT performance on tables with identity columns. Para evitar lagunas en los valores de una columna de identidad en los casos en que el servidor se reinicia inesperadamente o conmuta por error a un servidor secundario, deshabilite la opción IDENTITY_CACHE.To avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. Esta opción es similar a la marca de seguimiento 272 existente, excepto en que se puede establecer en el nivel de base de datos, en lugar de hacerlo solo en el nivel de servidor.This option is similar to the existing Trace Flag 272, except that it can be set at the database level rather than only at the server level.

Nota

Esta opción solo se puede establecer para PRIMARY.This option can only be set for the PRIMARY. Para obtener más información, vea las columnas de identidad.For more information, see identity columns.

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

Se aplica a: Base de datos SQL de AzureAzure SQL DatabaseApplies to: Base de datos SQL de AzureAzure SQL Database

Habilita o deshabilita un código auxiliar de plan compilado que se almacenará en caché cuando se compile un lote por primera vez.Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. El valor predeterminado es OFF.The default is OFF. Una vez que la configuración de ámbito de base de datos OPTIMIZE_FOR_AD_HOC_WORKLOADS está habilitada para una base de datos, se almacena un código auxiliar de plan compilado en caché al compilar por primera vez un lote.Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored in cache when a batch is compiled for the first time. Los códigos auxiliares de plan tienen una superficie de memoria menor en comparación con el tamaño del plan compilado completo.Plan stubs have a smaller memory footprint compared to the size of the full compiled plan. Si un lote se compila o se ejecuta de nuevo, se quitará el código auxiliar del plan compilado y se reemplazará con un plan compilado completo.If a batch is compiled or executed again, the compiled plan stub will be removed and replaced with a full compiled plan.

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

Se aplica a: Base de datos SQL de AzureAzure SQL DatabaseApplies to: Base de datos SQL de AzureAzure SQL Database

Habilita o deshabilita la recopilación de estadísticas de ejecución a nivel de módulo para los módulos de T-SQL compilados de forma nativa en la base de datos actual.Enables or disables collection of execution statistics at the module-level for natively compiled T-SQL modules in the current database. El valor predeterminado es OFF.The default is OFF. Las estadísticas de ejecución se reflejan en sys.dm_exec_procedure_stats.The execution statistics are reflected in sys.dm_exec_procedure_stats.

Las estadísticas de ejecución a nivel de módulo de los módulos de T-SQL compilados de forma nativa se recopilan si esta opción está activada o si se habilita la recopilación de estadísticas mediante sp_xtp_control_proc_exec_stats.Module-level execution statistics for natively compiled T-SQL modules are collected if either this option is ON, or if statistics collection is enabled through sp_xtp_control_proc_exec_stats.

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

Se aplica a: Base de datos SQL de AzureAzure SQL DatabaseApplies to: Base de datos SQL de AzureAzure SQL Database

Habilita o deshabilita la recopilación de estadísticas de ejecución a nivel de instrucción para los módulos de T-SQL compilados de forma nativa en la base de datos actual.Enables or disables collection of execution statistics at the statement-level for natively compiled T-SQL modules in the current database. El valor predeterminado es OFF.The default is OFF. Las estadísticas de ejecución se reflejan en sys.dm_exec_query_stats y en el Almacén de consultas.The execution statistics are reflected in sys.dm_exec_query_stats and in Query Store.

Las estadísticas de ejecución a nivel de instrucción de los módulos de T-SQL compilados de forma nativa se recopilan si esta opción está activada o si se habilita la recopilación de estadísticas mediante sp_xtp_control_query_exec_stats.Statement-level execution statistics for natively compiled T-SQL modules are collected if either this option is ON, or if statistics collection is enabled through sp_xtp_control_query_exec_stats.

Para obtener información más detallada sobre la supervisión del rendimiento de los módulos de T-SQL compilados de forma nativa, vea Supervisar el rendimiento de los procedimientos almacenados compilados de forma nativa.For more details about performance monitoring of natively-compiled T-SQL modules see Monitoring Performance of Natively Compiled Stored Procedures.

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

Se aplica a: Base de datos SQL de AzureAzure SQL Database (la característica está en versión preliminar pública)Applies to: Base de datos SQL de AzureAzure SQL Database (feature is in public preview)

Le permite seleccionar opciones que hacen que el motor eleve automáticamente las operaciones admitidas a ONLINE.Allows you to select options to cause the engine to automatically elevate supported operations to online. El valor predeterminado es OFF, que significa que las operaciones no se elevarán a ONLINE a menos que se especifique en la instrucción.The default is OFF, which means operations will not be elevated to online unless specified in the statement. Sys.database_scoped_configurations refleja el valor actual de ELEVATE_ONLINE.sys.database_scoped_configurations reflects the current value of ELEVATE_ONLINE. Estas opciones solo se aplicarán a las operaciones que generalmente son compatibles con ONLINE.These options will only apply to operations that are generally supported for online.

FAIL_UNSUPPORTEDFAIL_UNSUPPORTED

Este valor eleva todas las operaciones DDL compatibles a ONLINE.This value elevates all supported DDL operations to ONLINE. Se producirá un error en las operaciones que no admiten la ejecución ONLINE y se generará una advertencia.Operations that do not support online execution will fail and throw a warning.

WHEN_SUPPORTEDWHEN_SUPPORTED

Este valor eleva las operaciones que admiten ONLINE.This value elevates operations that support ONLINE. Las operaciones que no admiten ONLINE se ejecutarán sin conexión.Operations that do not support online will be run offline.

Nota

Puede invalidar la configuración predeterminada enviando una instrucción con la opción ONLINE especificada.You can override the default setting by submitting a statement with the ONLINE option specified.

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

Se aplica a: Base de datos SQL de AzureAzure SQL Database (la característica está en versión preliminar pública)Applies to: Base de datos SQL de AzureAzure SQL Database (feature is in public preview)

Le permite seleccionar opciones que hacen que el motor eleve automáticamente las operaciones admitidas a RESUMABLE.Allows you to select options to cause the engine to automatically elevate supported operations to resumable. El valor predeterminado es OFF, que significa que las operaciones no se elevarán a RESUMABLE a menos que se especifique en la instrucción.The default is OFF, which means operations are not be elevated to resumable unless specified in the statement. Sys.database_scoped_configurations refleja el valor actual de ELEVATE_RESUMABLE.sys.database_scoped_configurations reflects the current value of ELEVATE_RESUMABLE. Estas opciones solo se aplican a las operaciones que generalmente son compatibles con RESUMABLE.These options only apply to operations that are generally supported for resumable.

FAIL_UNSUPPORTEDFAIL_UNSUPPORTED

Este valor eleva todas las operaciones DDL compatibles a RESUMABLE.This value elevates all supported DDL operations to RESUMABLE. Se produce un error en las operaciones que no admiten la ejecución RESUMABLE y se genera una advertencia.Operations that do not support resumable execution fail and throw a warning.

WHEN_SUPPORTEDWHEN_SUPPORTED

Este valor eleva las operaciones que admiten RESUMABLE.This value elevates operations that support RESUMABLE. Las operaciones que no son compatibles con RESUMABLE se ejecutan como no reanudables.Operations that do not support resumable are run non-resumably.

Nota

Puede invalidar la configuración predeterminada enviando una instrucción con la opción RESUMABLE especificada.You can override the default setting by submitting a statement with the RESUMABLE option specified.

PermissionsPermissions

Requiere ALTER ANY DATABASE SCOPE CONFIGURATIONRequires ALTER ANY DATABASE SCOPE CONFIGURATION
en la base de datos.on the database. Este permiso se puede conceder por un usuario con permiso CONTROL en una base de datos.This permission can be granted by a user with CONTROL permission on a database.

Notas generalesGeneral Remarks

Aunque se pueden configurar bases de datos secundarias con valores de configuración de ámbito diferentes a los de su principal, en todas las bases de datos secundarias se usa la misma configuración.While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases use the same configuration. No se pueden configurar otros valores para bases de datos secundarias individuales.Different settings cannot be configured for individual secondaries.

La ejecución de esta instrucción borra la caché de procedimientos en la base de datos actual, lo que significa que se tendrán que volver a compilar todas las consultas.Executing this statement clears the procedure cache in the current database, which means that all queries have to recompile.

Para las consultas con nombres de tres elementos, la configuración de la conexión de base de datos actual para la consulta se cumplirá, menos para los módulos SQL (como procedimientos, funciones y desencadenadores) que se compilan en el contexto de base de datos actual y, por tanto, se usan las opciones de la base de datos en la que residen.For 3-part name queries, the settings for the current database connection for the query is honored, other than for SQL modules (such as procedures, functions, and triggers) that are compiled in the current database context and therefore uses the options of the database in which they reside.

El evento ALTER_DATABASE_SCOPED_CONFIGURATION se agrega como un evento de DDL que se puede usar para activar un desencadenador DDL.The ALTER_DATABASE_SCOPED_CONFIGURATION event is added as a DDL event that can be used to fire a DDL trigger. Se trata de un elemento secundario del grupo de desencadenador ALTER_DATABASE_EVENTS.This is a child of the ALTER_DATABASE_EVENTS trigger group.

Las opciones de configuración con ámbito de base de datos se transfieren con la base de datos.Database scoped configuration settings will be carried over with the database. Esto significa que cuando se restaura o adjunta una base de datos, se conservan las opciones de configuración existentes.This means that when a given database is restored or attached, the existing configuration settings remain.

Limitaciones y restriccionesLimitations and Restrictions

MAXDOPMAXDOP

La configuración granular puede invalidar las globales y Resource Governor puede limitar todas las demás opciones de MAXDOP.The granular settings can override the global ones and that resource governor can cap all other MAXDOP settings. La lógica para el valor MAXDOP es la siguiente:The logic for MAXDOP setting is the following:

  • La sugerencia de consulta invalida tanto sp_configure como la configuración con ámbito de base de datos.Query hint overrides both the sp_configure and the database scoped setting. Si el valor MAXDOP del grupo de recursos se establece para el grupo de cargas de trabajo:If the resource group MAXDOP is set for the workload group:

    • Si la sugerencia de consulta se establece en 0, se reemplaza por la configuración de Resource Governor.If the query hint is set to 0, it is overridden by the resource governor setting.

    • Si la sugerencia de consulta no es 0, se limita por la configuración de Resource Governor.If the query hint is not 0, it is capped by the resource governor setting.

  • La configuración con ámbito de base de datos (a menos que sea 0) invalida el valor de sp_configure a menos que haya una sugerencia de consulta y esté limitada por la configuración de Resource Governor.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.

  • La configuración de Resource Governor reemplaza el valor de sp_configure.The sp_configure setting is overridden by the resource governor setting.

QUERY_OPTIMIZER_HOTFIXESQUERY_OPTIMIZER_HOTFIXES

Cuando se usa la sugerencia QUERYTRACEON para habilitar el optimizador de consultas heredado o las revisiones del optimizador de consultas, sería una condición OR entre la sugerencia de consulta y el valor de configuración con ámbito de base de datos, lo que significa que si una de las dos está habilitada, las opciones se aplican.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 apply.

GeoDRGeoDR

Las bases de datos secundarias legibles (por ejemplo, los Grupos de disponibilidad AlwaysOn y GeoReplication) usan el valor secundario comprobando el estado de la base de datos.Readable secondary databases, e.g. Always On Availability Groups and GeoReplication, use the secondary value by checking the state of the database. Aunque la nueva compilación no se produce en la conmutación por error y técnicamente la nuevo base de datos principal tiene consultas que usan la configuración de la secundaria, la idea es que la configuración entre principal y secundaria solo varía cuando la carga de trabajo es diferente y, por tanto, las consultas en caché usan la configuración óptima, mientras que las consultas nuevas eligen la configuración nueva que es adecuada para ellas.Even though recompile does not occur 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 only vary when the workload is different and therefore the cached queries are using the optimal settings, whereas new queries pick the new settings that are appropriate for them.

DacFxDacFx

Como ALTER DATABASE SCOPED CONFIGURATION es una característica nueva de Azure SQL Database y SQL Server a partir de SQL Server 2016 que afecta al esquema de base de datos, las exportaciones del esquema (con o sin datos) no se pueden importar a una versión anterior de SQL Server como SQL Server 2012 (11.x)SQL Server 2012 (11.x) o SQL Server 2017 (14.x)SQL Server 2017 (14.x).Since ALTER DATABASE SCOPED CONFIGURATION is a new feature in Azure SQL Database and SQL Server beginning with SQL Server 2016 that affects the database schema, exports of the schema (with or without data) are not be able to be imported into an older version of SQL Server e.g. SQL Server 2012 (11.x)SQL Server 2012 (11.x) or SQL Server 2017 (14.x)SQL Server 2017 (14.x). Por ejemplo, una exportación a un DACPAC o un BACPAC desde una base de datos de SQL DatabaseSQL Database o SQL Server 2016 (13.x)SQL Server 2016 (13.x) que usara esta nueva característica no podría importarse a un servidor de nivel inferior.For example, an export to a DACPAC or a BACPAC from an SQL DatabaseSQL Database or SQL Server 2016 (13.x)SQL Server 2016 (13.x) database that used this new feature would not be able to be imported into a down-level server.

ELEVATE_ONLINEELEVATE_ONLINE

Esta opción solo se aplica a las instrucciones de DDL que admiten la sintaxis WITH(ONLINE =.This option only applies to DDL statements that support the WITH(ONLINE= syntax). Los índices XML no se ven afectados.XML indexes are not affected

ELEVATE_RESUMABLEELEVATE_RESUMABLE

Esta opción solo se aplica a las instrucciones de DDL que admiten la sintaxis WITH(ONLINE =.This option only applies to DDL statements that support the WITH(ONLINE= syntax). Los índices XML no se ven afectados.XML indexes are not affected

MetadatosMetadata

En la vista del sistema sys.database_scoped_configurations (Transact-SQL) se proporciona información sobre las configuraciones con ámbito en una base de datos.The sys.database_scoped_configurations (Transact-SQL) system view provides information about scoped configurations within a database. Las opciones de configuración con ámbito de base de datos solo se muestran en sys.database_scoped_configurations por ser invalidaciones de la configuración predeterminada de todo el servidor.Database-scoped configuration options only show up in sys.database_scoped_configurations as they are overrides to server-wide default settings. En la vista del sistema sys.configurations (Transact-SQL) solo se muestra la configuración de todo el servidor.The sys.configurations (Transact-SQL) system view only shows server-wide settings.

EjemplosExamples

En estos ejemplos se muestra el uso de ALTER DATABASE SCOPED CONFIGURATIONThese examples demonstrate the use of ALTER DATABASE SCOPED CONFIGURATION

A.A. Conceder permisoGrant Permission

En este ejemplo se concede el permiso necesario para ejecutar ALTER DATABASE SCOPED CONFIGURATIONThis example grant permission required to execute ALTER DATABASE SCOPED CONFIGURATION
al usuario [Joe].to user [Joe].

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;  

B.B. Configuración de MAXDOPSet MAXDOP

En este ejemplo se establece MAXDOP = 1 para una base de datos principal y MAXDOP = 4 para una base de datos secundaria en un escenario de replicación geográfica.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 ;  

En este ejemplo se establece MAXDOP para una base de datos secundaria de la misma forma que se establece para su base de datos principal en un escenario de replicación geográfica.This example sets MAXDOP for a secondary database to be the same as it is set for its primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY ;

C.C. Configuración de LEGACY_CARDINALITY_ESTIMATIONSet LEGACY_CARDINALITY_ESTIMATION

En este ejemplo se establece LEGACY_CARDINALITY_ESTIMATION en ON para una base de datos secundaria en un escenario de replicación geográfica.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 ;  

En este ejemplo se establece LEGACY_CARDINALITY_ESTIMATION para una base de datos secundaria de la misma forma que para su base de datos principal en un escenario de replicación geográfica.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.D. Configuración de PARAMETER_SNIFFINGSet PARAMETER_SNIFFING

En este ejemplo se establece PARAMETER_SNIFFING en OFF para una base de datos principal en un escenario de replicación geográfica.This example sets PARAMETER_SNIFFING to OFF for a primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ;  

En este ejemplo se establece PARAMETER_SNIFFING en OFF para una base de datos principal en un escenario de replicación geográfica.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 ;  

En este ejemplo se establece PARAMETER_SNIFFING para una base de datos secundaria de la misma forma que en la base de datos principal en un escenario de replicación geográfica.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.E. Configuración de QUERY_OPTIMIZER_HOTFIXESSet QUERY_OPTIMIZER_HOTFIXES

Se establece QUERY_OPTIMIZER_HOTFIXES en ON para una base de datos principal en un escenario de replicación geográfica.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.F. Borrado de la caché de procedimientosClear Procedure Cache

En este ejemplo se borra la caché de procedimientos (solo es posible para una base de datos principal).This example clears the procedure cache (possible only for a primary database).

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;  

G.G. Configuración de IDENTITY_CACHESet IDENTITY_CACHE

Se aplica a: SQL Server 2017 (14.x)SQL Server 2017 (14.x) y SQL DatabaseSQL Database (la característica está en versión preliminar pública)Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and SQL DatabaseSQL Database (feature is in public preview)

En este ejemplo se deshabilita la caché de identidad.This example disables the identity cache.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ; 

H.H. Configuración de OPTIMIZE_FOR_AD_HOC_WORKLOADSSet OPTIMIZE_FOR_AD_HOC_WORKLOADS

Se aplica a: SQL DatabaseSQL DatabaseApplies to: SQL DatabaseSQL Database

En este ejemplo se habilita o deshabilita un código auxiliar de plan compilado que se almacenará en caché cuando se compile un lote por primera vez.This example enables a compiled plan stub to be stored in cache when a batch is compiled for the first time.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

I.I. Establecer ELEVATE_ONLINESet ELEVATE_ONLINE

Se aplica a: Base de datos SQL de AzureAzure SQL Database (la característica está en versión preliminar pública)Applies to: Base de datos SQL de AzureAzure SQL Database (feature is in public preview)

Este ejemplo establece ELEVATE_ONLINE en FAIL_UNSUPPORTED.This example sets ELEVATE_ONLINE to FAIL_UNSUPPORTED. tsqlCopytsqlCopy

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE=FAIL_UNSUPPORTED ;

J.J. Establecer ELEVATE_RESUMABLESet ELEVATE_RESUMABLE

Se aplica a: Base de datos SQL de AzureAzure SQL Database (la característica está en versión preliminar pública)Applies to: Base de datos SQL de AzureAzure SQL Database (feature is in public preview)

Este ejemplo establece ELEVEATE_RESUMABLE en WHEN_SUPPORTED.This example sets ELEVEATE_RESUMABLE to WHEN_SUPPORTED. tsqlCopytsqlCopy

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE=WHEN_SUPPORTED ;  

Recursos adicionalesAdditional Resources

Recursos de MAXDOPMAXDOP Resources

Recursos de LEGACY_CARDINALITY_ESTIMATIONLEGACY_CARDINALITY_ESTIMATION Resources

Recursos de PARAMETER_SNIFFINGPARAMETER_SNIFFING Resources

Recursos de QUERY_OPTIMIZER_HOTFIXESQUERY_OPTIMIZER_HOTFIXES Resources

Recursos ELEVATE_ONLINEELEVATE_ONLINE Resources

Recursos ELEVATE_RESUMABLEELEVATE_RESUMABLE Resources

Más informaciónMore information

sys.database_scoped_configurations sys.database_scoped_configurations
sys.configurations sys.configurations
Vistas de catálogo de archivos y bases de datos (Transact-SQL) Databases and Files Catalog Views
Opciones de configuración de servidor (SQL Server) sys.configurationsServer Configuration Options sys.configurations