Efectos de DBCC FREEPROCCACHE, y DBCC DROPCLEANBUFFERS en SQL Server

Cuando te encuentras optimizando tus sistemas de bases de datos, el escenario que se suele intentar optimizar es como si el servidor estuviera “limpio”. Es decir, como si no tuviera en memoria ninguna de las filas que necesita para procesar la consulta. Por otra parte, te sueles encargar de limpiar los buffers de SQL Server para que el procesamiento de la consulta/procedimiento almacenado tenga que asumir el coste de compilar y general el plan de ejecución adecuado. Al final obtendremos valores como duración total del proceso (consulta/procedimiento almacenado), y CPU utilizada (incluida la utilizada para compilar el plan de ejecución).SQL Server tiene unos comandos documentados que precisamente nos “intentan” acercar a dicho escenario: buffer de datos completamente vacio, y caché de procemiento limpio (sin planes de ejecución alojados en memoria).

¿Qué hacen estos comandos DBCC?

Como hemos comentado, estos procedimientos se encargan de limpiar el caché de datos, y caché de procedimiento de la instancia de SQL Server.

DBCC FREEPROCCACHE

Comando que elimina todos los planes de ejecución de la Caché de procedimiento. Como veremos más adelante, hay contadores del monitor de rendimiento (performance monitor), que nos muestra el número de páginas de memoria utilizadas para guardar planes de ejecución. En el fondo, un plan de ejecución es un árbol relacional de resolución de la consulta, un conjunto de relaciones con operadores físicos, y lógicos, y una secuencia de predicados para “potenciales” variables de la consulta. Todo esto ocupa espacio, y es guardado en páginas de 8Kb; evidentemente si esta información se guardara en disco, el coste de tener que recuperar el plan de ejecución desde el sistema de almacenamiento sería alto. Por ello, SQL Server los planes de ejecución compilados los guarda en memoria, porque es un recurso al que se accede muy rápidamente.

DBCC FLUSHPROCINDB(dbid)

El efecto es el mismo que FREEPROCCACHE, pero su ámbito se limita al identificador de base de datos especificado en el argumento “dbid”. Funciona en SQL Server 2000, y SQL Server 2005, pero no está documentado, por lo que si surge algún problema y se lo comentas a soporte de MS, es posible que no te den soporte. Yo creo que se le ha pasado al equipo de documentación de SQL Server documentarlo. Por mi propia experiencia, nunca he visto que hayan surgido problemas por usar este comando no documentado.

DBCC DROPCLEANBUFFERS

FREEPROCCACHE es a planes de ejecución como DROPCLEANBUFFERS a páginas de datos. Los datos, y los planes de ejecución se guardan en dos secciones diferentes de la memoria. Con DROPCLEANBUFFERS, lo que haces es elimiar todo el contenido de páginas de datos que existe en memoria. Importante: “sólo” limpia páginas de datos, es decir, la información que SQL Server tuvo que leer de disco para procesar consultas… para ser más concretos, si una consulta necesita todos los nombres de clientes, esa información, antes de viajar al cliente, pasa por la memoria de SQL Server; esta es la información a la que nos referimos con DROPCLEANBUFFERS.

Sus efectos

Estuve en Gran Canaria dando un curso de optimización de SQL Server. Mi sensación del curso fué muy positiva; ya sabes, según va pasando el curso vas viendo que los alumnos se interesan más por la materia que estás impartiendo, y a cada día que pasaba, preguntas más interesantes surgían.

El curso fué en un formato de dos grupos, cuatro horas diárias cada grupo, por lo que los que recibian la clase por la mañana, cuando llegaba la tarde tenían oportunidad de aplicar nuevos conceptos en su día a día. Desde ese punto de vista, los alumnos agradecieron ese formato porque cada día, volvían a clase con nuevas preguntas sobre lo que habían “descubierto” en su turno opuesto.

Yo, como mi principal actividad es optimización de sistemas, uno de los días, me acercé a su oficina a ver la “salud” de su servidor; creamos la pertinente traza de performance monitor, la dejamos funcionando durante un día, y al día siguiente analizamos los resultados; los resultados que obtuvimos fueron los que puedes ver en la imagen:

Supongo que has identificado en la imagen unos “bajones” de lo que quiera ser la linea roja tremendos ¿verdad? Vamos a ver que representan las líneas más significativas.

En primer lugar, estamos hablando de un servidor 64bits, con SQL Server 2000 Enterprise Edition, con 10GB de memoria asignada a SQL Server.

• Procedure cache pages — Línea Verde. Páginas dedicadas a almacenar planes de ejecución y contextos de ejecución; el valor máximo es de 383005 * 8 / 1024 = 2992 Gb; fíjate en los bajones pronuciados.
• Database pages — Línea Roja. Representa el número total de páginas dedicadas a datos (es decir, los datos, los pedidos, los clientes, etc…). Con un valor máximo de 993802 páginas que son * 8 / 1024 = 7764 Mb; sin embargo fíjate que pronunciados son los bajones que aparece.
• SQL Compilations/sec — Línea Marrón. Numero de compilaciones que suceden por segundo: cuando se va a ejecutar una consulta ad-hoc (generalmente no parametrizada), o un procedimiento almacenado que no tiene en caché el plan de ejecución, hay que parsearlo, y compilarlo. Este contador mide precisamente eso. En la escala de la imagen, su valor máximo está sobre el 20 que serían 200 compilaciones por segundo. Fíjate que cuando el resto de los contadores baja, este precisamente sube… Curioso…
• Total Pages — Línea Negra. Número total de páginas asignadas a SQL Server. Fíjate que es un valor constante de 1310720 páginas que son 10240 Mb (1310720 * 8 / 1024).
• Connection memory (KB) — Línea Amarilla. Memoria asignada a las conexiones. fíjate que en rango comprende a una jornada normal de trabajo; algo así como desde las 7.00Am hasta las 05.00PM de la tarde… Cómo se nota la jornada intensiva en verano !!!! :)Supongo que ya has notado que representan esos bajones; efectivamente, ese es el efecto de la limpieza que hacen los comandos DBCC que hemos descrito; vamos a razonar un poco: se nota un desplome generalizado de todos los contadores excepto de la memoria para conexiones, y del número de compilaciones por segundo que se incrementa. Tengo que reconocer que en la imagen me falta el contador de uso de procesador que este caso, por cada pico de bajada, tendría un pico grande de subidas de procesador. También tendría que haber incorporado la información de IO, pero bueno, aunque esa información no la tengo porque ya acabó el proyecto, sí podemos asumirolo.¿Por qué razón subiría la CPU? Si se desploma el caché de procedimiento, habrá que compilar las peticiones que llegan… esa compilación se hace con usando CPU del sistema.¿Por qué razón subiría el uso de disco? Si la memoria de datos se “ha perdido” las consultas que lleguen tendrán que leer los datos de algún sitio, ¿no? si no están en memoria, pues tendrá que ir a disco a recuperarlos.¿Por qué el gráfico no representa un bug de SQL Server? ¿No podrían ser periodos en los que SQL Server pierde la memoria? Como diría David Carmona, “me gusta que me hagas esta pregunta” :). Si fuera perdida de memoria, el contador de color negro (las páginas totales asignadas a SQL Server), también se habría desplomado.

Conclusión

Me sentí un poco “satisfecho” de ver esto, porque como hablamos durante la clase del día siguiente, eso era significativo de que durante su trabajo diario, aplicaban las cosas que aprendían en sus cuatro horas anteriores. Evidentemente, no es deseable que suceda, pero fué un sintoma de que mis amigos me oían durante la clase 🙂

¿Qué solución se adoptó?

Quitar al equipo de desarrollo del role de servidor sysadmin. Esto dió algunos problemillas porque había algunos procedimientos que no tenían el propietario apropiado, pero sí conseguimos evitar posteriores “desplomes” de memoria.