Artículo "Visualización de grandes conjuntos de datos en ASP.NET" publicado en DotNetManía: Correcciones y recursos.

En artículo publicado en DotNetManía de este mes, comentaba en un punto que « …  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é …«. Que aunque no afecta a la esencia del artículo, para nada es correcto. Los procedimientos almacenados en SQL Server no se compilan. Se compila su plan de ejecución y es tratado en la caché como un elemento más con las mismas politicas que por ejemplo una entrada marcada como prepared_query. Esta compilación se lleva acabo en la primera invocación del procedimiento almacenado y puede ser recompilado a lo largo del tiempo por diversos motivos. Si os interesa podeis consultar el post «Caché de planes de ejecución en SQL Server 2005 y comportamiento de los procedimientos almacenados» dónde explico con detalle preguntas lógicas sobre el rendimiento de los procedimientos almacenados con consultas y consultas marcadas como prepared_query.


Por otra parte a todos aquellos que esteis interesados en obtener el código fuente del artículo podeis descargarlo haciendo clic aquí

Caché de planes de ejecución en SQL Server 2005 y comportamiento de los procedimientos almacenados

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.
Y puede que las consultas preparadas tengan mejor rendimiento que las incluidas en un procedimiento almacenado por la siguiente razón: Si un procedimiento almacenado se ejecuta en una base de datos D1, su plan de ejecución no es reutilizado si se ejecuta en una base de datos distinta D2. En cambio las consultas ad-hoc, las prepared y las dinámicas SI.

Los planes de ejecución se reutilizan entre usuarios, los contextos de ejecución (p.e. unos valores de parámetros determinados) no porque son afines a la sesión del usuario (lógicamente). Así que, aunque se cachean, el impacto es más bien escaso. Por eso es muy importante envíar consultas con parámetros explícitos o crear procedimientos almacenados, si bien SQL Server 2005 tiene una característica de autoparametrización de consultas que se repiten.

Es MUY IMPORTANTE utilizar nombres calificados a la hora de referenciar objetos de la base de datos. En otro caso no podrán SER REUTILIZADOS entre usuarios.

Por ejemplo: Supongamos que hay dos tablas foo: marta.foo y jaime.foo en una misma base de datos. El plan de ejecución «select * from users» para el usuario marta no sería compartido con el de jaime. Son tablas distintas. También ocurre cuando solo tenemos una tabla, por eso es importante hacer siempre «select * from dbo.users». En ese caso el UID coincide (-2) y puede ser compartida entre distintos usuarios.

 

¿Cual es el tamaño del caché de planes de ejecución?¿cúal es la política para descartar elementos?

Para empezar no hay un solo caché de planes de ejecución… realmente hay cuatro, pero dos son los importantes: hay uno para las consultas marcadas como ad-hoc y para las marcadas como prepared query y otro para los procedimientos almacenados. Podríamos pensar que estaría bueno balancear y crear prepared queries y proc. alamacenados para llenar equitativamente las dos cachés. Es una tontería, porque en el caso de que uno se llene y queden libres en el otro se le asigna el espacio sobrante al que le hace falta.

 

Con la siguiente consulta obtenemos los siguientes tamaños de entradas para cada plan de caché:
 

select name, type, buckets_count from sys.dm_os_memory_cache_hash_tables
where type in (‘CACHESTORE_OBJCP’ , ‘CACHESTORE_SQLCP’, ‘CACHESTORE_PHDR’, ‘CACHESTORE_XPROC’)

 

Por ejemplo en mi sistema (SQl Server Express 2005)

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?


En bastantes circustancias, solo comento las principales (mirar mejor la documentación mencionada) y se agrupan en dos:


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.


Conclusión

Los planes de ejecución de los Procedimientos Almacenados son creados en la primera invocación de la instancia del servidor SQL Server y son tratados como una entrada más en la caché de planes de ejecución. No parece que haya ninguna ventaja en utilizar procedimientos almacenados en SQL Server 2005 para mejorar el rendimiento de consultas preparadas, especialmente en lo que se refiere al caché de planes de ejecución. Por lo que no compensa su creación.