RECOMPILE o no RECOMPILE

---

La ventaja de utilizar procedimientos almacenados es que el proceso de la planeación de la ejecución del código que lo compone se realiza una sola vez (durante su primera ejecución) y el plan es almacenado para futuras referencias.

El proceso de planear la ejecución de un query es un tanto lento dado que se deben de explorar las estructuras de datos que existen alrededor de la información que se desea consultar, sean las tablas mismas, índices clustered, índices nonclustered, índices de cobertura, vistas indexadas, estadísticas, etc.

Es importante tomar en cuenta que el plan de ejecución habrá sido calculado según la cantidad de información y las estructuras previamente mencionadas al momento de su ejecución.

Dependiendo de la estructura o de la lógica de nuestro procedimiento almacenado deberemos estudiar la posibilidad de obligar a que el plan de ejecución sea recalculado.

Para lograr este objetivo existen muchas opciones:

1. Crear el procedimiento almacenado con la opción RECOMPILE.
2. Ejecutar el procedimiento almacenado con  la opción RECOMPILE.
3. Ejecutar DBCC FREEPROCCACHE.
4. Recrear el procedimiento almacenado.
5. Usar el procedimiento almacenado sp_recompile.

En esta entrada del blog abordaré el punto 1, en unos días terminaré de comentar los siguientes puntos.

1. Crear procedimiento almacenado con la opción RECOMPILE.

Es probable que muchas personas entren en conflicto con esta opción dado que uno de los objetivos principales de los procedimientos almacenados es que su plan de ejecución quede almacenado y así nos evitemos la tarea de estar recalculando el plan de ejecución cada vez que se haga uso de ellos.

Para aterrizar esta idea, utilizaré la siguiente estructura:

CREATE TABLE Customers(
      Id INT IDENTITY(1, 1) NOT NULL,
      Name VARCHAR(80) NOT NULL,
      City VARCHAR(4) NOT NULL,
      RegistrationDate DATE NOT NULL,
      CONSTRAINT pkCustomers PRIMARY KEY (Id)
)
GO
CREATE NONCLUSTERED INDEX ixCustomers_City
ON Customers (RegistrationDate)
La llenaremos con datos ficticios a través del siguiente query:
DECLARE @i INT = 0
DECLARE @Name VARCHAR(80)
DECLARE @City VARCHAR(4)
DECLARE @RegistrationDate DATE
WHILE @i < 30000
BEGIN
      SET @Name = REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', '')
      SET @City = SUBSTRING(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 1, 4)
      SET @RegistrationDate = DATEADD(DAY, CHECKSUM(NEWID()) % 15, GETDATE())
      INSERT INTO Customers (Name, City, RegistrationDate)
      VALUES (@Name, @City, @RegistrationDate)
      SET @i = @i + 1
END

Supongamos que nuestro procedimiento almacenado recibe como parámetro la columna bajo la cual se realizará una búsqueda de información.

CREATE PROC pFindCustomers(
      @Column VARCHAR(30),
      @Condition VARCHAR(10)
)
AS
BEGIN
      IF @Column = 'City'
      BEGIN
            SELECT Id, Name, City, RegistrationDate
            FROM Customers
            WHERE City = @Condition
      END
      ELSE IF @Column = 'RegistrationDate'
      BEGIN
            SELECT Id, Name, City, RegistrationDate
            FROM Customers
            WHERE RegistrationDate = @Condition
      END
END

Ejecutemos nuestro procedimiento almacenado para buscar a los clientes de la ciudad “0014” (busquen alguna ciudad que exista en los registros de la tabla para que al menos vean resultados)

pFindCustomers 'City', '0014'

Ahora, exploremos los queries que han sido ejecutados para encontrar el plan de ejecución almacenado.

SELECT C.plan_handle, T.text, P.query_plan
FROM sys.dm_exec_cached_plans C
CROSS APPLY sys.dm_exec_sql_text(C.plan_handle) T
CROSS APPLY sys.dm_exec_query_plan(C.plan_handle) P

Buscaremos en la columna “text” algo que comience con “CREATE PROC pFindCustomers …” y daremos clic en la celda de la columna query_plan.

Sólo demos un vistazo superficial al plan de ejecución para verificar algunas tareas que está llevando a cabo: ixCustomers_City (seek) y pkCustomers (scan)

Ahora en una nueva ventana del Management Studio escribimos lo siguiente:

pFindCustomers 'RegistrationDate', '2012-02-07'

Demos clic derecho y elegimos la opción “Display estimated execution plan” y veremos que el plan es el mismo que habíamos visto antes, entonces sin importar que tengamos una cantidad enorme de posibilidades de búsqueda, siempre se utilizará el mismo plan de ejecución el cual sólo era óptimo para la primera ejecución.

Para este tipo de problemas, podemos modificar nuestro procedimiento almacenado para que cada vez que se ejecute se recalcule el plan de ejecución:

ALTER PROC pFindCustomers(
      @Column VARCHAR(30),
      @Condition VARCHAR(10)
)
WITH RECOMPILE
AS
BEGIN ...

He colocado puntos suspensivos dado que lo demás sigue igual.

En una nueva ventana escribimos el query para buscar por ciudad y elegimos la opción “Display Estimated Execution Plan”, veremos que es el mismo plan que ya habíamos visto previamente.

Ahora volvamos a la ventana donde está nuestro query para la búsqueda por “RegistrationDate” y elegimos de nuevo la opción “Display Estimated Execution Plan”… ¡zaz!, podemos ver que el plan de ejecución es diferente y está optimizado para la búsqueda que ejecutamos.

En la siguiente entrada veremos los siguientes puntos de la lista.