¿Cómo recompilar todos los procedimientos almacenados y las funciones con valores de tabla en la base de datos de SQL Server?

Posteriormente a consultar con expertos en la materia, programadores de varias áreas y profesores dimos con la solución al problema y la plasmamos en este post.

Solución:

Tu puedes correr sp_recompile en todo usando un cursor para producir SQL ad-hoc para cada uno y ejecútelo, si cree que eso ayudará:

DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF'));
DECLARE @name SYSNAME;
OPEN C;
FETCH NEXT FROM C INTO @name;
WHILE @@FETCH_STATUS=0 BEGIN
    EXEC sp_recompile @name;
    FETCH NEXT FROM C INTO @name;
END;
CLOSE C;
DEALLOCATE C;

o podría producir SQL ad-hoc y ejecutarlo a través de EXECtoma menos código que podría ser marginalmente más eficiente:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_recompile '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF');
EXEC (@sql);

(aunque encuentro que este formulario a veces arroja a la gente debido a buscando basado en conjuntos pero construyendo el string de forma iterativa, y no siendo un patrón SQL estándar)

Otro conjunto de objetos que podría ser una preocupación similar aquí son las vistas. De manera similar, puede marcarlos como que necesitan ser reevaluados para asegurarse de que los planes almacenados y otros metadatos no estén obsoletos con sp_refreshviewmediante pequeñas modificaciones en el cursor o en los métodos SQL ad-hoc que se muestran arriba:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_refreshview '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('V');
EXEC (@sql);

Los planes de ejecución muestran que más del 80 por ciento del tiempo de ejecución está en una búsqueda de índice agrupado, por lo que no creo que pueda hacer mucho más para optimizar los procedimientos almacenados.

A veces hay más en la optimización que preferir las búsquedas a los escaneos, etc., a veces un escaneo de índice es más eficiente que muchas ejecuciones de operaciones de búsqueda, y las estimaciones de costos sobre las que se calculan las cifras porcentuales que está viendo son (estimaciones) en mejor (una guía útil pero a veces lejos de ser precisa).

Si bien “agregar más memoria” puede ayudar con algunos problemas de rendimiento de la base de datos, al menos temporalmente, si los cuellos de botella están muy vinculados a la CPU en lugar de a la memoria y/o a la E/S, agregar más memoria tendrá muy poco efecto.

Si agrega memoria (incluso si se agrega en caliente a una máquina virtual) y aumenta la memoria máxima del servidor para que coincida, la memoria caché de su plan se borrará.

Eso es efectivamente ‘recompilar’ todas esas cosas que mencionó, porque no tendrán un plan almacenado en caché para reutilizar. SQL Server tendrá que construir uno nuevo.

Sin embargo, es posible que nunca haya configurado Max Server Memory. Si no está seguro de eso, puede ejecutar DBCC FREEPROCCACHE para borrar el caché del plan.

Usted hace esto bajo su propio riesgo en la producción. No puedo garantizar que el nuevo plan sea mejor.

La memoria no resuelve todos los problemas de rendimiento en SQL Server, y Seek no es necesariamente la meta del ajuste del rendimiento.

Si necesita ayuda con una consulta específica, debe hacer una pregunta por separado.

Esta solución se basa en la otra respuesta aquí, pero tiene en cuenta esquemas y considera caracteres especiales en el nombre.
Recompila todos los procedimientos, funciones y funciones en línea. en todos los esquemas.

create procedure dbo.RecompileAllProcedures
as
begin
    declare cur cursor for 
    (
        select quotename(s.name) + '.' + quotename(o.name) as procname
        from 
           sys.objects o
           inner join sys.schemas s on o.schema_id = s.schema_id
        where  o.[type] in ('P', 'FN', 'IF')
    );

    declare @procname sysname;

    open cur;
    fetch next from cur into @procname;
    while @@fetch_status=0 
    begin
        exec sp_recompile @procname;
        fetch next from cur into @procname;
    end;
    close cur;
    deallocate cur;
end;

Entonces se puede llamar de la siguiente manera:

exec dbo.RecompileAllProcedures

PD: soy un gran admirador del estilo de codificación de Allman;)

EDITAR: Se mejoró el procedimiento usando quotename que es más seguro en caso de que el nombre contenga un corchete.

Reseñas y valoraciones del tutorial

Si estás contento con lo expuesto, tienes el poder dejar un escrito acerca de qué le añadirías a este artículo.

---

---

Utiliza Nuestro Buscador