Ricompilazione di stored procedure
Quando un database viene modificato da azioni quali l'aggiunta di indici o la modifica di dati in colonne indicizzate, è consigliabile ottimizzare nuovamente i piani di query originali utilizzati per accedere alle tabelle ricompilandoli. Questa ottimizzazione viene eseguita automaticamente alla prima esecuzione della stored procedure dopo il riavvio di SQL Server e in caso di modifica di una tabella sottostante che viene utilizzata dalla stored procedure. Se, tuttavia, viene aggiunto un nuovo indice dal quale la stored procedure può trarre vantaggio, l'ottimizzazione non verrà eseguita automaticamente fino all'esecuzione successiva della stored procedure dopo il riavvio di SQL Server. In tale situazione può risultare utile forzare la ricompilazione della stored procedure alla successiva esecuzione.
È opportuno forzare la ricompilazione di una stored procedure anche per rispondere, qualora necessario, a un eventuale sniffing dei parametri riscontrato durante la compilazione. Quando SQL Server esegue le stored procedure, nella generazione del piano di query rientrano tutti i valori di parametri che vengono utilizzati dalla procedura durante la compilazione. Se tali valori rappresentano quelli standard utilizzati per le chiamate successive della procedura, la stored procedure beneficerà del piano di query a ogni compilazione ed esecuzione. In caso contrario, potrebbero verificarsi problemi di prestazioni.
In SQL Server 2008 R2 viene introdotta la ricompilazione di stored procedure a livello di istruzione. Quando in SQL Server 2008 R2 vengono ricompilate le stored procedure, in realtà viene compilata solo l'istruzione che ha causato la ricompilazione, anziché l'intera procedura. Di conseguenza, i valori dei parametri utilizzati in SQL Server saranno quelli inclusi nell'istruzione ricompilata durante la rigenerazione del piano di query. Tali valori possono differire da quelli passati in origine alla procedura.
Ricompilazione forzata di una stored procedure
In SQL Server sono disponibili tre metodi per forzare la ricompilazione di una stored procedure:
La stored procedure di sistema sp_recompile forza la ricompilazione di una stored procedure alla successiva esecuzione. Ciò avviene tramite l'eliminazione del piano esistente dalla cache delle procedure e la creazione forzata di un nuovo piano alla successiva esecuzione della stored procedure.
SQL Server non memorizza nella cache un piano per una stored procedure nella cui definizione è presente l'opzione WITH RECOMPILE. La stored procedure verrà infatti ricompilata a ogni esecuzione. Utilizzare l'opzione WITH RECOMPILE quando le stored procedure accettano parametri con valori molto diversi tra un'esecuzione e l'altra della stored procedure, causando ogni volta la creazione di piani di esecuzione diversi. L'utilizzo di questa opzione è poco frequente e rallenta l'esecuzione della stored procedure perché deve essere ricompilata ad ogni esecuzione.
Se si desidera che vengano ricompilate solo singole query all'interno della stored procedure, anziché l'intera stored procedure, specificare l'hint per la query RECOMPILE all'interno di ciascuna query da ricompilare. Questo comportamento è analogo a quello della compilazione a livello di istruzione incluso in SQL Server descritto in precedenza in questa sezione. In questo caso, tuttavia, oltre ai parametri correnti della stored procedure, l'hint per la query RECOMPILE utilizza i valori di tutte le variabili locali incluse all'interno della stored procedure durante la compilazione dell'istruzione. Preferire questa opzione quando si utilizzano valori atipici o temporanei solo in un subset di query che appartengono alla stored procedure. Per ulteriori informazioni, vedere Hint per le query (Transact-SQL).
È possibile forzare la ricompilazione della stored procedure specificando l'opzione WITH RECOMPILE quando si esegue la stored procedure. Utilizzare questa opzione soltanto se il parametro specificato è atipico o se i dati sono stati modificati in modo significativo dalla creazione della stored procedure.
Nota
Se un oggetto cui fa riferimento la stored procedure viene eliminato o rinominato, durante l'esecuzione della stored procedure verrà restituito un errore. Se un oggetto cui fa riferimento la stored procedure viene invece sostituito da un oggetto con lo stesso nome, la stored procedure verrà eseguita senza essere ricompilata.
Per ricompilare una stored procedure alla successiva esecuzione