Volver a crear y sp_recompile

---

4. Recrear el procedimiento almacenado.

Otra opción que tenemos para obligar a que el plan de ejecución vuelva a calcularse es actualizar o volver a crear el procedimiento almacenado.

No me gustaría plantear estas dos opciones como posibles soluciones al problema de la actualización del plan de ejecución, y esto es dado que no siempre tendremos acceso al
código fuente de los objetos que componen a la base de datos.

Supongamos que estamos trabajando en la optimización de acceso a datos de una empresa de administración de inversiones y muchos de sus procedimientos fueron creados con la
opción WITH ENCRYPTION para proteger su código fuente, es muy probable (o seguro) que no te den acceso al código fuente de los procedimientos dado que podrían tener información
confidencial.

Más que como solución, deberemos entender que esto es una implicación, deberemos aplicar los cambios a los objetos cuando el sistema o el servidor se encuentre en una ventana de
mantenimiento y así afectar en lo mínimo el tiempo de respuesta del servidor.

5. Ejecutar el procedimiento almacenado sp_recompile

Este procedimiento recibe como parámetro el nombre de un objeto (no solamente de un procedimiento almacenado).

Al ejecutarlo con el nombre de un procedimiento almacenado como parámetro, estaremos indicando que su plan de ejecución vuelva a calcularse.

Pero la utilidad de este procedimiento va mucho más allá.    Supongamos que hicimos cambios o mejoras en la estructura de almacenamiento u ordenamiento de una tabla en
particular, si queremos que todos los objetos "precompilados" vuelvan a calcular su plan de ejecución para que tomen en cuenta las recientes modificaciones en la estructura de
nuestra tabla, basta con ejecutar el procedimiento sp_recompile pasándole como parámetro el nombre de nuestra tabla, veamos un ejemplo.

Supongamos que tenemos la siguiente estructura de información:

CREATE TABLE Students(
  Id SMALLINT IDENTITY(1, 1) NOT NULL,
  FirstName VARCHAR(30) NOT NULL,
  LastName VARCHAR(30) NOT NULL,
  DOB DATE NOT NULL,
  Notes VARCHAR(72) NOT NULL,
  RegistrationDate DATE NOT NULL,
  CONSTRAINT pkStudents PRIMARY KEY (Id)
)

Y la llenamos con datos:

DECLARE @FirstName VARCHAR(30)
DECLARE @LastName VARCHAR(30)
DECLARE @RegistrationDate DATE
DECLARE @DOB DATE
DECLARE @Notes VARCHAR(64)
DECLARE @i SMALLINT = 0
DECLARE @Length INT
WHILE @i < 10000
BEGIN
  SET @Length = ABS(CHECKSUM(NEWID()) % 8) + 1
  SET @FirstName = SUBSTRING(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 1, @Length)
  SET @Length = ABS(CHECKSUM(NEWID()) % 8) + 1
  SET @LastName = SUBSTRING(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 1, @Length)
  SET @Length = ABS(CHECKSUM(NEWID()) % 80)
  SET @RegistrationDate = DATEADD(DAY, @Length, '2012-02-01')
  SET @Length = ABS(CHECKSUM(NEWID()) % 800)
  SET @DOB = DATEADD(DAY, @Length, '1980-01-01')
  SET @Notes = CAST(NEWID() AS CHAR(36)) + CAST(NEWID() AS CHAR(36))
 
  INSERT INTO Students (FirstName, LastName, DOB, Notes, RegistrationDate)
  VALUES (@FirstName, @LastName, @DOB, @Notes, @RegistrationDate)
 
  SET @i = @i + 1
END

Creamos nuestro procedimiento almacenado y lo ejecutamos:

CREATE PROC pGetStudentsByDate(
  @RegistrationDate DATE
)
AS
BEGIN
  SELECT Id, FirstName, LastName
  FROM Students
  WHERE RegistrationDate = @RegistrationDate
END
GO
pGetStudentsByDate '2012-03-24'

Ahora veamos el plan de ejecución de nuestro procedimiento, podemos abrir un nuevo editor para queries y escribimos el código para ejecutar nuestro procedimiento almacenado,
damos clic derecho sobre el editor y elegimos la opción "Display Estimated Execution Time".    Veremos que nuestro procedimiento almacenado está llevando a cabo un barrido
completo de la tabla a través del índice pkStudents.

Vamos a crear un índice sobre nuestra tabla para optimizar la búsqueda de alumnos por fecha de registro:

CREATE NONCLUSTERED INDEX ixStudents_RegistrationDate
ON Students (RegistrationDate)
INCLUDE (FirstName, LastName)

Si volvemos a mostrar el plan de ejecución de nuestro procedimiento, veremos que no ha cambiado; entonces utilizaremos sp_recompile para obligar al cálculo de un nuevo plan de
ejecución, pero lo haremos sobre la tabla para demostrar que también funciona de esa forma:

sp_recompile 'dbo.Students'

Si volvemos a nuestra ventana del query y mostramos el plan de ejecución de nuestro procedimiento almacenado, veremos que ahora está utilizando nuestro índice (seek) para obtener los registros deseados (ixStudents_Registration)

Espero les sea de mucha utilidad.