• Articolo

Memorizzazione nella cache e riutilizzo del piano di esecuzione

In SQL Server è presente un pool di memoria utilizzato per archiviare sia i piani di esecuzione che i buffer dei dati. La percentuale del pool allocata ai piani di esecuzione o ai buffer dei dati varia dinamicamente in base allo stato del sistema. La parte del pool di memoria utilizzata per archiviare i piani di esecuzione è denominata cache delle procedure.

I piani di esecuzione di SQL Server includono i componenti principali seguenti:

  • Piano della query

    La parte centrale del piano di esecuzione è una struttura di dati rientrante di sola lettura che può essere utilizzata da un numero qualsiasi di utenti. Questo elemento è detto piano della query. Nel piano della query non viene archiviato alcun contesto utente. In memoria non vi sono mai più di una o due copie del piano della query: una copia per tutte le esecuzioni seriali e una per tutte le esecuzioni parallele. La copia parallela copre tutte le esecuzioni parallele, indipendentemente dal loro grado di parallelismo.

  • Contesto di esecuzione

    Ogni utente che esegue la query dispone di una struttura di dati contenente i dati specifici per l'esecuzione, ad esempio i valori dei parametri. Questa struttura di dati è denominata contesto di esecuzione. Le strutture di dati del contesto di esecuzione vengono riutilizzate. Se un utente esegue una query e una delle strutture non è in uso, questa viene reinizializzata con il contesto del nuovo utente.

Contesto di esecuzione, stessa query, valori letterali diversi

Quando in SQL Server viene eseguita un'istruzione SQL, tramite il motore relazionale viene prima eseguita una ricerca nella cache delle procedure per verificare la presenza di un piano di esecuzione esistente per la stessa istruzione SQL. Se viene trovato un piano, questo viene riutilizzato in SQL Server, consentendo di evitare l'overhead associato alla ricompilazione dell'istruzione SQL. Se non esiste già un piano di esecuzione, in SQL Server viene generato un nuovo piano per la query.

In SQL Server è disponibile un algoritmo efficiente per l'individuazione di piani di esecuzione esistenti per una specifica istruzione SQL. Nella maggior parte dei sistemi, le risorse minime utilizzate da questa analisi sono inferiori a quelle risparmiate riutilizzando i piani esistenti anziché compilando ogni istruzione SQL.

Per gli algoritmi che consentono di trovare la corrispondenza tra le nuove istruzioni SQL e i piani di esecuzione esistenti inutilizzati nella cache è necessario che i riferimenti agli oggetti siano completi. Per la prima delle istruzioni SELECT seguenti, ad esempio, non viene trovata la corrispondenza con un piano esistente, come avviene invece per la seconda:

SELECT * FROM Person;

SELECT * FROM Person.Person;

Rimozione di piani di esecuzione dalla cache delle procedure

I piani di esecuzione rimangono nella cache delle procedure fino a quando è disponibile memoria sufficiente per archiviarli. In caso di un numero eccessivo di richieste di memoria, il Motore di database utilizza un approccio basato sui costi per determinare i piani di esecuzione da rimuovere dalla cache delle procedure. Per prendere una decisione basata sui costi, il Motore di database incrementa e decrementa una variabile relativa al costo corrente per ogni piano di esecuzione in base ai fattori descritti di seguito.

Quando un processo utente inserisce un piano di esecuzione nella cache, il costo corrente viene impostato sul costo di compilazione della query originale. Per i piani di esecuzione ad hoc, il processo utente imposta il costo corrente su zero. Quindi, ogni volta che un processo utente fa riferimento a un piano di esecuzione, il costo corrente viene reimpostato sul costo di compilazione originale. Per i piani di esecuzione ad hoc, il processo utente aumenta il costo corrente. Per tutti i piani, il valore massimo per il costo corrente corrisponde al costo di compilazione originale.

In caso di un numero eccessivo di richieste di memoria, il Motore di database risponde rimuovendo i piani di esecuzione dalla cache delle procedure. Per determinare i piani da rimuovere, il Motore di database esamina ripetutamente lo stato di ogni piano di esecuzione e rimuove i piani quando il relativo costo corrente è pari a zero. Un piano di esecuzione con un costo corrente pari a zero non viene rimosso automaticamente in caso di un numero eccessivo di richieste di memoria, ma viene rimosso solo nel momento in cui il Motore di database esamina il piano e il costo corrente è pari a zero. Durante l'analisi di un piano di esecuzione attualmente non utilizzato da una query, il Motore di database decrementa il costo corrente fino a raggiungere il valore zero.

Il Motore di database esamina ripetutamente i piani di esecuzione fino a quando non ne viene rimosso un numero sufficiente per soddisfare i requisiti di memoria. In caso di un numero eccessivo di richieste di memoria, è possibile che il costo di un piano di esecuzione venga incrementato e decrementato più di una volta. Quando il numero di richieste diminuisce, il Motore di database arresta la riduzione del costo corrente dei piani di esecuzione non utilizzati e tutti i piani di esecuzione rimangono nella cache delle procedure anche se il costo relativo è pari zero.

Il Motore di database utilizza il monitoraggio risorse e i thread utente per liberare memoria dalla cache delle procedure in risposta al numero eccessivo di richieste di memoria. Il monitoraggio risorse e i thread utente possono esaminare i piani in esecuzione simultanea per ridurre il costo corrente per ogni piano di esecuzione non utilizzato. Il monitoraggio risorse rimuove i piani di esecuzione dalla cache delle procedure quando è presente un numero eccessivo di richieste di memoria globale e libera memoria per applicare i criteri per la memoria di sistema, la memoria processi, la memoria del pool di risorse e la dimensione massima per tutte le cache.

Le dimensioni massime di tutte le cache sono rappresentate da una funzione relativa alle dimensioni del pool di buffer e non possono superare la memoria massima del server. Per ulteriori informazioni sulla configurazione della memoria massima del server, vedere l'impostazione max server memory in sp_configure (Transact-SQL).

In caso di un numero eccessivo di richieste di memoria cache, i thread utente rimuovono i piani di esecuzione dalla cache delle procedure e applicano i criteri per le dimensioni massime e per il numero massimo di voci della singola cache.

Nell'esempio seguente vengono indicati i piani di esecuzione rimossi dalla cache delle procedure:

  • A un piano di esecuzione viene fatto riferimento di frequente in modo che il costo non sia mai pari a zero. Il piano viene mantenuto nella cache delle procedure e non viene rimosso a meno che non vi sia un numero eccessivo di richieste di memoria e il costo corrente non sia pari a zero.

  • Viene inserito un piano di esecuzione ad hoc, cui non viene fatto nuovamente riferimento prima che sia presente un numero massimo di richieste di memoria. Poiché i piani ad hoc vengono inizializzati con un costo corrente pari a zero, quando il Motore di database esamina il piano di esecuzione, individuerà il costo corrente pari a zero e rimuoverà il piano dalla cache delle procedure. Il piano di esecuzione ad hoc viene mantenuto nella cache delle procedure con un costo corrente pari a zero in assenza di un numero eccessivo di richieste di memoria.

Per rimuovere manualmente un singolo piano o tutti i piani dalla cache, utilizzare DBCC FREEPROCCACHE (Transact-SQL).

Ricompilazione dei piani di esecuzione

Dopo avere apportato determinate modifiche a un database, è possibile che un piano di esecuzione risulti inefficiente o non valido in base al nuovo stato del database. In SQL Server vengono rilevate le modifiche che invalidano un piano di esecuzione e il piano viene contrassegnato come non valido. Per la successiva connessione che esegue la query, pertanto, è necessaria la ricompilazione di un nuovo piano. Le condizioni che invalidano un piano includono le seguenti:

  • Modifiche apportate a una tabella o a una vista a cui fa riferimento la query (ALTER TABLE e ALTER VIEW).

  • Modifiche agli indici utilizzati dal piano di esecuzione.

  • Aggiornamenti ai dati statistici utilizzati dal piano di esecuzione, generati in modo esplicito da un'istruzione, ad esempio UPDATE STATISTICS, o automaticamente.

  • Eliminazione di un indice utilizzato dal piano di esecuzione.

  • Chiamata esplicita a sp_recompile.

  • Grande quantità di modifiche alle chiavi, generate dalle istruzioni INSERT o DELETE eseguite da altri utenti che modificano una tabella a cui fa riferimento la query.

  • Nel caso di tabelle con trigger, aumento significativo del numero di righe della tabella inserted o deleted.

  • Esecuzione di una stored procedure utilizzando l'opzione WITH RECOMPILE.

La maggior parte delle ricompilazioni è necessaria per garantire la correttezza dell'istruzione o per ottenere piani di esecuzione della query potenzialmente più veloci.

In SQL Server 2000 ogni volta che un'istruzione di un batch provoca la ricompilazione, viene ricompilato l'intero batch, indipendentemente dal fatto che sia stato inviato tramite una stored procedure, un trigger, un batch ad hoc o un'istruzione preparata. In SQL Server 2005 e versioni successive, viene ricompilata solo l'istruzione all'interno del batch che ha provocato la ricompilazione. A causa di questa differenza, i conteggi delle ricompilazioni in SQL Server 2000 e versioni successive non possono essere confrontati. In SQL Server 2005 e versioni successive sono inoltre presenti più tipi di ricompilazioni, in quanto il set di caratteristiche è più ampio.

La ricompilazione a livello di istruzione consente di migliorare le prestazioni in quanto, nella maggior parte dei casi, le ricompilazioni e gli svantaggi associati, in termini di blocchi e tempo della CPU, sono dovuti a un piccolo numero di istruzioni. Questi svantaggi possono pertanto venire evitati per le altre istruzioni nel batch che non devono essere ricompilate.

L'evento di traccia SP:Recompile di SQL Server Profiler indica le ricompilazioni a livello di istruzione. In SQL Server 2000 tale evento di traccia indica solo le ricompilazioni dei batch. Viene inoltre popolata la colonna TextData di questo evento. Non è pertanto più necessario tracciare SP:StmtStarting o SP:StmtCompleted per ottenere il testo Transact-SQL che ha causato la ricompilazione, come è invece necessario in SQL Server 2000.

L'evento di traccia SQL:StmtRecompile indica le ricompilazioni a livello di istruzione e può essere utilizzato per tracciare le ricompilazioni ed eseguirne il debug. Mentre SP:Recompile viene generato solo per stored procedure e trigger, SQL:StmtRecompile viene generato per stored procedure, trigger, batch ad hoc, batch eseguiti utilizzando sp_executesql, query preparate e linguaggio SQL dinamico.

La colonna EventSubClass di SP:Recompile e SQL:StmtRecompile contiene un codice integer che indica il motivo della ricompilazione. Nella tabella seguente viene descritto il significato di ogni numero di codice.

Valore di EventSubClass

Descrizione

1

Schema modificato.

2

Statistiche modificate.

3

Compilazione posticipata.

4

Opzione SET modificata.

5

Tabella temporanea modificata.

6

Set di righe remoto modificato.

7

Autorizzazioni FOR BROWSE modificate.

8

Ambiente di notifica query modificato.

9

Vista partizionata modificata.

10

Opzioni cursore modificate.

11

Opzione (RECOMPILE) richiesta.

Nota

Quando l'opzione di database AUTO_UPDATE_STATISTICS è impostata su ON, le query vengono ricompilate quando sono indirizzate a tabelle o viste indicizzate le cui statistiche sono state aggiornate o le cui cardinalità sono state modificate in modo significativo dall'ultima esecuzione. Questo comportamento si applica alle tabelle standard definite dall'utente, alle tabelle temporanee e alle tabelle inserted e deleted create dai trigger DML. Se le prestazioni delle query sono influenzate da un numero eccessivo di ricompilazioni, è possibile modificare l'impostazione su OFF. Quando l'opzione AUTO_UPDATE_STATISTICS del database è impostata su OFF, non vengono eseguite ricompilazioni in base alle statistiche o alle modifiche delle cardinalità, ad eccezione delle tabelle inserted e deleted create dai trigger DML INSTEAD OF. Poiché tali tabelle vengono create in tempdb, la ricompilazione delle query che vi accedono dipende dall'impostazione di AUTO_UPDATE_STATISTICS in tempdb. Si noti che in SQL Server 2000, la ricompilazione delle query continua in base alle modifiche delle cardinalità delle tabelle inserted e deleted del trigger DML, anche quando l'impostazione è OFF. Per ulteriori informazioni sulla disabilitazione di AUTO_UPDATE_STATISTICS, vedere Utilizzo di statistiche per migliorare le prestazioni di esecuzione delle query.