Ricompilare una stored procedure

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzurePiattaforma di analisi (PDW)Database SQL in Microsoft Fabric

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

Prima di iniziare

Recommendations

• 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 della procedura.

• Ci sono casi in cui la ricompilazione della procedura deve essere forzata e altri in cui avviene automaticamente. La ricompilazione automatica si verifica a ogni riavvio di SQL Server. Si verifica inoltre se una tabella sottostante a cui fa riferimento la procedura ha subito modifiche alla progettazione fisica.

• Un altro motivo per cui forzare la ricompilazione di una procedura è contrastare il comportamento di "parameter sniffing" della compilazione della procedura. Quando SQL Server esegue le procedure, tutti i valori dei parametri utilizzati dalla procedura durante la compilazione vengono inclusi nella generazione del piano di query. Se questi valori rappresentano quelli tipici con cui la procedura viene successivamente chiamata, la procedura trae vantaggio dal piano di esecuzione della query ogni volta che viene compilata ed eseguita. 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 supporta la ricompilazione delle procedure memorizzate 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 alcune query all'interno di una stored procedure utilizzano regolarmente valori atipici o temporanei, è possibile migliorare le prestazioni della stored procedure utilizzando l'hint di query RECOMPILE all'interno di tali query. Poiché vengono ricompilate solo le query che usano l'hint di query anziché l'intera stored procedure, si replica 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).

Security

Permissions

Opzione WITH RECOMPILE

Se questa opzione viene utilizzata quando viene creata la definizione della routine, è necessaria CREATE PROCEDURE l'autorizzazione nel database e l'autorizzazione ALTER per lo schema in cui viene creata la routine.

Se questa opzione viene utilizzata in un'istruzione EXECUTE, richiede autorizzazioni di EXECUTE sulla procedura. 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, sono necessarie l'autorizzazione CREATE PROCEDURE sul database e l'autorizzazione ALTER per lo schema in cui viene creata la procedura.

Stored procedure di sistema sp_recompile

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

Utilizzo di Transact-SQL

1. Connettersi al Motore di database.

2. Nella barra Standard selezionare Nuova query.

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

   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. Questo comando esegue la procedura e ricompila il piano di esecuzione della query della procedura.

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. Questa operazione non esegue la procedura, ma la contrassegna per la ricompilazione in modo che il relativo piano di esecuzione della query venga aggiornato la volta successiva in cui la procedura viene eseguita.

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

Passaggi successivi

Creazione di una stored procedure
Modificare una Stored Procedure
Rinominare procedura archiviata
Visualizza la definizione di una procedura memorizzata
Visualizza le dipendenze di una stored procedure
DROP PROCEDURE (Transact-SQL)