Ricompilare una stored procedure

Articolo
05/24/2023

Si applica a:SQL Server database SQL di Azure e Istanza gestita di SQL di Azure Azure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Questo articolo descrive come ricompilare una stored procedure in SQL Server tramite Transact-SQL. È possibile eseguire questa operazione in tre modi: usando l'opzione WITH RECOMPILE nella definizione della stored procedure o quando viene chiamata la stored procedure, tramite l'hint per la query RECOMPILE nelle singole istruzioni o usando la stored procedure di sistema sp_recompile.

Prima di iniziare

Consigli

Quando una stored procedure viene compilata per la prima volta o viene ricompilata, il piano di query della stored procedure viene ottimizzato per lo stato corrente del database e dei relativi oggetti. Se i dati o la struttura di un database vengono modificati significativamente, con la ricompilazione di una stored procedure viene aggiornato e ottimizzato il piano di query della stored procedure per tali modifiche. Ciò può migliorare le prestazioni di elaborazione della stored procedure.
In alcuni casi la ricompilazione della stored procedure deve essere forzata, in altri invece avviene automaticamente. La ricompilazione automatica si verifica a ogni riavvio di SQL Server. Si verifica inoltre se vengono apportate modifiche alla progettazione fisica di una tabella sottostante a cui fa riferimento la stored procedure.
È opportuno forzare la ricompilazione di una stored procedure anche per rispondere 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 usati dalla procedura durante la compilazione. Se tali valori rappresentano quelli standard utilizzati per le chiamate successive della stored procedure, questa beneficerà del piano di query a ogni compilazione ed esecuzione. Se i valori dei parametri della stored procedure sono spesso atipici, le prestazioni possono migliorare con la forzatura di una ricompilazione della stored procedure e un nuovo piano basato su valori dei parametri diversi.
SQL Server presenta la ricompilazione di stored procedure a livello di istruzione. Quando SQL Server ricompila le stored procedure, in realtà viene compilata solo l'istruzione che ha causato la ricompilazione, anziché la stored procedure completa.
Se determinate query in una stored procedure utilizzano regolarmente valori atipici o temporanei, le prestazioni della stored procedure possono migliorare tramite l'utilizzo dell'hint per la query RECOMPILE all'interno delle query stesse. Poiché vengono ricompilate solo le query che usano l'hint per la query anziché la stored procedure completa, viene riprodotto il comportamento di ricompilazione a livello di istruzione di SQL Server. Oltre all'utilizzo dei valori dei parametri correnti della stored procedure, l'hint per la query RECOMPILE utilizza i valori di qualsiasi variabile locale inclusa nella stored procedure quando si compila l'istruzione. Per altre informazioni, vedere Hint per la query (Transact-SQL).

Nota

Nei pool dedicati e serverless di Azure Synapse Analytics le stored procedure non sono il risultato di codice precompilato, pertanto non possono essere ricompilate. Per altre informazioni, vedere Uso di stored procedure per pool SQL dedicati in Azure Synapse Analytics.

Sicurezza

Autorizzazioni

Opzione WITH RECOMPILE

Se si utilizza questa opzione alla creazione della definizione della stored procedure, è necessario disporre dell'autorizzazione CREATE PROCEDURE per il database e dell'autorizzazione ALTER per lo schema in cui verrà creata la stored procedure.

Se questa opzione viene utilizzata in un'istruzione EXECUTE, richiede autorizzazioni EXECUTE sulla stored procedure. Le autorizzazioni non sono richieste per l'istruzione EXECUTE stessa, ma le autorizzazioni di esecuzione sono necessarie per la stored procedure a cui fa riferimento l'istruzione EXECUTE. Per altre informazioni, vedere EXECUTE (Transact-SQL).

RECOMPILE - hint per la query

Questa funzionalità viene usata quando si crea la stored procedure e si include l'hint nelle istruzioni Transact-SQL nella stored procedure. È pertanto necessario disporre dell'autorizzazione CREATE PROCEDURE per il database e dell'autorizzazione ALTER per lo schema in cui la stored procedure viene creata.

Stored procedure di sistema sp_recompile

È richiesta l'autorizzazione ALTER per la stored procedure specificata.

Utilizzo di Transact-SQL

Connettersi al motore di database di .
Nella barra Standard selezionare Nuova query.

Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui. In questo esempio seguente viene creata la definizione della stored procedure.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
    DROP PROCEDURE dbo.uspProductByVendor;  
GO  
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
WITH RECOMPILE  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
    FROM Purchasing.Vendor AS v   
    JOIN Purchasing.ProductVendor AS pv   
      ON v.BusinessEntityID = pv.BusinessEntityID   
    JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID  
    WHERE v.Name LIKE @Name;

Per ricompilare una stored procedure utilizzando l'opzione WITH RECOMPILE

Selezionare Nuova query, quindi copiare e incollare l'esempio di codice seguente nell'intervallo di query e selezionare Esegui. Verrà eseguita la stored procedure e verrà ricompilato il piano di query della stored procedure.

USE AdventureWorks2022;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

Per ricompilare una stored procedure utilizzando sp_recompile

Selezionare Nuova query, quindi copiare e incollare l'esempio seguente nell'intervallo di query e selezionare Esegui. La stored procedure non verrà eseguita, ma contrassegnata per la ricompilazione in modo che il relativo piano di query venga aggiornata alla successiva esecuzione della stored procedure.

USE AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Passaggi successivi

Creazione di una stored procedure
Modificare una stored procedure
Rinominare una stored procedure
Visualizzare la definizione di una stored procedure
Visualizzare le dipendenze di una stored procedure
DROP PROCEDURE (Transact-SQL)