ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azuredatabase SQL di Azure Synapse Analyticsin Microsoft Fabric

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

Le impostazioni seguenti sono supportate in database SQL di Azure, database SQL in Microsoft Fabric, 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, scegliendo il valore più appropriato per lo specifico carico di lavoro, e impostare un valore diverso per i database di replica secondari usati per le query di report. Per indicazioni sulla scelta di un valore per il parametro 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 T-SQL compilati a livello 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 la funzionalità di attivazione 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 viene sospesa un'operazione di indice ripristinabile sospesa prima che venga interrotta automaticamente dal motore di database.
• Abilitare o disabilitare l'attesa per i blocchi a priorità bassa per l'aggiornamento asincrono delle statistiche.
• Abilitare o disabilitare il caricamento dei digest libro mastro in Archiviazione BLOB di Azure.

Questa impostazione è disponibile solo in Azure Synapse Analytics.

• Impostare il livello di compatibilità di un database utente

Convenzioni relative alla 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 }
    | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }
    | MEMORY_GRANT_FEEDBACK_PERSISTENCE = { 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 }
    | OPTIMIZED_PLAN_FORCING = { ON | OFF }
    | DOP_FEEDBACK = { ON | OFF }
    | CE_FEEDBACK = { ON | OFF }
    | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }
    | LEDGER_DIGEST_STORAGE_ENDPOINT = { <endpoint URL string> | OFF }
    | OPTIMIZED_SP_EXECUTESQL = { ON | OFF }
}

-- Syntax for Azure Synapse Analytics

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

< set_options > ::=
{
    DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 | 30 | 40 | 50 | 9000 }
}

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 del 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> | PRIMARY }

<value>

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. MAXDOP nell'ambito del database esegue l'override (a meno che non sia impostato su 0) il grado massimo 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.

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

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.

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.

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

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.

INTERLEAVED_EXECUTION_TVF = { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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. Il valore predefinito è ON. L'esecuzione interleaved è una funzionalità che fa parte dell'elaborazione di query adattive disponibile nel database SQL di Azure. Per altre informazioni, vedere Elaborazione di query intelligenti.

BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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. Il valore predefinito è ON. Il feedback delle concessioni di memoria in modalità batch, introdotto in SQL Server 2017 (14.x), fa parte della suite intelligente di funzionalità di elaborazione di query. Per altre informazioni, vedere Feedback delle concessioni di memoria .

BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF}

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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. Il valore predefinito è ON. I join adattivi in modalità batch fanno parte della famiglia di funzionalità Elaborazione di query intelligenti introdotta in SQL Server 2017 (14.x).

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)

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. Il valore predefinito è ON. L'inlining di funzioni definite dall'utente scalari T-SQL fa parte della famiglia di funzionalità di elaborazione di query intelligenti.

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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 hanno esito negativo e generano un errore.

WHEN_SUPPORTED

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

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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 la ripresa vengono eseguite in modo non ripristinabile.

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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, 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. Il valore predefinito è ON. Feedback delle concessioni di memoria in modalità riga una funzionalità che fa parte dell'elaborazione intelligente delle query introdotta in SQL Server 2017 (14.x). La modalità riga è supportata in SQL Server 2019 (15.x) e database SQL di Azure. Per altre informazioni sul feedback sulle concessioni di memoria, vedere Commenti e suggerimenti sulle concessioni di memoria.

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }

Si applica a: SQL Server (a partire da SQL Server 2022 (16.x) SP1) e al database SQL di Azure

Consente di disabilitare il percentile del feedback delle concessioni di memoria per tutte le esecuzioni di query provenienti dal database. Il valore predefinito è ON. Per informazioni complete, vedere Feedback sulle concessioni di memoria in modalità percentile e persistenza.

MEMORY_GRANT_FEEDBACK_PERSISTENCE = { ON | OFF }

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

Consente di disabilitare la persistenza del feedback delle concessioni di memoria per tutte le esecuzioni di query provenienti dal database. Il valore predefinito è ON. Per informazioni complete, vedere Feedback sulle concessioni di memoria in modalità percentile e persistenza.

BATCH_MODE_ON_ROWSTORE = { ON | OFF}

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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. Il valore predefinito è ON. La modalità batch per i rowstore è una funzionalità che fa parte della famiglia di funzionalità di elaborazione di query intelligenti.

DEFERRED_COMPILATION_TV = { ON | OFF}

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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. Il valore predefinito è ON. La compilazione posticipata delle variabili di tabella è una funzionalità che fa parte della famiglia di funzionalità di elaborazione di query intelligenti.

ACCELERATED_PLAN_FORCING = { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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.

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à autodrop per le tabelle temporanee globali. Il valore predefinito è ON, il che significa che le tabelle temporanee globali vengono eliminate automaticamente quando non vengono usate da alcuna sessione. Se impostata su OFF, le tabelle temporanee globali devono essere eliminate in modo esplicito tramite un'istruzione DROP TABLE o verranno eliminate automaticamente al riavvio del server.

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

LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }

SI APPLICA A: SQL Server (a partire da SQL Server 2019 (15.x)), Database SQL di Azure, 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. Il valore predefinito è ON.

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

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

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

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, 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. Il valore predefinito è OFF.

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES

Si applica a: SQL Server (a partire da SQL Server 2022 (16.x)), 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 un giorno (1440 minuti)
• La durata minima è impostata su 1 minuto
• La durata massima è di 71.582 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, 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. Il valore predefinito è OFF. 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. Quando la configurazione con ambito database è ON, l'ottimizzazione delle query stima 1 milione di righe. È consigliabile usare il valore predefinito per la maggior parte dei carichi di lavoro.

DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 | 30 | 40 | 50 | 9000 }

Si applica solo 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, vengono esercitate solo le funzionalità compatibili. A ogni livello di compatibilità, sono supportati diversi miglioramenti dell'elaborazione delle query. Ogni livello assorbe la funzionalità del livello precedente. Per impostazione predefinita, il livello di compatibilità di un database viene impostato su AUTO al momento della creazione. Si tratta dell'impostazione consigliata. Il livello di compatibilità viene mantenuto anche dopo operazioni di sospensione/ripresa o backup/ripristino del database. Il valore predefinito è AUTO.

Livello di compatibilità	Commenti
AUTO	Predefinito. Il valore viene aggiornato automaticamente dal motore di Synapse Analytics ed è rappresentato da 0 in sys.database_scoped_configurations. AUTO esegue attualmente il mapping alla funzionalità di livello di compatibilità 30 .
10	Esegue gli esercizi sui comportamenti del motore di query e Transact-SQL prima dell'introduzione del supporto del livello di compatibilità.
20	Primo livello di compatibilità che include comportamenti del motore di query e Transact-SQL gestiti. La stored procedure di sistema sp_describe_undeclared_parameters è supportata in questo livello.
30	Include nuovi comportamenti del motore di query.
40	Include nuovi comportamenti del motore di query.
50	La distribuzione a più colonne è supportata in questo livello. Per altre informazioni, vedere CREATE TABLE, CREATE TABLE AS SELECT e CREATE MATERIALIZED VIEW.
9000	Livello di compatibilità dell'anteprima. Le funzionalità di anteprima incluse in questo livello sono riportate nella documentazione specifica della funzionalità. Questo livello include anche le abilità di livello non 9000 più alto.

EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }

si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

Consente di controllare se le statistiche di esecuzione delle funzioni definite dall'utente (UDF) di tipo scalare vengono riportate nella vista di sistema sys.dm_exec_function_stats. Per alcuni carichi di lavoro a elevato utilizzo di funzioni scalari, la raccolta di statistiche di esecuzione delle funzioni può causare un notevole sovraccarico delle prestazioni. Per evitare questa situazione, impostare la configurazione con ambito database EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS su OFF. Il valore predefinito è ON.

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }

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

Se l'aggiornamento asincrono delle statistiche è abilitato, l'abilitazione di questa configurazione fa sì che la richiesta in background aggiorni le statistiche in attesa di un Sch-M blocco in una coda con priorità bassa, per evitare di bloccare altre sessioni in scenari di concorrenza elevata. Per altre informazioni, vedere AUTO_UPDATE_STATISTICS_ASYNC. Il valore predefinito è OFF.

OPTIMIZED_PLAN_FORCING = { ON | OFF }

Si applica a: SQL Server (a partire da SQL Server 2022 (16.x) SP1) e al database SQL di Azure

La forzatura del piano ottimizzato riduce il sovraccarico di compilazione per le query ripetitive imposte. Il valore predefinito è ON. Al completamento della generazione del piano di esecuzione delle query, alcuni specifici passaggi di compilazione vengono archiviati in modo che sia possibile riusarli come script per l'ottimizzazione della riproduzione. Uno script di ottimizzazione della riproduzione viene archiviato come parte del file XML dello showplan compresso in Query Store, in un attributo OptimizationReplay nascosto. Per altre informazioni, vedere Forzatura del piano ottimizzato con Query Store.

DOP_FEEDBACK = { ON | OFF }

Si applica a: SQL Server (a partire da SQL Server 2022 (16.x) SP1) e al database SQL di Azure

Identifica le inefficienze del parallelismo per le query ripetitive, in base al tempo trascorso e alle attese. Se l'uso del parallelismo è considerato inefficiente, il feedback del grado di parallelismo (DOP) riduce il grado di parallelismo per la successiva esecuzione della query, indipendentemente dal valore DOP configurato, e verifica se la riduzione ha migliorato l'efficienza. Richiede Query Store abilitato e in modalità READ_WRITE. Per altre informazioni, vedere Feedback del grado di parallelismo (DOP). Il valore predefinito è OFF.

CE_FEEDBACK = { ON | OFF }

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

Il feedback di stima di cardinalità risolve i problemi di regressione percepiti derivanti da presupposti non corretti del modello stima di cardinalità quando si usa la stima di cardinalità predefinita (CE120 o superiore) e può applicare in modo selettivo presupposti di modello diversi. Richiede Query Store abilitato e in modalità READ_WRITE. Per altre informazioni, vedere Feedback sulla stima della cardinalità (CE). Il valore predefinito è ON nel livello di compatibilità del database 160 e versioni successive.

PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }

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

L'ottimizzazione del piano di riservatezza dei parametri risolve lo scenario in cui un singolo piano memorizzato nella cache per una query con parametri non è ottimale per tutti i possibili valori dei parametri in ingresso. Questa situazione si verifica in caso di distribuzioni di dati non uniformi. Il valore predefinito è ON a partire dal livello di compatibilità del database 160. Per altre informazioni, vedere Ottimizzazione del piano sensibile ai parametri.

LEDGER_DIGEST_STORAGE_ENDPOINT = { <endpoint URL string> | OFF }

Si applica a: SQL Server, a partire da SQL Server 2022 (16.x)

Abilita o disabilita il caricamento di digest libro mastro in Archiviazione BLOB di Azure. Per abilitare il caricamento dei digest del libro mastro, specificare l'endpoint di un account di archiviazione BLOB di Azure. Per disabilitare il caricamento dei digest del libro mastro, impostare il valore dell'opzione su OFF. Il valore predefinito è OFF.

FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = { ON | OFF }

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

Fa sì che SQL Server generi un frammento Showplan XML con ParameterRuntimeValue quando si usa l'infrastruttura di profilatura leggera delle statistiche di esecuzione delle query o l'esecuzione della DMV durante la sys.dm_exec_query_statistics_xml risoluzione dei problemi relativi alle query a esecuzione prolungata.

OPTIMIZED_SP_EXECUTESQL = { ON | OFF }

Si applica a: Database SQL di Azure

Abilita o disabilita il comportamento di serializzazione della compilazione di sp_executesql quando viene compilato un batch. Il valore predefinito è OFF. Consentire ai batch che usano sp_executesql per serializzare il processo di compilazione riduce l'impatto delle tempeste di compilazione. Una tempesta di compilazione si riferisce a una situazione in cui un numero elevato di query vengono compilate contemporaneamente, causando problemi di prestazioni e contesa delle risorse.

Quando OPTIMIZED_SP_EXECUTESQL è ON, la prima esecuzione di sp_executesql compilerà e inserirà il piano compilato nella cache dei piani. Altre sessioni interrompino l'attesa del blocco di compilazione e riutilizzano il piano una volta che diventa disponibile. Ciò consente sp_executesql di comportarsi come oggetti come stored procedure e trigger dal punto di vista della compilazione.

Autorizzazioni

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

Osservazioni:

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 tre parti, le impostazioni per la connessione al database corrente per la query vengono rispettate, ad eccezione dei moduli SQL (ad esempio procedure, funzioni e trigger) compilati in un altro contesto di 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 per il 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.

Quando un determinato database viene ripristinato o collegato, le impostazioni di configurazione con ambito database vengono eseguite e rimangono con il database.

A partire da SQL Server 2019 (15.x), in Azure SQL Database e in 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

Nel database SQL in Microsoft Fabric l'autenticazione avviee tramite il pass-through "USER IDENTITY" dell'ID Entra di Microsoft.

Limiti

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 l'hint QUERYTRACEON viene usato per abilitare l'istanza predefinita di Query Optimizer delle versioni di SQL Server da SQL Server 7.0 a SQL Server 2012 (11.x) o gli aggiornamenti di Query Optimizer, tra l'hint per la query e l'impostazione di configurazione con ambito database sarà presente una condizione OR per indicare che, se una qualsiasi delle due opzioni è abilitata, vengono applicate le configurazioni con ambito database.

Geo DR

I database secondari leggibili (gruppi di disponibilità Always On, database con replica geografica di Database SQL di Azure e Istanza gestita di SQL di Azure) usano il valore secondario verificando 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à di Database SQL di Azure, Istanza gestita di SQL di Azure e di 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, quale SQL Server 2012 (11.x) o SQL Server 2014 (12.x). Ad esempio, un'esportazione in DACPAC o BACPAC da un database di SQL o di SQL Server 2016 (13.x) in cui sia stata usata la nuova funzionalità non può essere importata in un server precedente.

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 UFX

Nella vista di sistema sys.database_scoped_configurations (Transact-SQL) sono riportate informazioni sulle configurazioni con ambito incluse in un database. Le opzioni di configurazione con ambito database vengono visualizzate solo quando sys.database_scoped_configurations vengono sostituite con le impostazioni predefinite a livello di server. Nella vista di sistema sys.configurations (Transact-SQL) sono disponibili 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, 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, 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, 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 2019 (15.x)), Database SQL di Azure, 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

M. Abilitare e disabilitare il caricamento dei digest del libro mastro

Si applica a: SQL Server, a partire da SQL Server 2022 (16.x)

In questo esempio viene abilitato il caricamento dei digest del libro mastro in un account di archiviazione di Azure.

ALTER DATABASE SCOPED CONFIGURATION
SET LEDGER_DIGEST_STORAGE_ENDPOINT = 'https://mystorage.blob.core.windows.net'

In questo esempio viene disabilitato il caricamento dei digest del libro mastro.

ALTER DATABASE SCOPED CONFIGURATION
SET LEDGER_DIGEST_STORAGE_ENDPOINT = OFF

Risorse aggiuntive

Risorse di MAXDOP

• Grado di parallelismo
• Indicazioni e linee guida per l'opzione di configurazione "max degree of parallelism" in SQL Server

Risorse di LEGACY_CARDINALITY_ESTIMATION

• Stima della cardinalità (SQL Server)
• Ottimizzazione dei piani di query con la funzionalità di stima di cardinalità di SQL Server 2014

Risorse di PARAMETER_SNIFFING

• Analisi dei parametri
• "I smell a parameter!" (Uso dei parametri)

Risorse di QUERY_OPTIMIZER_HOTFIXES

• Flag di traccia
• SQL Server query optimizer hotfix trace flag 4199 servicing model(Modello di manutenzione del flag di traccia 4199 di Query Optimizer in SQL Server)

Risorse di ELEVATE_ONLINE

Linee guida per le operazioni sugli indici online

Risorse di ELEVATE_RESUMABLE

Linee guida per le operazioni sugli indici online

Contenuto correlato

• sys.database_scoped_configurations
• sys.configurations
• Viste del catalogo di database e file (Transact-SQL)
• Opzioni di configurazione del server (SQL Server)
• ALTER INDEX (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• Indicazioni e linee guida per l'opzione di configurazione "max degree of parallelism" in SQL Server
• Funzionamento delle operazioni sugli indici online
• Eseguire operazioni online sugli indici
• Elaborazione di query intelligenti nei database SQL
• Feedback della concessione di memoria
• Feedback sulla stima di cardinalità (CE)
• Feedback sul grado di parallelismo (DOP)