Volver a compilar procedimientos almacenados
Cuando se cambia una base de datos mediante la adición de índices o la modificación de datos de columnas indizadas, los planes de consulta originales usados para el acceso a las tablas deberán optimizarse de nuevo; para ello, será preciso volver a compilarlos. Esta optimización se produce automáticamente la primera vez que se ejecuta un procedimiento almacenado tras reiniciar SQL Server. También se produce si cambia una tabla subyacente usada por el procedimiento almacenado. Pero si se agrega un nuevo índice del que se puede beneficiar el procedimiento almacenado, la optimización no se produce automáticamente hasta que se vuelva a ejecutar el procedimiento almacenado tras reiniciar SQL Server. En esta situación, puede ser útil forzar que se vuelva a compilar el procedimiento almacenado la próxima vez que se ejecute.
Otro motivo para forzar que se vuelva a compilar un procedimiento almacenado es contrarrestar, cuando sea necesario, el comportamiento de "examen de parámetros" de la compilación de procedimientos almacenados. Cuando SQL Server ejecuta procedimientos almacenados, 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 el procedimiento se invoca posteriormente, el procedimiento almacenado se beneficia del plan de consulta cada vez que se compila y se ejecuta. En caso contrario, el rendimiento puede verse afectado.
SQL Server 2008 R2 incorpora recompilación de instrucciones individuales de los procedimientos almacenados. Cuando SQL Server 2008 R2 recompila procedimientos almacenados, solo se compila la instrucción que ha causado la recompilación, en lugar del procedimiento completo. En consecuencia, SQL Server usa los valores de parámetros como existen en la instrucción recompilada al volver a generar un plan de consulta. Esos valores pueden diferir de los que se pasaron originalmente al procedimiento.
Forzar la recompilación de un procedimiento almacenado
SQL Server proporciona tres formas de forzar que un procedimiento almacenado se vuelva a compilar:
El procedimiento almacenado del sistema sp_recompile fuerza que se vuelva a compilar un procedimiento almacenado la próxima vez que se ejecute. Para ello, elimina el plan existente de la memoria caché de procedimientos que fuerza la creación de un nuevo plan la próxima vez que se ejecuten el procedimiento o el desencadenador.
La creación de un procedimiento almacenado que especifique la opción WITH RECOMPILE en su definición indica a SQL Server que no guarde en memoria caché ningún plan para ese procedimiento, que vuelve a compilarse cada vez que se ejecuta. Use la opción WITH RECOMPILE cuando los procedimientos almacenados adopten parámetros cuyos valores varíen considerablemente entre distintas ejecuciones del procedimiento almacenado, de modo que se creen distintos planes de ejecución cada vez. Esta opción no se usa con mucha frecuencia y provoca que el procedimiento almacenado se ejecute más lentamente, ya que debe compilarse de nuevo cada vez que se ejecuta.
Si solo desea que se vuelvan a compilar consultas individuales dentro del procedimiento almacenado, en lugar del procedimiento almacenado completo, especifique la sugerencia de consulta de RECOMPILE dentro de cada consulta que desee volver a compilar. Este comportamiento imita el comportamiento de la recompilación de nivel de instrucción de SQL Server expuesto anteriormente en esta sección, pero además de usar los valores de parámetro actuales del procedimiento almacenado, la sugerencia de consulta de RECOMPILE también usa los valores de las variables locales de dentro del procedimiento almacenado durante la compilación de la instrucción. Use esta opción cuando se utilicen valores atípicos o temporales solo en un subconjunto de consultas que pertenecen al procedimiento almacenado. Para obtener más información, vea Sugerencia de consulta (Transact-SQL).
Puede forzar que se vuelva a compilar el procedimiento almacenado si especifica la opción WITH RECOMPILE al ejecutarlo. Use esta opción únicamente si el parámetro que está suministrando es atípico o si los datos han cambiado considerablemente desde que se creó el procedimiento almacenado.
Nota
Si se elimina o se cambia el nombre de un objeto al que se hace referencia desde un procedimiento, se devuelve un error durante la ejecución del procedimiento almacenado. No obstante, si un objeto al que se hace referencia en un procedimiento almacenado se reemplaza por un objeto con el mismo nombre, el procedimiento se ejecuta sin necesidad de volver a crearse.
Para volver a compilar un procedimiento almacenado cuando se ejecuta de nuevo