Volver a compilar un procedimiento almacenado

Artículo
04/03/2023

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)

En este artículo se describe cómo volver a compilar un procedimiento almacenado en SQL Server usando 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.

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 Sugerencias de consulta (Transact-SQL).

Nota:

En los grupos dedicados y sin servidor de Azure Synapse Analytics, los procedimientos almacenados no se compilan previamente y, por tanto, no se pueden volver a compilar. Para obtener más información, consulte Uso de procedimientos almacenados para grupos de SQL dedicados en Azure Synapse Analytics.

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).

RECOMPILE, sugerencia de consulta

Esta característica se emplea cuando se crea el procedimiento y se incluye la sugerencia en las instrucciones 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.

Ejecutar el procedimiento almacenado del sistema sp_recompile

Necesita el permiso ALTER en el procedimiento especificado.

Usar Transact-SQL

Conéctese con el Motor de base de datos.
En la barra Estándar, seleccione Nueva consulta.

Copie y pegue el ejemplo siguiente en la ventana de consulta y seleccione Ejecutar. En este ejemplo se crea la definición del procedimiento.

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;

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

Seleccione Nueva consulta. Luego, copie y pegue el ejemplo de código siguiente en la ventana de consulta y seleccione Ejecutar. Esto ejecutará el procedimiento y volverá a compilar el plan de consulta del procedimiento.

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

Para volver a compilar un procedimiento almacenado mediante sp_recompile

Seleccione Nueva consulta. Luego, copie y pegue el ejemplo siguiente en la ventana de consulta y 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 AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Pasos siguientes

Crear un procedimiento almacenado
Modificar un procedimiento almacenado
Cambiar el nombre de un procedimiento almacenado
Ver la definición de un procedimiento almacenado
Ver las dependencias de un procedimiento almacenado
DROP PROCEDURE (Transact-SQL)

Compartir vía