Caching e riutilizzo del piano di esecuzione

Data aggiornamento: 17 luglio 2006

In SQL Server 2005 è 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 2005 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.

Quando in SQL Server 2005 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 2005, consentendo di evitare l'overhead della ricompilazione dell'istruzione SQL. Se non esiste già un piano di esecuzione, in SQL Server 2005 viene generato un nuovo piano per la query.

In SQL Server 2005 è 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 scansione 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 Contact

SELECT * FROM Person.Contact

È più probabile che i singoli piani di esecuzione vengano riutilizzati in un'istanza di SQL Server 2000 e SQL Server 2005 che non in SQL Server 6.5 e versioni precedenti.

Durata dei piani di esecuzione

Dopo essere stato generato, un piano di esecuzione viene conservato nella cache delle procedure. In SQL Server 2005 i piani di esecuzione vecchi inutilizzati vengono rimossi dalla cache solo quando è necessario spazio. A ogni piano della query e contesto di esecuzione è associato un fattore di costo che indica il costo della compilazione della struttura. Queste strutture di dati includono inoltre un campo di validità. Ogni volta che una connessione fa riferimento all'oggetto, il campo di validità viene incrementato del fattore di costo di compilazione. Ad esempio, se un piano della query ha un fattore di costo pari a 8 e vi viene fatto riferimento due volte, la validità è 16. Il processo Lazywriter esegue la scansione periodica dell'elenco degli oggetti presenti nella cache delle procedure. Il processo Lazywriter decrementa di un'unità il campo di validità di ogni oggetto a ogni scansione. La validità del piano della query di esempio verrà ridotta a 0 dopo 16 scansioni della cache delle procedure, a meno che un altro utente non faccia riferimento al piano. Il processo Lazywriter dealloca un oggetto se vengono soddisfatte le condizioni seguenti:

• Lo strumento di gestione della memoria richiede memoria e tutta la memoria disponibile è in uso.
• Il campo di validità dell'oggetto è 0.
• Nessuna connessione fa riferimento all'oggetto.

Poiché il campo di validità viene incrementato ogni volta che si fa riferimento a un oggetto, i campi di validità degli oggetti a cui viene fatto riferimento di frequente non verranno ridotti a 0 e gli oggetti non verranno rimossi dalla cache. Gli oggetti a cui viene fatto riferimento poco frequentemente diventano presto deallocabili, ma non vengono deallocati fino a quando non è necessaria memoria per altri oggetti.

Ricompilazione di piani di esecuzione

Dopo avere apportato determinate modifiche a un database, è possibile che un piano di esecuzione risulti inefficiente o non più 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 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 SQL Server 2005 non possono essere confrontati. In SQL Server 2005 sono inoltre presenti più tipi di ricompilazioni, in quanto il set di funzionalità è 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 2005. In SQL Server 2000 tale evento di traccia indica solo le ricompilazioni dei batch. In SQL Server 2005 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.

In SQL Server 2005 è inoltre incluso un nuovo evento di traccia denominato SQL:StmtRecompile, che indica le ricompilazioni a livello di istruzione e può essere utilizzato per tracciare le ricompilazioni ed eseguirne il backup. 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.

[!NOTA] Quando l'opzione di database AUTO_UPDATE_STATISTICS è SET 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 disattivazione di AUTO_UPDATE_STATISTICS, vedere Statistiche dell'indice.

Vedere anche

Riferimento

Oggetto SQL Statistics di SQL Server

Concetti

Gestione del buffer

Altre risorse

Problematiche di compilazione batch, ricompilazione e memorizzazione dei piani nella cache in SQL Server 2005

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche