Ottimizza per carichi di lavoro ad hoc (opzione di configurazione del server)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics (PDW)

L'opzione optimize for ad hoc workloads consente di migliorare l'efficienza della cache dei piani per carichi di lavoro che contengono molti batch ad hoc a uso singolo. Quando questa opzione viene impostata su 1, alla prima compilazione di un batch il motore di database archivia un piccolo stub del piano compilato nella cache dei piani, anziché il piano compilato completo. Con questa opzione è possibile ridurre le richieste di memoria evitando che la cache dei piani si riempia con piani compilati che non vengono riusati. Tuttavia, l'abilitazione di questa opzione può influire sulla possibilità di risolvere i problemi dei piani a uso singolo.

Lo stub del piano compilato consente al motore di database di riconoscere che il batch ad hoc è stato compilato in precedenza e archivia solo uno stub del piano compilato. Quando il batch viene richiamato (compilato o eseguito di nuovo), il motore di database compila il batch, rimuove lo stub del piano compilato dalla cache dei piani e aggiunge il piano compilato completo alla cache dei piani.

È possibile trovare gli stub del piano compilati eseguendo query sulla vista del catalogo sys.dm_exec_cached_plans e cercando "Piano compilato" nella colonna cacheobjtype. Lo stub ha un valore plan_handle univoco. Lo stub del piano compilato non ha un piano di esecuzione associato e l'esecuzione di query per l'handle del piano non restituisce uno showplan XML.

Il flag di traccia 8032 ripristina i parametri dei limiti di cache all'impostazione RTM di SQL Server 2005 (9.x), che consente, in generale, di aumentare le dimensioni delle cache. Usare questa impostazione quando le voci della cache riusate di frequente non rientrano nella cache e quando l'opzione Ottimizza per carichi di lavoro ad hoc non riesce a risolvere il problema della cache dei piani.

Avviso

Il flag di traccia 8032 può provocare prestazioni ridotte se cache di grandi dimensioni rendono disponibile meno memoria per altri consumer di memoria, ad esempio il pool di buffer.

Osservazioni:

L'impostazione dell'opzione Ottimizza per carichi di lavoro ad hoc su 1 influisce solo sui nuovi piani, mentre non si applica ai piani già presenti nella cache dei piani.

Per influire immediatamente sui piani di query già memorizzati nella cache, è necessario cancellare il contenuto della cache dei piani usando ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE oppure riavviare SQL Server.

Consigli

Evitare la presenza di un numero elevato di piani a uso singolo nella cache dei piani. I casi d'uso comuni comprendono i seguenti:

  • Tipi di dati di parametri di query non definiti in modo coerente. Questo è vero specificamente per la lunghezza delle stringhe ma è applicabile a qualsiasi tipo di dati che dispone di una lunghezza massima, una precisione o una scala. Se ad esempio un parametro con nome @Greeting viene passato come nvarchar(10) in una chiamata e come nvarchar(20) nella chiamata seguente, vengono creati piani separati per ogni dimensione del parametro.

  • Query senza parametri. Se una query ha uno o più parametri per i quali vengono inviati valori hardcoded al motore di database, per ogni query potrebbe esistere un numero elevato di piani di query. È possibile che esistano piani per ogni combinazione di tipi di dati dei parametri di query e lunghezze.

Se il numero di piani a uso singolo occupa una parte significativa della memoria del motore di database di SQL Server in un server OLTP e questi piani sono piani ad-hoc, usare questa opzione server per ridurre l'uso della memoria con questi oggetti.

Se l'opzione Ottimizza per carichi di lavoro ad hoc è abilitata, non è possibile visualizzare i piani di esecuzione per le query a uso singolo, perché viene memorizzato nella cache solo lo stub del piano. A seconda dell'ambiente e del carico di lavoro, è possibile trarre vantaggio dalle due funzionalità seguenti:

  • La funzionalità Query Store, introdotta in SQL Server 2016 (13.x), consente di individuare rapidamente le differenze di prestazioni causate dalle modifiche apportate al piano di query. Query Store è abilitata per impostazione predefinita nei nuovi database in SQL Server 2022 (16.x) e versioni successive.

  • La parametrizzazione forzata può offrire un miglioramento delle prestazioni di alcuni database riducendo la frequenza delle operazioni di compilazione e ricompilazione delle query. I database che possono essere soggetti a un miglioramento delle prestazione grazie alla parametrizzazione forzata generalmente ricevono volumi elevati di query simultanee da origini quali le applicazioni POS.

    La parametrizzazione forzata può causare problemi di prestazioni per l’importanza dei parametri. Per altre informazioni, vedere Analizzare e risolvere i problemi relativi ai parametri. Per SQL Server 2022 (16.x) e versioni successive, è anche possibile abilitare l'Ottimizzazione del piano sensibile ai parametri.

Esempi

Per trovare il numero di piani a uso singolo memorizzati nella cache, eseguire la query seguente:

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Contenuto correlato