Ricompilazione di stored procedure
Quando un database viene modificato da operazioni 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 MicrosoftSQL Server e in caso di modifica di una tabella sottostante utilizzata dalla stored procedure. Se, tuttavia, viene aggiunto un nuovo indice dal quale la stored procedure può trarre vantaggio, l'ottimizzazione non verrà eseguita 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 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.
[!NOTA]
In SQL Server 2008 viene introdotta la ricompilazione di stored procedure a livello di istruzione. Quando SQL Server 2008 ricompila le stored procedure, viene in realtà 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 consente di forzare 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.
La creazione di una stored procedure nella cui definizione viene specificata l'opzione WITH RECOMPILE indica che SQL Server non consente di memorizzare nella cache un piano per questa stored procedure; quest'ultima verrà infatti ricompilata a ogni esecuzione. Utilizzare l'opzione WITH RECOMPILE quando le stored procedure accettano parametri con valori molto diversi nelle differenti esecuzioni della stored procedure e comportano 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 a 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 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 con un oggetto con lo stesso nome, la stored procedure verrà eseguita senza dover essere ricreata.
Per ricompilare una stored procedure alla successiva esecuzione