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 Azure SQLAnalytics Platform System (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 di piano compilato interrogando la vista del catalogo sys.dm_exec_cached_plans e cercando "Compiled Plan" nella colonna cacheobjtype. Lo stub ha un valore plan_handle univoco. Lo stub del piano compilato non ha un piano di esecuzione ad esso associato e l'interrogazione dell'handle del piano non restituisce uno showplan grafico o 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 entrano nella cache e quando l'opzione optimize for ad hoc workloads non ha risolto il problema della cache dei piani di esecuzione.
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 la cache dei piani usando ALTER DATABASE SCOPED CONFIGURATION clear PROCEDURE_CACHE o SQL Server deve essere riavviata.
Consigli
Evitare di avere un gran numero di piani a uso singolo nella cache dei piani. Le cause comuni includono:
Tipi di dati dei 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. Potrebbero esistere piani per ogni combinazione di tipi di dati e lunghezze utilizzate per i parametri di query.
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 a causa della sensibilità ai 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;