Volver a compilar un procedimiento almacenado

Artículo
04/02/2014

En este tema se describe cómo volver a compilar un procedimiento almacenado en SQL Server 2012 mediante Transact-SQL. Hay tres formas de hacerlo: la opción WITH RECOMPILE en la definición del procedimiento o cuando se llama al procedimiento, la sugerencia de consulta RECOMPILE en instrucciones individuales o usando el procedimiento almacenado del sistema sp_recompile. En este tema se describe el uso de la opción WITH RECOMPILE al crear una definición de procedimiento y ejecutar un procedimiento existente. También se describe el uso del procedimiento almacenado del sistema sp_recompile para volver a compilar un procedimiento existente.

En este tema

Antes de empezar:

Recomendaciones

Seguridad
Para volver a compilar un procedimiento almacenado, usando:

Transact-SQL

Antes de empezar

Recomendaciones

Cuando un procedimiento se compila por primera vez o se vuelve a compilar, el plan de consulta del procedimiento se optimiza para el estado actual de la base de datos y sus objetos. Si una base de datos experimenta cambios significativos en sus datos o su estructura, al volver a compilar un procedimiento se actualiza y optimiza el plan de consulta del procedimiento para tener en cuenta esos cambios. Esto puede mejorar el rendimiento de procesamiento del procedimiento.
Hay ocasiones en las que se debe forzar la nueva compilación del procedimiento y otras en que se realiza automáticamente. La nueva compilación automática tiene lugar siempre que se reinicia SQL Server. También se produce si una tabla subyacente a la que hace referencia el procedimiento ha experimentado cambios de diseño físico.
Otro motivo para forzar la nueva compilación de un procedimiento es contrarrestar el comportamiento de "examen de parámetros" de la compilación de procedimientos. Cuando SQL Server ejecuta procedimientos, los valores de parámetros usados por el procedimiento cuando se compila se incluyen como parte de la generación del plan de consulta. Si esos valores representan los valores típicos con los que se llama al procedimiento posteriormente, el procedimiento se beneficia del plan de consulta cada vez que se compila y se ejecuta. Si los valores de parámetro del procedimiento suelen ser atípicos, forzar una nueva compilación del procedimiento y un nuevo plan basándose en diferentes valores de parámetro puede mejorar el rendimiento.
SQL Server incorpora nueva compilación de nivel de instrucciones de los procedimientos. Cuando SQL Server vuelve a compilar procedimientos almacenados, solo se compila la instrucción que ha causado la nueva compilación, en lugar de todo el procedimiento.
Si algunas consultas de un procedimiento suelen usar valores atípicos o temporales, se puede mejorar el rendimiento del procedimiento con la sugerencia de consulta RECOMPILE dentro de esas consultas. Puesto que solo se volverán a compilar las consultas que usan la sugerencia de consulta en lugar del procedimiento completo, se imita el comportamiento de nueva compilación de nivel de instrucciones de SQL Server. Además de usar los valores de parámetro actuales del procedimiento, la sugerencia de consulta RECOMPILE también emplea los valores de cualquier variable local del procedimiento almacenado al compilar la instrucción. Para obtener más información, vea Sugerencia de consulta (Transact-SQL).

Seguridad

Permisos

Opción WITH RECOMPILE
Si se usa esta opción cuando se crea la definición del procedimiento, se necesita el permiso CREATE PROCEDURE en la base de datos y el permiso ALTER en el esquema en el que se crea el procedimiento.

Si se usa esta opción en una instrucción EXECUTE, se necesitan permisos EXECUTE para el procedimiento. No se necesitan permisos en la propia instrucción EXECUTE, pero sí se necesitan permisos de ejecución en el procedimiento al que se hace referencia en la instrucción EXECUTE. Para obtener más información, vea EXECUTE (Transact-SQL).
Sugerencia de consulta RECOMPILE
Esta característica se emplea cuando se crea el procedimiento y la sugerencia se incluye en las instrucciones de Transact-SQL del procedimiento. Por tanto, necesita el permiso CREATE PROCEDURE en la base de datos y el permiso ALTER en el esquema en el que se va a crear el procedimiento.
Procedimiento almacenado del sistema sp_recompile
Necesita el permiso ALTER en el procedimiento especificado.

Icono de flecha usado con el vínculo Volver al principio [Principio]

Usar Transact-SQL

Para volver a compilar un procedimiento almacenado usando la opción WITH RECOMPILE

Conéctese con el Motor de base de datos.
En la barra Estándar, haga clic en Nueva consulta.
Copie y pegue el ejemplo siguiente en la ventana de consulta y haga clic en Ejecutar. En este ejemplo se crea la definición del procedimiento.

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

Para volver a compilar un procedimiento almacenado usando la opción WITH RECOMPILE

Conéctese con el Motor de base de datos.
En la barra Estándar, haga clic en Nueva consulta.
Copie y pegue el ejemplo siguiente en la ventana de consulta y haga clic en Ejecutar. En este ejemplo se crea un procedimiento simple que devuelve todos los empleados (nombre y apellidos), sus puestos y los nombres de sus departamentos a partir de una vista.

Después, copie y pegue el segundo ejemplo de código en la ventana de consulta y, a continuación, haga clic en Ejecutar. Esto ejecutará el procedimiento y volverá a compilar el plan de consulta del procedimiento.

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

Para volver a compilar un procedimiento almacenado mediante sp_recompile

Conéctese con el Motor de base de datos.
En la barra Estándar, haga clic en Nueva consulta.
Copie y pegue el ejemplo siguiente en la ventana de consulta y haga clic en Ejecutar. En este ejemplo se crea un procedimiento simple que devuelve todos los empleados (nombre y apellidos), sus puestos y los nombres de sus departamentos a partir de una vista.

Después, copie y pegue el ejemplo siguiente en la ventana de consulta y, a continuación, haga clic en Ejecutar. Esto no ejecuta el procedimiento, sino que lo marca para que se vuelva a compilar de forma que su plan de consulta se actualice la próxima vez que se ejecute el procedimiento.

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

Icono de flecha usado con el vínculo Volver al principio [Principio]