Ricompilare una stored procedure

Articolo
04/02/2014

In questo argomento viene descritto come ricompilare una stored procedure in SQL Server 2012 utilizzando Transact-SQL. È possibile eseguire questa operazione in tre modi: utilizzando 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 utilizzando la stored procedure di sistema sp_recompile. In questo argomento viene illustrato l'utilizzo dell'opzione WITH RECOMPILE quando si crea una definizione di stored procedure e si esegue una stored procedure esistente. Viene inoltre descritto l'utilizzo della stored procedure di sistema sp_recompile per ricompilare una stored procedure esistente.

Contenuto dell'argomento

Prima di iniziare:

Consigli

Sicurezza
Per ricompilare una stored procedure tramite:

Transact-SQL

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 ha luogo 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 utilizzati dalla stored procedure 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.
In SQL Server viene introdotta la ricompilazione di stored procedure a livello di istruzione. Quando in SQL Server vengono ricompilate 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 utilizzano 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 ulteriori informazioni, vedere Hint per la query (Transact-SQL).

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 ulteriori informazioni, vedere EXECUTE (Transact-SQL).
Hint per la query RECOMPILE
Questa funzionalità viene utilizzata 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.

Icona freccia utilizzata con il collegamento Torna all'inizio [Torna all'inizio]

Utilizzo di Transact-SQL

Per ricompilare una stored procedure utilizzando l'opzione WITH RECOMPILE

Connettersi al Motore di database.
Dalla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui. In questo esempio seguente viene creata la definizione della stored procedure.

USE AdventureWorks2012;
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;
GO

Per ricompilare una stored procedure utilizzando l'opzione WITH RECOMPILE

Connettersi al Motore di database.
Dalla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui. In questo esempio viene creata una stored procedure semplice tramite cui vengono restituiti tutti i dipendenti (per cui vengono indicati il nome e il cognome), le relative posizioni e i nomi dei reparti di appartenenza da una vista.

Successivamente, copiare e incollare il secondo esempio di codice nella finestra Query, quindi fare clic su Esegui. Verrà eseguita la stored procedure e verrà ricompilato il piano di query della stored procedure.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

USE AdventureWorks2012;
GO
EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE;
GO

Per ricompilare una stored procedure utilizzando sp_recompile

Connettersi al Motore di database.
Dalla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui. In questo esempio viene creata una stored procedure semplice tramite cui vengono restituiti tutti i dipendenti (per cui vengono indicati il nome e il cognome), le relative posizioni e i nomi dei reparti di appartenenza da una vista.

Successivamente, copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su 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 AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

USE AdventureWorks2012;
GO
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';
GO

Icona freccia utilizzata con il collegamento Torna all'inizio [Torna all'inizio]