Esta mañana Iván me preguntaba sobre una afirmación que ponía en las conclusiones de artículo «Visualización de grandes conjuntos de datos en ASP.NET» publicado en la DotNetMania de este mes. Decía « … también se planteó la posibilidad de utilizar procedimientos almacenados, aprovechando que se compilan y su plan de ejecución nunca se descarta de un caché …«. Esto es totalmente incorrecto y quizas me deje llevar por mis viejas experiencias con Informix y 4GL. [:P]
Además nos empezaron a surgir bastantes dudas sobre el comportamiento de los planes de ejecución de los procedimientos almacenados.
¿Los Procedimientos almacenados se compilan? ¿cuándo lo hacen?
¿Hay alguna diferencia en la forma de tratar la caché de los planes de ejecución marcados como prepared_query o stored proc?
¿Cuál es el tamaño del caché de planes de ejecución?¿Cuál es la política para descartar elementos?
¿Cuándo se invalida el plan de caché, datos etc y se precisa de recompilación?
Documentación consultada
Primero miré algunos blogs sobre SQL Server según los devolvía google y acabé leyendo bastantes opiniones que se contradecían unas a otras relacionadas con las preguntas anteriores. En algún sitio ponían que los procedimientos almacenados eran compilados en el momento de ser creados… en fin…
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005. Explica como funciona el caché de planes de ejecución con detalle para batchs (lotes) con sentencias SQL, incluidos procedimientos almacenados. También explica las diferencias principales de SQL Server 2000 y 2005 al respecto.
En el post 9.0 Memory Pressure Limits publicado en el blog SQL Programmability & API Development Team Blog, que siempre recomiendo para entender bien cómo funciona SQL Server por debajo encontré información adicional sobre el número de cachés de planes de ejecución y cómo se comportan en condiciones de poca memoria disponible. En el post What are the different cached objects in the plan cache? Ketan Duvedi nos explica los diversos tipos de objetos que se almacenan en las cachés de planes de ejecución
Por último en SQL Server Best Practices encontré una colección de enlaces a whitepapers y recursos con buenas prácticas para SQL Server (especialmente 2005)
Antes de responder a las preguntas conviene entender lo que es un batch o un lote de sentencias en SQL Server: Entenderemos por Batch o lote por un conjunto de sentencias SELECT, INSERT, DELETE, UPDATE, llamadas a prodecimientos almacenados. También se incluye todas las sentencias intermedias de Transact-sql, sentencias GRANT, DENY, etc… es la unidad de compilación e inserción en el caché pero cada sentencia individual. El batch es la unidad de compilación de plan de ejecución y cómo tal se guarda en la caché de planes de ejecución. Para cada sentencia del batch se guarda también su plan de ejecución en la caché, pero jerárquicamente dependen del batch.
Los Procedimientos almacenados se compilan? ¿cuándo lo hacen?
Pues primera discrepancia con lo que dije: los procedimientos almacenados estríctamente hablando no se compilan. Lo que se compila es el plan de ejecución y se almacena en la caché de planes de ejecución. El plan de ejecución se crea la primera vez que se invoca el procedimiento almacenado. Y es tratado como una entrada más en la caché de los planes de ejecución, por tanto puede llegar a ser decartado. Por cierto los batch no debería superarse los 8KB de tamaño (ojo con los literales grandes, BLOBs, etc.) si queremos que entre la caché de planes de ejecución. Una mejora importante de SQL Server 2005 respecto a 2000 es que cuando es necesario recompilar el plan de ejecución de una sentencia en un batch (por diversos motivos, porque ha cambiado el esquema, etc.) se recompila solo esa sentencia y no el batch completo, como ocurría en SQL Server 2000.
¿Hay alguna diferencia en la forma de tratar la caché de los planes de ejecución marcados como prepared_query o stored proc?
A parte de ser objetos distintos (consulta parametrizada vs. proc. almacenado), en principio parece que no, según la documentación consultada ambos favorecen la reutilización de su plan de ejecución. De hecho las consultas marcadas como prepared query se han lanzado con SQLPrepare(), al igual que sp_executesql favorecen la reutilización del Plan. Los proveedores de OleDB y Odbc lo utilizan y como comprobé el proveedor nativo de SqlServer de ADO.Net con este hecho no hay diferencia entre tener una consulta prepared (parametrizada) y un procedimiento almacenado que realiza dicha consulta pasando los parámetros. Aunque parezca poco importante es muy revelador porque aun hay mucha gente que introduce consultas en procedimientos almacenados porque cree que es la única manera de aumentar el rendimiento al asegurar la reutilización del plan de ejecución.where type in (‘CACHESTORE_OBJCP’ , ‘CACHESTORE_SQLCP’, ‘CACHESTORE_PHDR’, ‘CACHESTORE_XPROC’)
Object Plans (CACHESTORE_OBJCP) 10007
SQL Plans (CACHESTORE_SQLCP) 10007
Extended Stored Procedures (CACHESTORE_XPROC) 127
Respecto a la política para descartar elementos se basa en el espacio de memoria reservado y la reutilización del plan de ejecución. En ningún momento se desprende un trato especial a los procedimientos almacenados. Concretamente en SQL Server 2005 hay ciertas diferencias con la política de caché de SQL Server 2000. Los cachés de planes de ejecución y de datos son distintos. El lazy-writter que decrementa periodicamente el coste de un plan de ejecución (y si es 0 en caso de poca memoria se descarta) en SQL Server 2000 no existe como tal en SQL Server 2005. En vez de eso, cuando las entradas en el caché superan el 50% del tamaño destinado, el siguiente plan que llega al caché decrementa el contador de coste en 1 de todos los anteriores. Realmente este decremento es marcado a lo cerdito (piggybacked) y no es inmediato por el comportamiento, en la práctica, es parecido al del lazy-writter de SQL Server 2000. Cuando el tamaño del caché supera el 75% un hilo con un monitor dedicado se activa y decrementa el coste de los objetos en todas (ojo todas) las cachés. Si el plan se vuelve a utilizar se resetea este contador.
¿Cuándo se invalida el plan de caché, datos etc y se precisa de recompilación?
A. Razones relativas a la corrección del plan
A.1. Modificación en el esquema que afecta a la sentencia (p.e. se borra una tabla)
A.2. Llamadas a sp_recompile en un proc. almacenado, logicamente 😉 o que fuese creado con la opción WITH RECOMPILE
A.3. Operaciones que causan la invalidación total del plan de caché, a saber: Hacer un detach. Actualizar una bd (de 2000 a 2005). Ejecutar DBCC FREEPROCCAHE. Comando Reconfigure. Comando ALTER DATABASE … MODIFY FILE GROUP o COLLATE. Reiniciar el servidor o el servicio de SQLServer también causa la invalidación total de los planes (lo he comprobado)
A.4 Cambios en alguna opción SET antes de la consulta: ANSI_NULL_DFLT_OFF, ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, FORCEPLAN, LANGUAGE, NO_BROWSETABLE, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER. Es mejor establecerlos al principio de la conexión o de la sesión o dejárlos fijos en la BD.
B. Razones relativas a nivel de optimismo del plan.
B.1 Cuando todas las tablas de la consulta están marcadas como de solo lectura el plan no se recompila. Si la consulta tiene la opción KEEPFIXED tampoco se recompila
B.2. Cuando ciertos indicadores dicen que la consulta ya no es óptima y necesita ser recompilada… depende de bastantes factores como por ejemplo las estádisticas referentes a las tablas implicadas y su tipo (si es variable, temporal, etc.). Para más info verlo en el documento en la sección «Plan optimality.related recompilations: The Big Picture».
Evidentemente, aunque no lo incluye el documento queda la opción de que el plan de ejecución sea descartado de la caché por problemas de espacio.
Excelente articulo, tenía horas y horas buscando documentacion al respecto.
Gracias mil!