ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

Aplica-se a: simSQL Server 2016 (13.x) e posterior SimBanco de Dados SQL do Azure SimInstância Gerenciada do Azure SQL simAzure Synapse Analytics

Esse comando permite várias definições de configuração de banco de dados no nível do banco de dados individual.

As seguintes configurações têm suporte no Banco de Dados SQL do Azure, Instância Gerenciada do Azure SQL e SQL Server, conforme indicado pelo pela linha APLICA-SE A de cada configuração na seção Argumentos:

  • Limpar o cache de procedimento.
  • Definir o parâmetro MAXDOP para um valor arbitrário (1, 2,...) para o banco de dados primário com base naquilo que funciona melhor para esse banco de dados específico e definir um valor diferente (como 0) para todos os bancos de dados secundários usados (como para consultas de relatórios).
  • Defina o modelo de estimativa de cardinalidade do otimizador de consulta independente do nível de compatibilidade do banco de dados.
  • Habilitar ou desabilitar a detecção de parâmetro no nível do banco de dados.
  • Habilitar ou desabilitar hotfixes de otimização de consulta no nível do banco de dados.
  • Habilitar ou desabilitar o cache de identidade no nível do banco de dados.
  • Habilitar ou desabilitar um stub de plano compilado para ser armazenado em cache quando um lote for compilado pela primeira vez.
  • Habilitar ou desabilitar a coleta de estatísticas de execução para módulos Transact-SQL compilados nativamente.
  • Habilitar ou desabilitar online pelas opções padrão para instruções DDL compatíveis com a sintaxe ONLINE =.
  • Habilitar ou desabilitar retomáveis pelas opções padrão para instruções DDL compatíveis com a sintaxe RESUMABLE =.
  • Habilite ou desabilite recursos de processamento de consulta inteligente.
  • Habilitar ou desabilitar a imposição de plano acelerada.
  • Habilitar ou desabilitar a funcionalidade de remoção automática de tabelas temporárias globais.
  • Habilite ou desabilite a infraestrutura de criação de perfil de consulta leve.
  • Habilitar ou desabilitar a nova mensagem de erro String or binary data would be truncated.
  • Habilitar ou desabilitar a coleta do último plano de execução real em sys.dm_exec_query_plan_stats.
  • Especificar o número de minutos que uma operação de índice retomável em pausa permanece assim antes de ser anulada automaticamente pelo Mecanismo de Banco de Dados.
  • Habilitar ou desabilitar a espera por bloqueios de baixa prioridade para a atualização de estatísticas assíncrona.

Essa configuração só está disponível no Azure Synapse Analytics.

  • Definir o nível de compatibilidade de um banco de dados de usuário

Ícone de link Convenções da sintaxe Transact-SQL

Sintaxe

-- Syntax for SQL Server, Azure SQL Database and Azure SQL Managed Instance

ALTER DATABASE SCOPED CONFIGURATION
{
    { [ FOR SECONDARY] SET <set_options>}
}
| CLEAR PROCEDURE_CACHE [plan_handle]
| 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 }
    | INTERLEAVED_EXECUTION_TVF = { ON | OFF }
    | BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }
    | TSQL_SCALAR_UDF_INLINING = { ON | OFF }
    | ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | ELEVATE_RESUMABLE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
    | XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }
    | XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }
    | ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | BATCH_MODE_ON_ROWSTORE = { ON | OFF }
    | DEFERRED_COMPILATION_TV = { ON | OFF }
    | ACCELERATED_PLAN_FORCING = { ON | OFF }
    | GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }
    | LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }
    | VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }
    | LAST_QUERY_PLAN_STATS = { ON | OFF }
    | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = <time>
    | ISOLATE_SECURITY_POLICY_CARDINALITY  = { ON | OFF }
    | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }
    | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }
}

Importante

Começando com SQL Server 2019 (15.x), em Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL, alguns nomes de opções foram alterados:

  • DISABLE_INTERLEAVED_EXECUTION_TVF foi alterado para INTERLEAVED_EXECUTION_TVF
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK foi alterado para BATCH_MODE_MEMORY_GRANT_FEEDBACK
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS foi alterado para BATCH_MODE_ADAPTIVE_JOINS
-- Syntax for Azure Synapse Analytics

ALTER DATABASE SCOPED CONFIGURATION
{
    SET <set_options>
}
[;]

< set_options > ::=
{
    DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 } 
}

Argumentos

FOR SECONDARY

Especifica as definições para bancos de dados secundários (todos os bancos de dados secundários precisam ter valores idênticos).

CLEAR PROCEDURE_CACHE [plan_handle]

Limpa o cache do procedimento (plano) para o banco de dados e pode ser executado tanto no primário quanto no secundário.

Especifique um identificador de plano de consulta para limpar um único plano de consulta do cache do plano.

APLICA-SE A: A especificação de um identificador de plano de consulta está disponível começando pelo SQL Server 2019 (15.x), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL.

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

Especifica a configuração MAXDOP (grau máximo de paralelismo) padrão que deve ser usada nas instruções. 0 é o valor padrão e indica que a configuração do servidor será usada. O MAXDOP no escopo do banco de dados (a menos que esteja definido como 0) substitui o max degree of parallelism definido no nível do servidor por sp_configure. As dicas de consulta ainda podem substituir o MAXDOP no escopo do banco de dados para ajustar consultas específicas que precisem de uma configuração diferente. Todas essas configurações são limitadas pelo MAXDOP definido para o Grupo de carga de trabalho.

É possível usar a opção MAXDOP para limitar o número de processadores a serem usados na execução paralela do plano. SQL Server considera os planos de execução paralela para consultas, operações DDL (linguagem de definição de dados), inserção paralela, alteração online de coluna, coleta de estatísticas paralela e população de cursor estático e controlado por conjunto de chaves.

Observação

O limite de MAXDOP (grau máximo de paralelismo) é definido por tarefa. Não é um limite por solicitação ou por consulta. Isso significa que, durante uma execução de consulta paralela, uma solicitação única pode gerar várias tarefas que são atribuídas a um agendador. Para saber mais, confira o Guia de arquitetura de threads e tarefas.

Para definir essa opção no nível da instância, confira Configurar a opção max degree of parallelism de configuração do servidor.

Observação

No Banco de Dados SQL do Azure, a configuração no escopo do banco de dados MAXDOP para novos bancos de dados de pool único e elástico é definida como 8 por padrão. MAXDOP pode ser configurado para cada banco de dados, conforme descrito no artigo atual. Para obter recomendações sobre a configuraração ideal do MAXDOP, confira a seção Recursos Adicionais.

Dica

Para fazer isso no nível da consulta, use o MAXDOP, dica de consulta.
Para fazer isso no nível do servidor, use o MAXDOP (grau máximo de paralelismo) na opção de configuração do servidor.
Para fazer isso no nível de carga de trabalho, use a opção de configuração de grupo de carga de trabalho do Resource Governor MAX_DOP.

PRIMARY

Só pode ser definida para os secundários, enquanto o banco de dados está no primário e indica que a configuração será a definida para o primário. Se a configuração do primário for alterada, o valor nos secundários será alterado da maneira apropriada sem precisar ser definido explicitamente. PRIMARY é a configuração padrão dos secundários.

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Permite que você defina o modelo de estimativa de cardinalidade do otimizador de consulta para o SQL Server 2012 e versões anteriores independentemente do nível de compatibilidade do banco de dados. O padrão é OFF, que define o modelo de estimativa de cardinalidade do otimizador de consulta com base no nível de compatibilidade do banco de dados. Configurar LEGACY_CARDINALITY_ESTIMATION como ON equivale a habilitar Sinalizador de Rastreamento 9481.

Dica

Para fazer isso no nível da consulta, adicione a dica de consulta do QUERYTRACEON. Começando com o SQL Server 2016 (13.x) SP1, para fazer isso no nível da consulta, adicione a dica de consulta USE HINT em vez de usar o sinalizador de rastreamento.

PRIMARY

Esse valor só é válido nos secundários enquanto o banco de dados está no primário e especifica que a configuração do modelo de estimativa de cardinalidade do otimizador de consulta em todos os secundários será o valor definido para o primário. Se a configuração no primário para o modelo de estimativa de cardinalidade do otimizador de consulta for alterado, o valor nos secundários será alterado da maneira adequada. PRIMARY é a configuração padrão dos secundários.

PARAMETER_SNIFFING = { ON | OFF | PRIMARY }

Habilita ou desabilita a detecção de parâmetro. O padrão é ON. Configurar PARAMETER_SNIFFING como OFF equivale a habilitar Sinalizador de Rastreamento 4136.

Dica

Para fazer isso no nível da consulta, confira a dica de consulta OPTIMIZE FOR UNKNOWN. Começando com o SQL Server 2016 (13.x) SP1, para fazer isso no nível da consulta, a dica de consulta USE HINT também está disponível.

PRIMARY

Esse valor só é válido nos secundários enquanto o banco de dados está no primário e especifica que o valor dessa configuração em todos os secundários será o valor definido para o primário. Se a configuração do primário para o uso de detecção de parâmetro fora alterada, o valor nos secundários será alterado da maneira apropriada sem a necessidade de definir o valor dos secundários explicitamente. PRIMARY é a configuração padrão dos secundários.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Habilita ou desabilita os hotfixes de otimização de consulta, independentemente do nível de compatibilidade do banco de dados. O padrão é OFF, que desabilita os hotfixes de otimização de consulta que foram lançados depois que o nível de compatibilidade mais alto disponível foi introduzido para uma versão específica de (pós-RTM). Definir como ON equivale a habilitar o sinalizador de rastreamento 4199.

APLICA-SE A: SQL Server (Começando pelo SQL Server 2016 (13.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Dica

Para fazer isso no nível da consulta, adicione a dica de consulta do QUERYTRACEON. Começando com o SQL Server 2016 (13.x) SP1, para fazer isso no nível da consulta, adicione a dica de consulta USE HINT em vez de usar o sinalizador de rastreamento.

PRIMARY

Esse valor só é válido nos secundários enquanto o banco de dados está no primário e especifica que o valor dessa configuração em todos os secundários será o valor definido para o primário. Se a configuração do primário for alterada, o valor nos secundários será alterado da maneira apropriada sem precisar ser definido explicitamente. PRIMARY é a configuração padrão dos secundários.

IDENTITY_CACHE = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2017 (14.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Habilita ou desabilita o cache de identidade no nível do banco de dados. O padrão é ON. O cache de identidade é usado para melhorar o desempenho de INSERT em tabelas com colunas de identidade. Para evitar lacunas nos valores de uma coluna de identidade em casos em que o servidor é reiniciado inesperadamente ou efetua failover para um servidor secundário, desabilite a opção IDENTITY_CACHE. Essa opção é semelhante ao sinalizador de rastreamento 272 existente, exceto que ela pode ser definida no nível do banco de dados em vez de apenas no nível do servidor.

Observação

Essa opção só pode ser definida para o primário. Para obter mais informações, confira colunas de identidade.

INTERLEAVED_EXECUTION_TVF = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar a execução intercalada para funções com valor de tabela com várias instruções no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados como 140 e superior. A execução intercalada é um recurso que faz parte do processamento de consulta adaptável em Banco de Dados SQL do Azure. Para saber mais, confira Processamento de consulta inteligente.

Observação

Para o nível de compatibilidade do banco de dados 130 ou menor, esta configuração com escopo de banco de dados não tem nenhum efeito.

Somente no SQL Server 2017 (14.x), a opção INTERLEAVED_EXECUTION_TVF tinha o nome mais antigo DISABLE _INTERLEAVED_EXECUTION_TVF.

BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar Comentários de concessão de memória de modo de lote no escopo do banco de dados, mantendo o nível de compatibilidade do banco de dados como 140 e superior. Os comentários de concessão de memória de modo de lote é um recurso que faz parte do Processamento de consulta inteligente introduzido no SQL Server 2017 (14.x).

Observação

Para o nível de compatibilidade do banco de dados 130 ou menor, esta configuração com escopo de banco de dados não tem nenhum efeito.

BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar junções adaptáveis do modo de lote no escopo do banco de dados, mantendo o nível de compatibilidade do banco de dados como 140 e superior. As junções adaptáveis do modo de lote é um recurso que faz parte do Processamento de consulta inteligente introduzido no SQL Server 2017 (14.x).

Observação

Para o nível de compatibilidade do banco de dados 130 ou menor, esta configuração com escopo de banco de dados não tem nenhum efeito.

TSQL_SCALAR_UDF_INLINING = { ON | OFF }

APLICA-SE A: SQL Server (começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure (o recurso está em versão prévia pública)

Permite habilitar ou desabilitar o inlining UDF do T-SQL Scalar no escopo do banco de dados, mantendo o nível de compatibilidade do banco de dados como 150 e superior. O inlining UDF Escalar do T-SQL faz parte da família de recursos de Processamento de consulta inteligente.

Observação

Para o nível de compatibilidade do banco de dados 140 ou menor, esta configuração com escopo de banco de dados não tem nenhum efeito.

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite que você selecione as opções para fazer com que o mecanismo eleve automaticamente operações com suporte para online. O padrão é OFF, o que significa que as operações não serão elevadas para online, a menos que especificado na instrução. sys.database_scoped_configurations reflete o valor atual de ELEVATE_ONLINE. Essas opções serão aplicadas somente a operações que têm suporte para online.

FAIL_UNSUPPORTED

Este valor eleva todas as operações DDL com suporte para ONLINE. Operações que não oferecem suporte à execução online falharão e gerarão um erro.

Observação

A adição de uma coluna a uma tabela é uma operação online no caso geral. Em alguns cenários, por exemplo, ao adicionar uma coluna não anulável, uma coluna não pode ser adicionada online. Nesses casos, se FAIL_UNSUPPORTED for definido, a operação falhará.

WHEN_SUPPORTED

Este valor eleva operações que dão suporte a ONLINE. As operações sem suporte online serão executadas offline.

Observação

Você pode substituir a configuração padrão ao enviar uma instrução com a opção ONLINE especificada.

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite que você selecione as opções para fazer com que o mecanismo eleve automaticamente operações com suporte para retomáveis. O padrão é OFF, o que significa que as operações não serão elevadas para retomáveis, a menos que especificado na instrução. sys.database_scoped_configurations reflete o valor atual de ELEVATE_RESUMABLE. Essas opções serão aplicadas somente a operações que têm suporte para retomável.

FAIL_UNSUPPORTED

Este valor eleva todas as operações DDL com suporte para RESUMABLE. As operações que não oferecem suporte à execução retomável falharão e gerarão um aviso.

WHEN_SUPPORTED

Este valor eleva operações que dão suporte a RESUMABLE. As operações que não dão suporte a retomáveis são executadas de modo não retomável.

Observação

Você pode substituir a configuração padrão ao enviar uma instrução com a opção RESUMABLE especificada.

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Habilita ou desabilita um stub de plano compilado para ser armazenado em cache quando um lote é compilado pela primeira vez. O padrão é OFF. Quando a configuração no escopo do banco de dados OPTIMIZE_FOR_AD_HOC_WORKLOADS estiver habilitada para um banco de dados, um stub de plano compilado será armazenado em cache quando um lote for compilado pela primeira vez. Os stubs de plano têm um volume de memória menor em comparação com o tamanho do plano compilado completo. Se um lote for compilado ou executado novamente, o stub de plano compilado será removido e substituído por um plano compilado completo.

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

APLICA-SE A: Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Habilita ou desabilita a coleta de estatísticas de execução no nível do módulo para módulos T-SQL compilados nativamente no banco de dados atual. O padrão é OFF. As estatísticas de execução são refletidas em sys.dm_exec_procedure_stats.

As estatísticas de execução de nível de módulo para módulos T-SQL compilados nativamente serão coletadas se esta opção estiver ATIVADA ou se a coleta de estatísticas for habilitada por meio de sp_xtp_control_proc_exec_stats.

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

APLICA-SE A: Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Habilita ou desabilita a coleta de estatísticas de execução no nível de instrução para módulos T-SQL compilados nativamente no banco de dados atual. O padrão é OFF. As estatísticas de execução são refletidas em sys.dm_exec_query_stats e no Repositório de Consultas.

As estatísticas de execução de nível de instrução para módulos T-SQL compilados nativamente serão coletadas se esta opção estiver ATIVADA ou se a coleta de estatísticas for habilitada por meio de sp_xtp_control_query_exec_stats.

Para obter mais informações sobre monitoramento de desempenho de módulos Transact-SQL compilados nativamente, confira Monitorando o desempenho de procedimentos armazenados compilados nativamente.

ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar Comentários de concessão de memória de modo de linha no escopo do banco de dados, mantendo o nível de compatibilidade do banco de dados como 150 e superior. Os comentários de concessão de memória são um recurso que faz parte do Processamento de consulta inteligente introduzido no SQL Server 2017 (14.x) (o modo de linha é compatível com SQL Server 2019 (15.x) e Banco de Dados SQL do Azure).

Observação

Para o nível de compatibilidade do banco de dados 140 ou menor, esta configuração com escopo de banco de dados não tem nenhum efeito.

BATCH_MODE_ON_ROWSTORE = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar o modo de lote em rowstore no escopo do banco de dados, mantendo o nível de compatibilidade do banco de dados como 150 e superior. O modo de lote em rowstore é um recurso que faz parte da família de recursos de Processamento de consulta inteligente.

Observação

Para o nível de compatibilidade do banco de dados 140 ou menor, esta configuração com escopo de banco de dados não tem nenhum efeito.

DEFERRED_COMPILATION_TV = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar a compilação adiada de variável de tabela no escopo do banco de dados, mantendo o nível de compatibilidade do banco de dados como 150 e superior. Compilação adiada de variável de tabela é um recurso que faz parte da família de recursos Processamento de consulta inteligente.

Observação

Para o nível de compatibilidade do banco de dados 140 ou menor, esta configuração com escopo de banco de dados não tem nenhum efeito.

ACCELERATED_PLAN_FORCING = { ON | OFF }

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Habilita um mecanismo otimizado para a imposição de plano de consulta, aplicável a todas as formas de imposição de plano, tais como o Plano de Imposição do Repositório de Consultas, o Ajuste Automático ou a dica de consulta USE PLAN. O padrão é ON.

Observação

Não é recomendável desabilitar a imposição de plano acelerada.

GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }

APLICA-SE A: Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite configurar a funcionalidade de soltar automaticamente para tabelas temporárias globais. O padrão é ON, o que significa que as tabelas temporárias globais são descartadas automaticamente quando não estão em uso por qualquer sessão. Quando definido como OFF, as tabelas temporárias globais precisarão ser descartadas explicitamente usando uma instrução DROP TABLE, ou serão removidas automaticamente na reinicialização do servidor.

  • Com bancos de dados únicos e pools elásticos do Banco de Dados SQL do Azure, essa opção pode ser definida nos bancos de dados de usuários individuais do servidor do Banco de Dados SQL.
  • No SQL Server e na Instância Gerenciada de SQL do Azure, essa opção é definida em TempDB, e a configuração dos bancos de dados de usuário individuais não tem nenhum efeito.

LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite que você habilite ou desabilite a infraestrutura de criação de perfil de consulta leve. A LWP (infraestrutura de criação de perfil de consulta leve) fornece dados de desempenho de consulta de maneira mais eficiente do que os mecanismos de criação de perfil padrão e é habilitada por padrão.

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar a nova mensagem de erro String or binary data would be truncated. O SQL Server 2019 (15.x) apresenta uma mensagem de erro nova e mais específica (2628) para esse cenário:

String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.

Quando essa opção é definida como ON no nível de compatibilidade do banco de dados 150, os erros de truncamento acionam a nova mensagem de erro 2628 para fornecer mais contexto e simplificar o processo de solução de problemas.

Quando essa opção é definida como OFF no nível de compatibilidade do banco de dados 150, os erros de truncamento acionam a mensagem de erro anterior 8152.

Para o nível de compatibilidade do banco de dados 140 ou inferior, a mensagem de erro 2628 permanece uma mensagem de erro de aceitação que exige a habilitação do sinalizador de rastreamento 460 e essa configuração no escopo do banco de dados não tem nenhum efeito.

LAST_QUERY_PLAN_STATS = { ON | OFF }

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite habilitar ou desabilitar a coleta das últimas estatísticas de plano de consulta (equivalente a um plano de execução real) em sys.dm_exec_query_plan_stats.

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES

APLICA-SE A: Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

A opção PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES determina por quanto tempo (em minutos) o índice retomável ficará em pausa antes de ser anulado automaticamente pelo mecanismo.

  • O valor padrão é definido como 1 dia (1.440 minutos)
  • A duração mínima é definida como 1 minuto
  • A duração máxima é de 71.582 minutos
  • Quando definido como 0, uma operação em pausa nunca será anulada automaticamente

O valor atual da opção é exibido em sys.database_scoped_configurations.

ISOLATE_SECURITY_POLICY_CARDINALITY = { ON | OFF}

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite que você controle se um predicado da RLS (Segurança em Nível de Linha) é afetado pela cardinalidade do plano de execução da consulta geral do usuário. Quando ISOLATE_SECURITY_POLICY_CARDINALITY está ON, um predicado RLS não afeta a cardinalidade de um plano de execução. Por exemplo, considere uma tabela que contém 1 milhão linhas e um predicado RLS que restringe o resultado a 10 linhas para um usuário específico que emite a consulta. Com essa configuração com escopo de banco de dados definida como OFF, a estimativa de cardinalidade desse predicado será 10. Quando essa configuração com escopo do banco de dados estiver definida como ON, a otimização de consulta estimará 1 milhão linhas. É recomendável usar o valor padrão para a maioria das cargas de trabalho.

DW_COMPATIBILITY_LEVEL = {AUTO | 10 | 20 }

APLICA-SE A: Azure Synapse Analytics somente

Define Transact-SQL e os comportamentos de processamento de consulta para que sejam compatíveis com a versão especificada do mecanismo de banco de dados. Após a definição, quando uma consulta é executada nesse banco de dados, somente os recursos compatíveis serão exercitados. O nível de compatibilidade de um banco de dados é definido como AUTO por padrão quando é criado pela primeira vez. O nível de compatibilidade é preservado mesmo após as operações de pausa/retomada e backup/restauração do banco de dados.

Nível de Compatibilidade Comentários
AUTO Padrão. Seu valor é atualizado automaticamente pelo mecanismo do Synapse Analytics. O valor atual é 20.
10 Exercita os comportamentos de processamento de consulta e Transact-SQL antes da introdução do suporte em nível de compatibilidade.
20 Primeiro nível de compatibilidade que inclui os comportamentos de processamento de consulta e Transact-SQL restritos.

EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }

APLICA-SE A: Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Permite que você controle se as estatísticas de execução para UDF (funções definidas pelo usuário) escalares são mostradas no modo de exibição do sistema sys.dm_exec_function_stats. Para algumas cargas de trabalho intensivas que são pesadas em UDF, coletar estatísticas de execução de função pode causar uma sobrecarga de desempenho perceptível. Isso pode ser evitado definindo a configuração no escopo do banco de dados do EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS como OFF.

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }

APLICA-SE A: Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL (o recurso está na versão prévia pública)

Se a atualização de estatísticas assíncrona estiver habilitada, habilitar essa configuração fará com que a solicitação em segundo plano de atualização das estatísticas aguarde um bloqueio Sch-M em uma fila de baixa prioridade, a fim de evitar o bloqueio de outras sessões em cenários de alta simultaneidade. Para obter mais informações, confira AUTO_UPDATE_STATISTICS_ASYNC.

Permissões

Requer ALTER ANY DATABASE SCOPED CONFIGURATION no banco de dados. Essa permissão pode ser concedida por um usuário com a permissão CONTROL em um banco de dados.

Comentários gerais

Embora seja possível configurar bancos de dados secundários para com definições de configuração de escopo diferentes do primário, todos os bancos de dados secundários usam a mesma configuração. As configurações diferentes não podem ser configuradas para secundários individuais.

Executar essa instrução limpa o cache de procedimento no banco de dados atual, que significa que todas as consultas precisarão ser recompiladas.

Para consultas de nome de três partes, as configurações da conexão de banco de dados atual da consulta são cumpridas, exceto para os módulos SQL (como procedimentos, funções e gatilhos) que são compilados em outro contexto de banco de dados e, portanto, usam as opções do banco de dados no qual residem. Da mesma forma, ao atualizar estatísticas de maneira assíncrona, a configuração de ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY para o banco de dados em que as estatísticas residem é cumprida.

O evento ALTER_DATABASE_SCOPED_CONFIGURATION é adicionado como um evento DDL que pode ser usado para acionar um gatilho DDL e é um filho do grupo do gatilho ALTER_DATABASE_EVENTS.

Definições de configuração no escopo do banco de dados serão transferidas para o banco de dados, o que significa que, quando um determinado banco de dados é restaurado ou anexado, as definições de configuração existentes permanecem.

Começando com SQL Server 2019 (15.x), em Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL, alguns nomes de opções foram alterados:

  • DISABLE_INTERLEAVED_EXECUTION_TVF foi alterado para INTERLEAVED_EXECUTION_TVF
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK foi alterado para BATCH_MODE_MEMORY_GRANT_FEEDBACK
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS foi alterado para BATCH_MODE_ADAPTIVE_JOINS

Limitações e Restrições

MAXDOP

As configurações granulares podem substituir as globais e o Resource Governor pode limitar a todas as outras configurações de MAXDOP. A lógica para a configuração de MAXDOP é a seguinte:

  • A dica de consulta substitui tanto sp_configure quanto a configuração no escopo do banco de dados. Se o grupo de recursos MAXDOP estiver definido para o grupo de carga de trabalho:

    • Se a dica de consulta for definida como zero (0), ela será substituída pela configuração do Resource Governor.

    • Se a dica de consulta não for zero (0), ela será limitada pela configuração do Resource Governor.

  • A configuração no escopo do banco de dados (a menos que seja zero) substitui a configuração sp_configure, a menos que haja uma dica de consulta e seja limitada pela configuração do Resource Governor.

  • A configuração de sp_configure substituída pela configuração do Resource Governor.

QUERY_OPTIMIZER_HOTFIXES

Quando a dica QUERYTRACEON é usada para habilitar o Otimizador de Consulta padrão do SQL Server 7.0 por meio de versões do SQL Server 2012 (11.x) ou hotfixes do Otimizador de Consulta, ela é uma condição OR entre a dica de consulta e a definição de configuração de banco de dados com escopo, o que significa que se qualquer uma for habilitada, ambas as configurações de banco de dados com escopo se aplicarão.

DR de área geográfica

Os bancos de dados secundários para leitura (Grupos de Disponibilidade Always On e bancos de dados com replicação geográfica de Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL) usam o valor secundário verificando o estado do banco de dados. Embora a recompilação não ocorra no failover e tecnicamente o novo primário tenha consultas que usam as configurações do secundário, a ideia é que a configuração entre o primário e o secundário apenas varie quando a carga de trabalho for diferente e, portanto, as consultas armazenadas em cache estiverem usando as configurações ideais, enquanto as novas consultas estarão selecionando as novas configurações apropriadas para elas.

DacFx

Uma vez que ALTER DATABASE SCOPED CONFIGURATION é um novo recurso no Banco de Dados SQL do Azure, no Instância Gerenciada do Azure SQL e no SQL Server (começando pelo SQL Server 2016 (13.x)) que afeta o esquema de banco de dados, exportações do esquema (com ou sem dados) não podem ser importadas para uma versão mais antiga do SQL Server, como SQL Server 2012 (11.x) ou SQL Server 2014 (12.x). Por exemplo, uma exportação para um DACPAC ou um BACPAC de um Banco de Dados SQL ou de um banco de dados do SQL Server 2016 (13.x) que tiver usado esse novo recurso não poderá ser importada em um servidor de nível inferior.

ELEVATE_ONLINE

Essa opção é aplicável somente a instruções DDL com suporte a WITH (ONLINE = <syntax>). Índices XML não são afetados.

ELEVATE_RESUMABLE

Essa opção é aplicável somente a instruções DDL com suporte a WITH (RESUMABLE = <syntax>). Índices XML não são afetados.

Metadados

O Modo de Exibição do Sistema sys.database_scoped_configurations (Transact-SQL) fornece informações sobre as configurações no escopo em um banco de dados. As opções de configuração no escopo do banco de dados só aparecem em sys.database_scoped_configurations porque elas são substituições das configurações padrão de todo o servidor. O Modo de Exibição do Sistema sys.configurations (Transact-SQL) mostra apenas as configurações de todo o servidor.

Exemplos

Esses exemplos demonstram o uso de ALTER DATABASE SCOPED CONFIGURATION

a. Conceder permissão

Este exemplo concede a permissão necessária para executar ALTER DATABASE SCOPED CONFIGURATION ao usuário José.

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;

B. Definir MAXDOP

Este exemplo define MAXDOP = 1 para um banco de dados primário e MAXDOP = 4 para um banco de dados secundário em um cenário de replicação geográfica.

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

Este exemplo define MAXDOP para um banco de dados secundário como o mesmo que está definido para seu banco de dados primário em um cenário de replicação geográfica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY ;

C. Definir LEGACY_CARDINALITY_ESTIMATION

Este exemplo define LEGACY_CARDINALITY_ESTIMATION como ON para um banco de dados secundário em um cenário de replicação geográfica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON ;

Este exemplo define LEGACY_CARDINALITY_ESTIMATION para um banco de dados secundário como o mesmo que está definido para seu banco de dados primário em um cenário de replicação geográfica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY ;

D. Definir PARAMETER_SNIFFING

Este exemplo define PARAMETER_SNIFFING como OFF para um banco de dados primário em um cenário de replicação geográfica.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF ;

Este exemplo define PARAMETER_SNIFFING como OFF para um banco de dados secundário em um cenário de replicação geográfica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF ;

Este exemplo define PARAMETER_SNIFFING para um banco de dados secundário como o mesmo que está definido em seu banco de dados primário em um cenário de replicação geográfica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY ;

E. Definir QUERY_OPTIMIZER_HOTFIXES

Defina QUERY_OPTIMIZER_HOTFIXES como ON para um banco de dados primário em um cenário de replicação geográfica.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;

F. Limpar o cache de procedimento

Este exemplo limpa o cache de procedimento (é possível somente para um banco de dados primário).

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

G. Definir IDENTITY_CACHE

APLICA-SE A: SQL Server (Começando pelo SQL Server 2017 (14.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Este exemplo desabilita o cache de identidade.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF ;

H. Definir OPTIMIZE_FOR_AD_HOC_WORKLOADS

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Este exemplo habilita um stub de plano compilado para ser armazenado em cache quando um lote é compilado pela primeira vez.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

I. Definir ELEVATE_ONLINE

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Este exemplo define ELEVATE_ONLINE como FAIL_UNSUPPORTED.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED ;

J. Definir ELEVATE_RESUMABLE

APLICA-SE A: SQL Server (Começando pelo SQL Server 2019 (15.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Este exemplo define ELEVATE_RESUMABLE como WHEN_SUPPORTED.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED ;

K. Apagar um plano de consulta do cache do plano

APLICA-SE A: SQL Server (Começando pelo SQL Server 2017 (14.x)), Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Este exemplo limpa um plano específico do cache de procedimento

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000500F443610F003B7CD12C02000001000000000000000000000000000000000000000000000000000000;

L. Definir duração da pausa

APLICA-SE A: Banco de Dados SQL do Azure e Instância Gerenciada do Azure SQL

Este exemplo define a duração da pausa do índice retomável como 60 minutos.

ALTER DATABASE SCOPED CONFIGURATION
SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 60

Recursos adicionais

Recursos de MAXDOP

Recursos de LEGACY_CARDINALITY_ESTIMATION

Recursos de PARAMETER_SNIFFING

Recursos de QUERY_OPTIMIZER_HOTFIXES

Recursos ELEVATE_ONLINE

Diretrizes para operações de índice online

Recursos ELEVATE_RESUMABLE

Diretrizes para operações de índice online

Mais informações

sys.database_scoped_configurations
Recomendações e diretrizes para a opção de configuração "grau máximo de paralelismo" do SQL Server
sys.configurations
Exibições de catálogo de bancos de dados e de arquivos
Opções de configuração de servidor
Como funcionam as operações de índice online
Executar operações de índice online
ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)