ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

Si applica a:yes SQL Server 2016 (13.x) e versioni successive Yesdatabase SQL di Azure YesIstanza gestita di SQL di Azure Azure Synapse yesAnalytics

Questo comando abilita diverse impostazioni di configurazione del database a livello di singolo database.

Le impostazioni seguenti sono supportate in database SQL di Azure, Istanza gestita di SQL di Azure e in SQL Server come indicato dalla riga SI APPLICA A per ogni impostazione nella sezione Argomenti:

  • Cancellare la cache delle procedure.
  • Impostare il parametro MAXDOP su un valore consigliato (1,2, ...) per il database primario in base a ciò che funziona meglio per quel carico di lavoro specifico e impostare un valore diverso per i database di replica secondaria usati dalle query di report. Per indicazioni sulla scelta di maxDOP, vedere Configurare l'opzione di configurazione del server max degree of parallelism.
  • Impostare il modello di stima della cardinalità di Query Optimizer su un livello di compatibilità, indipendentemente dal database.
  • Abilitare o disabilitare l'analisi dei parametri a livello di database.
  • Abilitare o disabilitare gli hotfix di ottimizzazione query a livello di database.
  • Abilitare o disabilitare la cache Identity a livello di database.
  • Abilitare o disabilitare uno stub del piano compilato da memorizzare nella cache quando un batch viene compilato per la prima volta.
  • Abilitare o disabilitare la raccolta di statistiche di esecuzione per i moduli Transact-SQL compilati in modo nativo.
  • Abilitare o disabilitare le opzioni online per impostazione predefinita per le istruzioni DDL che supportano la sintassi ONLINE =.
  • Abilitare o disabilitare le opzioni ripristinabile per impostazione predefinita per le istruzioni DDL che supportano la sintassi RESUMABLE =.
  • Abilitare o disabilitare le funzionalità di elaborazione di query intelligenti.
  • Abilitare o disabilitare l'uso forzato del piano accelerato.
  • Abilitare o disabilitare le funzionalità di eliminazione automatica delle tabelle temporanee globali.
  • Abilitare o disabilitare l'infrastruttura leggera di profilatura query.
  • Abilitare o disabilitare il nuovo messaggio di errore String or binary data would be truncated.
  • Abilita l'equivalente dell'ultimo piano di esecuzione effettivo in sys.dm_exec_query_plan_stats.
  • Specificare il numero di minuti in cui un'operazione di indice ripristinabile sospesa viene sospesa prima che venga interrotta automaticamente dal motore di database.
  • Abilitare o disabilitare l'attesa di blocchi con priorità bassa per l'aggiornamento asincrono delle statistiche.

Questa impostazione è disponibile solo in Azure Synapse Analytics.

  • Impostare il livello di compatibilità di un database utente

link iconConvenzioni della sintassi transact-SQL

Sintassi

-- 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

A partire da SQL Server 2019 (15.x), in database SQL di Azure e Istanza gestita di SQL di Azure, alcuni nomi di opzione sono stati modificati:

  • DISABLE_INTERLEAVED_EXECUTION_TVF è diventata INTERLEAVED_EXECUTION_TVF
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK è diventata BATCH_MODE_MEMORY_GRANT_FEEDBACK
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS è diventata BATCH_MODE_ADAPTIVE_JOINS
-- Syntax for Azure Synapse Analytics

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

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

Argomenti

FOR SECONDARY

Specifica le impostazioni per i database secondari. Tutti i database secondari devono avere valori identici.

CLEAR PROCEDURE_CACHE [plan_handle]

Cancella la cache delle procedure (piani) per il database e può essere eseguito sia nel database primario che in quelli secondari.

Specificare un handle di piano di query per cancellare un singolo piano di query dalla cache dei piani.

SI APPLICA A: specificare un handle di piano di query è disponibile a partire da SQL Server 2019 (15.x), in database SQL di Azure e Istanza gestita di SQL di Azure.

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

Specifica l'impostazione del massimo grado di parallelismo (MAXDOP) predefinita da usare per le istruzioni. 0 è il valore predefinito. Indica che in alternativa sarà usata la configurazione server. Nell'ambito del database il parametro MAXDOP (a meno che non sia impostato su 0) sostituisce il massimo grado di parallelismo impostato a livello di server da sp_configure. Gli hint per la query possono tuttavia sostituire il parametro MAXDOP con ambito database per ottimizzare le query specifiche per cui è necessaria un'impostazione diversa. Tutte queste impostazioni sono vincolate dal parametro MAXDOP definito per il gruppo di carico di lavoro.

È possibile usare l'opzione MAXDOP per limitare il numero di processori da usare per l'esecuzione di piani paralleli. SQL Server valuta i piani di esecuzione parallela per query, operazioni DDL (Data Definition Language) sugli indici, inserimento parallelo, modifica colonna online, raccolta di statistiche parallela e popolamento dei cursori statici e gestiti da keyset.

Nota

Il limite del massimo grado di parallelismo (MAXDOP) è impostato per attività. Non è un limite per richiesta o per query. Ciò significa che durante l'esecuzione di query parallele una singola richiesta può generare più attività che vengono assegnate a un'utilità di pianificazione. Per altre informazioni, vedere Guida sull'architettura dei thread e delle attività.

Per impostare questa opzione a livello di istanza, vedere Configurare l'opzione di configurazione del server max degree of parallelism.

Nota

In database SQL di Azure, la configurazione con ambito database MAXDOP per i nuovi database di pool singolo ed elastico è impostata su 8 per impostazione predefinita. MAXDOP può essere configurato per ogni database, come descritto nell'articolo corrente. Per consigli sulla configurazione ottimale di MAXDOP, vedere la sezione Risorse aggiuntive.

Suggerimento

Per eseguire questa operazione a livello di query, usare l'hint per la queryMAXDOP.
Per eseguire questa operazione a livello di server, usare l'opzione di configurazione server relativa al massimo grado di parallelismo (MAXDOP).
Per eseguire questa operazione a livello di carico di lavoro, usare l'opzione di configurazione del gruppo di carico di lavoro di Resource GovernorMAX_DOP.

PRIMARY

Può essere impostato solo per i database secondari se il database è primario. Indica che la configurazione corrisponderà a quella impostata per il database primario. Se la configurazione per il database primario viene modificata, il valore nei database secondari sarà modificato di conseguenza senza dover impostare in modo esplicito il valore nei database secondari. PRIMARY è l'impostazione predefinita per i database secondari.

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Consente di impostare il modello di stima della cardinalità di Query Optimizer in SQL Server 2012 o versioni precedenti indipendentemente dal livello di compatibilità del database. Il valore predefinito è OFF che imposta il modello di stima della cardinalità di Query Optimizer sulla base del livello di compatibilità del database. Impostare LEGACY_CARDINALITY_ESTIMATION su ON equivale ad abilitare il flag di traccia 9481.

Suggerimento

Per eseguire questa operazione a livello di query, aggiungere l'hint per la queryQUERYTRACEON. A partire da SQL Server 2016 (13.x) SP1, per eseguire questa operazione a livello di query, aggiungere l'hint USEHINTquery anziché usare il flag di traccia.

PRIMARY

Questo valore è valido solo nei database secondari quando il database è primario. Specifica che l'impostazione del modello di stima della cardinalità di Query Optimizer in tutti i database secondari sarà il valore impostato per il database primario. Se la configurazione per il modello di stima di cardinalità di Query Optimizer viene modificata nel database primario, il valore nei database secondari sarà modificato di conseguenza. PRIMARY è l'impostazione predefinita per i database secondari.

= PARAMETER_SNIFFING { ON | OFF | PRIMARY }

Abilita o disabilita l'analisi dei parametri. Il valore predefinito è ON. Impostare PARAMETER_SNIFFING su OFF equivale ad abilitare il flag di traccia 4136.

Suggerimento

Per eseguire questa operazione a livello di query, vedere l'hint per la queryOPTIMIZE FOR UNKNOWN. A partire da SQL Server 2016 (13.x) SP1, per eseguire questa operazione a livello di query, è disponibile anche l'hint USEHINTquery.

PRIMARY

Questo valore è valido solo nei database secondari quando il database è primario. Specifica che il valore per questa impostazione in tutti i database secondari sarà il valore impostato per il database primario. Se la configurazione per l'uso dell'analisi dei parametri viene modificata nel database primario, il valore nei database secondari sarà modificato di conseguenza senza dover impostare in modo esplicito il valore nei database secondari. PRIMARY è l'impostazione predefinita per i database secondari.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Abilita o disabilita gli hotfix di ottimizzazione query indipendentemente dal livello di compatibilità del database. Il valore predefinito è OFF, che disabilita gli hotfix di ottimizzazione query rilasciati dopo che è stato introdotto il massimo livello di compatibilità disponibile per una specifica versione (post RTM). L'impostazione di ON equivale ad abilitare il flag di traccia 4199.

SI APPLICA A: SQL Server (a partire da SQL Server 2016 (13.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Suggerimento

Per eseguire questa operazione a livello di query, aggiungere l'hint per la queryQUERYTRACEON. A partire da SQL Server 2016 (13.x) SP1, per eseguire questa operazione a livello di query, aggiungere l'hint per la query USE HINT anziché usare il flag di traccia.

PRIMARY

Questo valore è valido solo nei database secondari quando il database è primario. Specifica che il valore per questa impostazione in tutti i database secondari è il valore impostato per il database primario. Se la configurazione per il database primario viene modificata, il valore nei database secondari viene modificato di conseguenza senza dover impostare in modo esplicito il valore nei database secondari. PRIMARY è l'impostazione predefinita per i database secondari.

IDENTITY_CACHE = { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2017 (14.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Abilita o disabilita la cache Identity a livello di database. Il valore predefinito è ON. La memorizzazione nella cache di Identity serve a migliorare le prestazioni di INSERT nelle tabelle che contengono colonne Identity. Disabilitare l'opzione IDENTITY_CACHE per evitare scostamenti nei valori in una colonna Identity nel caso in cui un server sia riavviato in modo imprevisto o esegua un failover in un server secondario. Questa opzione è simile all'attuale flag di traccia 272, ad eccezione del fatto che può essere impostata a livello di database, anziché solo a livello di server.

Nota

Questa opzione può essere impostata solo per PRIMARY. Per altre informazioni, vedere Colonne Identity.

INTERLEAVED_EXECUTION_TVF = { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare l'esecuzione interleaved per funzioni con valori di tabella a più istruzioni nell'ambito del database o dell'istruzione mantenendo comunque la compatibilità sul livello 140 o superiore. L'esecuzione interleaved è una funzionalità che fa parte dell'elaborazione di query adattive in database SQL di Azure. Per altre informazioni, vedere Elaborazione di query intelligenti.

Nota

Per livelli di compatibilità del database pari a 130 o inferiori, questa configurazione con ambito di database non ha effetto.

Solo in SQL Server 2017 (14.x), l'opzione INTERLEAVED_EXECUTION_TVF ha il nome precedente DISABLE_INTERLEAVED_EXECUTION_TVF.

= BATCH_MODE_MEMORY_GRANT_FEEDBACK { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare il feedback delle concessioni di memoria in modalità batch nell'ambito del database mantenendo comunque un livello di compatibilità del database pari a 140 o superiore. Concessione di memoria in modalità batch una funzionalità che fa parte dell'elaborazione di query intelligente introdotta in SQL Server 2017 (14.x).

Nota

Per livelli di compatibilità del database pari a 130 o inferiori, questa configurazione con ambito di database non ha effetto.

= BATCH_MODE_ADAPTIVE_JOINS { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare i join adattivi in modalità batch nell'ambito del database mantenendo comunque un livello di compatibilità del database pari a 140 o superiore. I join adattivi in modalità batch sono una funzionalità che fa parte dell'elaborazione di query intelligente introdotta in SQL Server 2017 (14.x).

Nota

Per livelli di compatibilità del database pari a 130 o inferiori, questa configurazione con ambito di database non ha effetto.

TSQL_SCALAR_UDF_INLINING = { ON | OFF }

SI APPLICA A: SQL Server (A partire da SQL Server 2019 (15.x)) e database SQL di Azure (funzionalità in anteprima pubblica)

Consente di abilitare o disabilitare l'inlining di funzioni definite dall'utente scalari T-SQL nell'ambito del database mantenendo comunque un livello di compatibilità del database pari a 150 o superiore. L'inlining di funzioni definite dall'utente scalari T-SQL fa parte della famiglia di funzionalità di elaborazione di query intelligenti.

Nota

Per il livello di compatibilità del database pari a 140 o inferiore, questa configurazione con ambito di database non ha effetto.

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di selezionare opzioni grazie alle quali il motore eleva automaticamente le operazioni supportate all'esecuzione online. Il valore predefinito è OFF. Ciò significa che le operazioni verranno elevate all'esecuzione online solo se specificato nell'istruzione. sys.database_scoped_configurations riflette il valore corrente di ELEVATE_ONLINE. Queste opzioni si applicano solo alle operazioni supportate per l'esecuzione online.

FAIL_UNSUPPORTED

Questo valore eleva tutte le operazioni DDL supportate all'esecuzione ONLINE. Le operazioni che non supportano l'esecuzione online avranno esito negativo e genereranno un errore.

Nota

L'aggiunta di una colonna a una tabella è un'operazione online nel caso generale. In alcuni scenari, ad esempio quando si aggiunge una colonna non nullable, non è possibile aggiungere una colonna online. In questi casi, se FAIL_UNSUPPORTED è impostato, l'operazione avrà esito negativo.

WHEN_SUPPORTED

Questo valore eleva le operazioni che supportano l'esecuzione ONLINE. Le operazioni che non supportano l'esecuzione online verranno eseguite offline.

Nota

È possibile eseguire l'override dell'impostazione predefinita inviando un'istruzione con l'opzione ONLINE specificata.

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di selezionare opzioni grazie alle quali il motore eleva automaticamente le operazioni supportate all'esecuzione ripristinabile. Il valore predefinito è OFF. Ciò significa che le operazioni verranno elevate all'esecuzione ripristinabile solo se specificato nell'istruzione. sys.database_scoped_configurations riflette il valore corrente di ELEVATE_RESUMABLE. Queste opzioni si applicano solo alle operazioni supportate per l'esecuzione ripristinabile.

FAIL_UNSUPPORTED

Questo valore eleva tutte le operazioni DDL supportate all'esecuzione RESUMABLE. Le operazioni che non supportano l'esecuzione ripristinabile hanno esito negativo e generano un errore.

WHEN_SUPPORTED

Questo valore eleva le operazioni che supportano l'esecuzione RESUMABLE. Le operazioni che non supportano l'esecuzione ripristinabile vengono eseguite in modo non ripristinabile.

Nota

È possibile eseguire l'override dell'impostazione predefinita inviando un'istruzione con l'opzione RESUMABLE specificata.

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Abilita o disabilita uno stub del piano compilato da memorizzare nella cache quando un batch viene compilato per la prima volta. Il valore predefinito è OFF. Dopo aver abilitato la configurazione con ambito database OPTIMIZE_FOR_AD_HOC_WORKLOADS per un database, uno stub del piano compilato sarà archiviato nella cache quando un batch viene compilato per la prima volta. Il footprint di memoria degli stub del piano è ridotto rispetto alle dimensioni del piano compilato completo. Se un batch viene compilato o eseguito nuovamente, lo stub del piano compilato sarà rimosso e sostituito da un piano compilato completo.

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

SI APPLICA A: database SQL di Azure e Istanza gestita di SQL di Azure

Abilita o disabilita la raccolta di statistiche di esecuzione a livello di modulo per i moduli T-SQL compilati in modo nativo nel database corrente. Il valore predefinito è OFF. Le statistiche di esecuzione vengono riflesse in sys.dm_exec_procedure_stats.

Le statistiche di esecuzione a livello di modulo per i moduli T-SQL compilati in modo nativo vengono raccolte se questa opzione è ON o se la raccolta di statistiche è abilitata mediante sp_xtp_control_proc_exec_stats.

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

SI APPLICA A: database SQL di Azure e Istanza gestita di SQL di Azure

Abilita o disabilita la raccolta di statistiche di esecuzione a livello di istruzione per i moduli di T-SQL compilati in modo nativo nel database corrente. Il valore predefinito è OFF. Le statistiche di esecuzione vengono riflesse in sys.dm_exec_query_stats e in Query Store.

Le statistiche di esecuzione a livello di istruzione per i moduli T-SQL compilati in modo nativo vengono raccolte se questa opzione è ON o se la raccolta di statistiche è abilitata mediante sp_xtp_control_query_exec_stats.

Per altre informazioni sul monitoraggio delle prestazioni dei moduli Transact-SQL compilati in modo nativo, vedere Monitoraggio delle prestazioni delle stored procedure compilate in modo nativo.

= ROW_MODE_MEMORY_GRANT_FEEDBACK { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare il feedback delle concessioni di memoria in modalità riga nell'ambito del database mantenendo comunque un livello di compatibilità del database pari a 150 o superiore. Concessione di memoria in modalità riga una funzionalità che fa parte dell'elaborazione di query intelligente introdotta in SQL Server 2017 (14.x) (modalità di riga è supportata in SQL Server 2019 (15.x) e database SQL di Azure).

Nota

Per il livello di compatibilità del database pari a 140 o inferiore, questa configurazione con ambito di database non ha effetto.

= BATCH_MODE_ON_ROWSTORE { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare la modalità batch per i rowstore nell'ambito del database mantenendo comunque un livello di compatibilità del database pari a 150 o superiore. La modalità batch per i rowstore è una funzionalità che fa parte della famiglia di funzionalità di elaborazione di query intelligenti.

Nota

Per il livello di compatibilità del database pari a 140 o inferiore, questa configurazione con ambito di database non ha effetto.

= DEFERRED_COMPILATION_TV { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare la compilazione posticipata delle variabili di tabella nell'ambito del database mantenendo comunque un livello di compatibilità del database pari a 150 o superiore. La compilazione posticipata delle variabili di tabella è una funzionalità che fa parte della famiglia di funzionalità di elaborazione di query intelligenti.

Nota

Per il livello di compatibilità del database pari a 140 o inferiore, questa configurazione con ambito di database non ha effetto.

ACCELERATED_PLAN_FORCING = { ON | OFF }

Si applica a: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Abilita un meccanismo ottimizzato per l'uso forzato del piano di query, applicabile a tutte le forme di uso forzato dei piani, ad esempio piani forzati da Query Store, l'ottimizzazione automatica o l'hint per la query USE PLAN. Il valore predefinito è ON.

Nota

Non è consigliabile disabilitare l'uso forzato del piano accelerato.

GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }

SI APPLICA A: database SQL di Azure e Istanza gestita di SQL di Azure

Consente di impostare la funzionalità di eliminazione automatica per le tabelle temporanee globali. Il valore predefinito è ON, il che significa che le tabelle temporanee globali vengono eliminate automaticamente quando non sono usate in nessuna sessione. Se impostato su OFF, le tabelle temporanee globali devono essere eliminate in modo esplicito usando un'istruzione DROP TABLE, altrimenti verranno eliminate automaticamente al riavvio del server.

  • Con database SQL di Azure database singoli e pool elastici, questa opzione può essere impostata nei singoli database utente del server database SQL.
  • In SQL Server e Istanza gestita di SQL di Azure questa opzione è impostata in TempDB e l'impostazione dei singoli database utente non ha alcun effetto.

= LIGHTWEIGHT_QUERY_PROFILING { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare l'infrastruttura leggera di profilatura query. L'infrastruttura leggera di profilatura query restituisce dati sulle prestazioni delle query in modo più efficiente rispetto ai meccanismi di profilatura standard ed è abilitata per impostazione predefinita.

= VERBOSE_TRUNCATION_WARNINGS { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare il nuovo messaggio di errore String or binary data would be truncated. SQL Server 2019 (15.x) introduce un nuovo messaggio di errore più specifico (2628) per questo scenario:

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

Se impostato su ON nel livello di compatibilità del database 150, gli errori di troncamento generano il nuovo messaggio di errore 2628 per offrire maggiore contesto e semplificare il processo di risoluzione dei problemi.

Se impostato su OFF nel livello di compatibilità del database 150, gli errori di troncamento generano il messaggio di errore 8152 precedente.

Per il livello di compatibilità del database 140 o inferiore, il 2628 rimane un messaggio di errore che prevede il consenso esplicito e richiede l'abilitazione del flag di traccia 460 e questa configurazione con ambito database non ha alcun effetto.

= LAST_QUERY_PLAN_STATS { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di abilitare o disabilitare la raccolta delle statistiche dell'ultimo piano di query (equivalente a un piano di esecuzione effettivo) in sys.dm_exec_query_plan_stats.

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES

SI APPLICA A: database SQL di Azure e Istanza gestita di SQL di Azure

L'opzione PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES determina la durata (in minuti) della sospensione dell'indice ripristinabile prima che venga interrotto automaticamente dal motore.

  • Il valore predefinito è impostato su 1 giorno (1440 minuti)
  • La durata minima è impostata su 1 minuto
  • La durata massima corrisponde a 71582 minuti
  • Se il valore è impostato su 0, un'operazione sospesa non viene mai interrotta automaticamente

Il valore corrente di questa opzione è visualizzato in sys.database_scoped_configurations.

ISOLATE_SECURITY_POLICY_CARDINALITY = { ON | OFF}

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Consente di controllare se un predicato di Sicurezza a livello di riga influisce sulla cardinalità del piano di esecuzione della query utente complessiva. Quando ISOLATE_SECURITY_POLICY_CARDINALITY è impostato su ON, un predicato di Sicurezza a livello di riga non influisce sulla cardinalità di un piano di esecuzione. Si considerino, ad esempio, una tabella contenente 1 milione di righe e un predicato di Sicurezza a livello di riga che limita il risultato a 10 righe per un utente specifico che esegue la query. Se questa configurazione con ambito di database è impostata su OFF, la stima della cardinalità di questo predicato sarà 10. Se invece è impostata su ON, l'ottimizzazione delle query stimerà 1 milione righe. È consigliabile usare il valore predefinito per la maggior parte dei carichi di lavoro.

DW_COMPATIBILITY_LEVEL = {AUTO | 10 | 20 }

SI APPLICA A: solo Azure Synapse Analytics

Imposta i comportamenti di Transact-SQL e dell'elaborazione delle query in modo che risultino compatibili con la versione specificata del motore di database. Una volta impostata, quando viene eseguita una query su tale database, verranno applicate solo le funzionalità compatibili. Per impostazione predefinita, il livello di compatibilità di un database viene impostato su AUTO al momento della creazione. Il livello di compatibilità viene mantenuto anche dopo operazioni di sospensione/ripresa o backup/ripristino del database.

Livello di compatibilità Commenti
AUTO Valore predefinito. Il valore viene aggiornato automaticamente dal motore di Synapse Analytics. Il valore corrente è 20.
10 Applica i comportamenti Transact-SQL e di elaborazione delle query prima dell'introduzione del supporto del livello di compatibilità.
20 Primo livello di compatibilità che include i comportamenti di elaborazione delle query e Transact-SQL controllati.

= EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS { ON | OFF }

SI APPLICA A: database SQL di Azure e Istanza gestita di SQL di Azure

Consente di controllare se le statistiche di esecuzione per le funzioni definite dall'utente scalari vengono visualizzate nella visualizzazione di sistema sys.dm_exec_function_stats . Per alcuni carichi di lavoro con un numero elevato di funzioni definite dall'utente scalari, la raccolta delle statistiche di esecuzione delle funzioni può causare un notevole sovraccarico delle prestazioni. Questa operazione può essere evitata impostando la EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS configurazione con ambito database su OFF.

= ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY { ON | OFF }

SI APPLICA A: database SQL di Azure e Istanza gestita di SQL di Azure

Se è attivo l'aggiornamento asincrono delle statistiche, con questa configurazione abilitata, la richiesta in background di aggiornamento delle statistiche resterà in attesa di un blocco di modifica dello schema in una coda separata con priorità bassa, in modo da evitare il blocco di altre sessioni in scenari di concorrenza elevata. Per altre informazioni, vedere AUTO_UPDATE_STATISTICS_ASYNC.

Autorizzazioni

Richiede ALTER ANY DATABASE SCOPED CONFIGURATION per il database. Questa autorizzazione può essere concessa da un utente con CONTROL autorizzazione per un database.

Osservazioni generali

Tutti i database secondari usano la stessa configurazione, nonostante sia possibile configurarli in modo che abbiamo impostazioni di configurazione con ambiti diversi rispetto al database primario. Non è possibile configurare impostazioni diverse per singoli database secondari.

Se viene eseguita questa istruzione, viene cancellata la cache delle procedure nel database corrente, il che significa che è necessario ricompilare tutte le query.

Per le query con nome in 3 parti, vengono prese in considerazione le impostazioni per la connessione al database corrente per la query, a differenza dei moduli SQL (ad esempio procedure, funzioni e trigger) che vengono compilati nel contesto di un altro database e quindi usano le opzioni del database in cui risiedono. Analogamente, quando si aggiornano le statistiche in modo asincrono, viene rispettata l'impostazione di ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY relativa al database in cui risiedono le statistiche.

L'evento ALTER_DATABASE_SCOPED_CONFIGURATION viene aggiunto come evento DDL che può essere usato per attivare un trigger DDL ed è elemento figlio del gruppo di trigger ALTER_DATABASE_EVENTS.

Le impostazioni di configurazione con ambito database verranno trasferite con il database, il che significa che quando un determinato database viene ripristinato o collegato, le impostazioni di configurazione esistenti rimangono.

A partire da SQL Server 2019 (15.x), in database SQL di Azure e Istanza gestita di SQL di Azure, alcuni nomi di opzione sono stati modificati:

  • DISABLE_INTERLEAVED_EXECUTION_TVF è diventata INTERLEAVED_EXECUTION_TVF
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK è diventata BATCH_MODE_MEMORY_GRANT_FEEDBACK
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS è diventata BATCH_MODE_ADAPTIVE_JOINS

Limitazioni e restrizioni

MAXDOP

Le impostazioni granulari possono sostituire quelle globali e Resource Governor può limitare tutte le altre impostazioni MAXDOP. La logica per l'impostazione MAXDOP è la seguente:

  • L'hint per la query sostituisce sia sp_configure, sia la configurazione con ambito database. Se il parametro MAXDOP del gruppo di risorse è impostato per il gruppo di carico di lavoro:

    • Se l'hint per la query è impostato su zero (0), viene sostituito dall'impostazione di Resource Governor.

    • Se l'hint per la query non è zero (0), viene limitato dall'impostazione di Resource Governor.

  • La configurazione con ambito database, a meno che non sia zero, sostituisce l'impostazione sp_configure, sempre che non sia presente un hint per la query e non vi sia un limite di impostazione di Resource Governor.

  • L'impostazione sp_configure viene sostituita dall'impostazione di Resource Governor.

QUERY_OPTIMIZER_HOTFIXES

Quando QUERYTRACEON si usa l'hint per abilitare Query Optimizer predefinito di SQL Server 7.0 fino a SQL Server 2012 (11.x) versioni o aggiornamenti rapidi di Query Optimizer, si tratta di una condizione OR tra l'hint per la query e l'impostazione di configurazione con ambito database, ovvero se sono abilitate, si applicano le configurazioni con ambito database.

Geo DR

I database secondari leggibili (Always On gruppi di disponibilità, database SQL di Azure e Istanza gestita di SQL di Azure database con replica geografica), usano il valore secondario controllando lo stato del database. Anche se la ricompilazione non avviene in caso di failover e tecnicamente il nuovo database primario contiene le query che usano le impostazioni del database secondario, l'idea è che l'impostazione tra primario e secondario vari solo quando il carico di lavoro è diverso e pertanto le query memorizzate nella cache usino le impostazioni ottimali, mentre le nuove query selezionino le nuove impostazioni adatte a loro.

DacFx

Poiché ALTER DATABASE SCOPED CONFIGURATION è una nuova funzionalità in database SQL di Azure, Istanza gestita di SQL di Azure e SQL Server (a partire da SQL Server 2016 (13.x)) che influisce sullo schema del database, le esportazioni dello schema (con o senza dati) non possono essere importate in una versione precedente di SQL Server, ad esempio SQL Server 2012 (11,x) o SQL Server 2014 (12,x). Ad esempio, un'esportazione in un file DACPAC o bacPAC da un database SQL o SQL Server 2016 (13.x) che usa questa nuova funzionalità non può essere importata in un server di livello inferiore.

ELEVATE_ONLINE

Questa opzione si applica solo alle istruzioni DDL che supportano la sintassi WITH (ONLINE = <syntax>). Gli indici XML non sono interessati.

ELEVATE_RESUMABLE

Questa opzione si applica solo alle istruzioni DDL che supportano la sintassi WITH (RESUMABLE = <syntax>). Gli indici XML non sono interessati.

Metadati

La vista di sistema sys.database_scoped_configurations (Transact-SQL) fornisce informazioni sulle configurazioni con ambito all'interno di un database. Le opzioni di configurazione con ambito database vengono visualizzate solo in sys.database_scoped_configurations in quanto vengono sostituite dalle impostazioni predefinite a livello di server. La visualizzazione di sistema sys.configurations (Transact-SQL) mostra solo le impostazioni a livello di server.

Esempi

In questi esempi viene illustrato l'uso di ALTER DATABASE SCOPED CONFIGURATION

R. Concessione dell'autorizzazione

In questo esempio viene concessa all'utente Joe l'autorizzazione necessaria per eseguire ALTER DATABASE SCOPED CONFIGURATION.

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;

B. Impostare MAXDOP

In questo esempio viene impostato il parametro MAXDOP = 1 per un database primario e MAXDOP = 4 per un database secondario in uno scenario di replica geografica.

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

In questo esempio viene impostato il parametro MAXDOP per un database secondario in modo che corrisponda a quello impostato per il database primario in uno scenario di replica geografica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY ;

C. Impostare LEGACY_CARDINALITY_ESTIMATION

In questo esempio il parametro LEGACY_CARDINALITY_ESTIMATION viene impostato su ON per un database secondario in uno scenario di replica geografica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON ;

In questo esempio viene impostato il parametro LEGACY_CARDINALITY_ESTIMATION per un database secondario come nel database primario in uno scenario di replica geografica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY ;

D. Impostare PARAMETER_SNIFFING

In questo esempio il parametro PARAMETER_SNIFFING viene impostato su OFF per un database primario in uno scenario di replica geografica.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF ;

In questo esempio il parametro PARAMETER_SNIFFING viene impostato su OFF per un database secondario in uno scenario di replica geografica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF ;

In questo esempio viene impostato il parametro PARAMETER_SNIFFING per un database secondario come nel database primario in uno scenario di replica geografica.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY ;

E. Impostare QUERY_OPTIMIZER_HOTFIXES

Impostare il parametro QUERY_OPTIMIZER_HOTFIXES su ON per un database primario in uno scenario di replica geografica.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;

F. Cancellare la cache delle procedure

In questo esempio viene cancellata la cache delle procedure. È possibile solo per un database primario.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

G. Impostare IDENTITY_CACHE

SI APPLICA A: SQL Server (a partire da SQL Server 2017 (14.x)), database SQL di Azure e Istanza gestita di SQL di Azure

In questo esempio viene disabilitata la cache Identity.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF ;

H. Impostare OPTIMIZE_FOR_AD_HOC_WORKLOADS

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

In questo esempio viene abilitato uno stub del piano compilato da memorizzare nella cache quando un batch viene compilato per la prima volta.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

I. Impostare ELEVATE_ONLINE

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

In questo esempio viene impostato il parametro ELEVATE_ONLINE su FAIL_UNSUPPORTED.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED ;

J. Impostare ELEVATE_RESUMABLE

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), database SQL di Azure e Istanza gestita di SQL di Azure

In questo esempio viene impostato il parametro ELEVATE_RESUMABLE su WHEN_SUPPORTED.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED ;

K. Cancellare un piano di query dalla cache dei piani

SI APPLICA A: SQL Server (a partire da SQL Server 2017 (14.x)), database SQL di Azure e Istanza gestita di SQL di Azure

Questo esempio cancella un piano specifico dalla cache delle procedure

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000500F443610F003B7CD12C02000001000000000000000000000000000000000000000000000000000000;

L. Impostare la durata della sospensione

SI APPLICA A: database SQL di Azure e Istanza gestita di SQL di Azure

Questo esempio imposta la durata della sospensione di un indice ripristinabile su 60 minuti.

ALTER DATABASE SCOPED CONFIGURATION
SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 60

Risorse aggiuntive

Risorse di MAXDOP

Risorse di LEGACY_CARDINALITY_ESTIMATION

Risorse di PARAMETER_SNIFFING

Risorse di QUERY_OPTIMIZER_HOTFIXES

Risorse di ELEVATE_ONLINE

Linee guida per le operazioni sugli indici online

Risorse di ELEVATE_RESUMABLE

Linee guida per le operazioni sugli indici online

Ulteriori informazioni

sys.database_scoped_configurations
Indicazioni e linee guida per l'opzione di configurazione "max degree of parallelism" in SQL Server
sys.configurations
Viste del catalogo di database e file
Opzioni di configurazione del server
Funzionamento delle operazioni sugli indici online
Eseguire operazioni online sugli indici
ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)