Nota
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare ad accedere o modificare le directory.
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare a modificare le directory.
Si applica a:
SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics (PDW)
L'opzione optimize for ad hoc workloads viene usata per migliorare l'efficienza della cache dei piani per i carichi di lavoro che contengono molti batch ad hoc per 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 limite della cache all'impostazione RTM di SQL Server 2005 (9.x), che in generale consente di aumentare le dimensioni delle cache. Usare questa impostazione quando le voci della cache riutilizzate di frequente non rientrano nella cache e quando l'opzione non è riuscita a risolvere il problema con la optimize for ad hoc workloads cache dei piani.
Avviso
Il flag di traccia 8032 può causare prestazioni scarse se le cache di grandi dimensioni limitano la memoria disponibile per altri consumatori di memoria, ad esempio il pool di buffer.
Osservazioni:
L'impostazione dell'opzione optimize for ad hoc workloads su 1 influisce solo sui nuovi piani. I piani già presenti nella cache dei piani non sono interessati.
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
@Greetingviene 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 optimize for ad hoc workloads è 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;