Surviving the Night

El blog de Pablo Doval sobre .NET, SQL, WinDbg...

Why Do I?: Memoria para los Workspaces en SQL Server (I)

Os voy a confesar un oscuro secreto: tengo un plan. Y no solo eso, sino que es un plan de los buenos. De los de Dominación Mundial™. Curiosamente, y como no podía se de otro modo, ese plan pasa por hacerme bueno, muy bueno en SQL Server1, así que últimamente estoy aprovechando este descanso navideño para estudiar en profundidad algunos temas en los que nunca entre en suficiente detalle, o que tengo olvidados.

Durante las sesiones de estudio me gusta tomar notas de lo que voy repasando, así como preparar unas slides personales sobre el tema, que voy almacenando para futuras referencias y para dar charlas de esas que me gusta hacer de vez en cuando para satisfacer mi lado mas nerd. En esta ocasión, sin embargo, he pensado utilizar parte de esas notas para darle un poco mas de vidilla al blog y aprovechar para escribir de algunas partes de SQL Server de las que no hay demasiada literatura en nuestro idioma.

En esta ocasión, voy a hablaros de una zona de memoria especial de SQL Server, llamada Workspace y que me niego a traducir al castellano :) Mi objetivo para esta mini serie será explicaros algunos fenómenos perniciosos desde el punto de vista del rendimiento en SQL Server (como son Hash Warnings, Sort Warnings, etc…) y su relación con esta misteriosa y desconocida área.

NOTA: Tras muchos intentos en el pasado, soy plenamente consciente de que esta pequeña reactivación de mi blog va a tener un carácter muy temporal; después vendrá el día a día y se llevará todas mis buenas intenciones. Así que, tomaros esto como una pequeña aportación navideña, un regalo de reyes anticipado :P

La Memoria en SQL Server

Esta claro que antes de zambullirnos en la manera en la que SQL Server gestiona la memoria workspace, es importante conocer el funcionamiento básico de la memoria en el producto. Para esta entrada tengo que hacer la suposición de que ya estáis familiarizados con la gestión de memoria básica de SQL Server. Algunas de las cosas básicas que debes de saber antes de continuar leyendo es que…

  • La memoria de SQL Server se divide en dos zonas principalmente:
    • Buffer Pool: Es la caché principal de datos de SQL Server. Aquí se cachean, en páginas de 8Kb, datos, índices, planes de ejecución… Todas las reservas de 8Kb van aquí, y es sin duda la zona mas importante de memoria del producto.
    • Non-Buffer Pool: También llamada mem-to-leave, es la zona de memoria en al que se realizan las reservas superiores a 8Kb.
  • El parámetro de configuración max server memory (MB) limita sólo Buffer Pool
  • El tamaño de la región Non-Buffer Pool viene determinado por la siguiente formula: Memoria Física Total – (Max Server Memory + Memoria Física usada por el OS y otras aplicaciones)

Si no es así, os recomiendo que busquéis alguna introducción básica al tema y luego continuéis leyendo por aquí :)

NOTA: Aunque estas cosas son bastante genéricas, en realidad solo son correctas para un SQL Server 2008 R2 con arquitectura x64.

Este maravilloso diagrama “Windows 8 Modern UI”  que os he preparado, sin bordes, ni colorido, ni ná de ná, muestra la relación entre los componentes de asignación de memoria en SQL Server 2008 R2:

image

Un rápido resumen podría ser el siguiente:

  • Las reservas de memoria de 8Kb o menores se almacenan en el Buffer Pool (mediante en single-page allocator)
  • Las reservas de memoria mayores de 8Kb se dirigen al área mal llamada Mem-to-leave a través del multi-page allocator.
  • Las reservas de memoria del código de CLR que se ejecuta dentro del proceso de SQL Server se dirigen también al mem-to-leave mediante el VAS allocator.

En SQL Server 2012 se han producido bastantes cambios a la gestión de memoria: sin duda, el más importante es que ya no se realiza una diferencia entre el single-page allocator y el multi-page allocator, habiendo sido sustituidos ambos por un único y genérico gestor de reservas de memoria, como se puede ver en el siguiente diagrama:

image

A partir de ahora, tanto las reservas tradicionales menores de 8Kb, como las reservas superiores a 8Kb y las reservas del CLR pasan a estar en los limites de memoria definidos entre el max server memory (MB) y el min server memory (MB).

NOTA: Si habéis hecho una actualización a SQL Server 2012 sin tener en cuenta este cambio, es probable que queráis repasar vuestra configuración de memoria después de leer esto. Tened en cuenta también que que muchas DMVs han cambiado, por lo que scripts de captura y monitorización pueden estar devolviéndoos resultados no fiables.

Una vez descritas las dos grandes zonas de memoria de SQL Server, estoy seguro de que muchos de vosotros ya estáis poniendo en su lugar, dentro de la zona controlada por el Memory Manager (la zona verde), las varias caches de datos que conocéis en SQL Server: para los datos recientemente usados, para los planes de ejecución, etc. Sin embargo, SQL Server tiene mas usos para la memoria principal, y uno de ellos es para la…. ¡¡Ejecución de Consultas!!

<sonido de truenos y lluvia distante, por favor….. …… ¡gracias!>

Ok… ok… se que era predecible :) Pero es un paso importante para llegar a donde os quiero llevar. Vamos a hablar de unas pequeñas estructuras muy amigables que viven de de nuestros planes de ejecución.

Los Iteradores

En realidad, los iteradores (u operadores, pero como en realidad todos son iteradores, prefiero llamarlos asi) son las piezas de lego con las que se construyen nuestros planes de ejecución. Hay aproximadamente un centenar de ellos en SQL Server 2012, y nos sirven para realizar acciones tan variadas como leer registros, realizar filtrados y agregados, actualizar datos, etc… Estos se combinan y son los que finalmente dan lugar a nuestros planes de ejecución.

A nivel interno, todos exponen los mismos tres métodos:

  • Init(): Instancia el operador físico e inicializa todos los recursos que pueda necesitar. Idealmente solo se ejecutará una vez, pero puede ser necesario llamarle múltiples veces: esto suele indicar algún problema y se materializa con un número alto de binds/rebinds en el plan de ejecución.

  • GetNext(): La llamada a este método devuelve la primera fila, o la fila siguiente a la actual. El operador puede recibir cualquier número de llamadas a este método. ¿Entendéis ahora porque llamamos iteradores a todos los operadores? ;)

  • Close(): Este método provoca la ejecución de las tareas de limpieza y eliminación de recursos del operador. Solo puede ser invocado una vez.

Aunque a primera vista parece extraño que *todos* los operadores se implementen básicamente con un GetNext(), esta aproximación hace que sea relativamente fácil implementar nuevos operadores para el Query Optimizer/Query Processor.

NOTA: Aquí tenéis la referencia de los disponibles en SQL Server 2012: http://msdn.microsoft.com/es-es/library/ms191158.aspx

Sigamos: todos los iteradores (Table Scan, index Scan, Compute Scalar, etc…) en un plan de ejecución necesitan algo de memoria para levantar su infraestructura básica, para mantener el estado del iterador y para almacenar las filas que están siendo procesadas. Este último punto es el más interesante, veamos por qué.

Los iteradores pueden ser de dos tipos:

  • La mayoría de los iteradores que nos vamos a encontrar en un plan de ejecución son de tipo streaming; esto es, pedirán una fila, la procesarán y pedirán la siguiente. Evidentemente, en estos escenarios el consumo de memoria del iterador para almacenar las filas que están siendo procesadas será escaso y predecible: siempre se almacena una única fila.
  • Hay unos pocos operadores que son de tipo parcialmente o completamente bloqueantes (blocking). Estos deben de mantener un subconjunto (o la totalidad) de las filas en memoria hasta que finaliza su proceso, en cuyo momento el GetNext() devuelve un bloque de n filas, en lugar de una única filas.

Evidentemente los iteradores parcialmente o completamente bloqueantes consumen mucha más memoria. Un ejemplo básico es el operador sort, que es complentamente bloqueante ya que requiere que todas las filas se almacenen hasta la llegada de la última fila de su operador de entrada, antes de empezar a producir resultados por su salida. Esto es así, porque la última fila en entrar podría ordenarse la primera; es un ejemplo muy claro.

La buena noticia es que son muy pocos los operadores bloqueantes, y se pueden categorizar en estos tres tipos:

  • Hash Match:
    • Hash Match
    • Hash Match (Aggregate)
  • Sort:
    • Sort
    • Sort (Top N Sort)
  • Exchange:
    • Distribute Streams
    • Gather Streams
    • Repartition Streams

Cuando una consulta utiliza este tipo de operadores, tiene que realizar peticiones de memoria especiales a SQL Server para poder realizar las tareas que estos iteradores requieren: estas peticiones de memoria son las que definen el workspace de la consulta (workspace memory grants).

Algunas Preguntas

Lo primero que nos podemos preguntar es, ¿de dónde sacamos esa memoria? Esta pregunta es la fácil; estoy convencido de que todos ya sabéis que viene del Buffer Pool… entre otras cosas porque ya lo hemos comentado antes ;)

La siguiente pregunta es: ¿cuanta memoria se va a reservar de nuestro Buffer Pool para la ejecución de la consulta? De esto se encargará nuestro amigo, el Query Optimizer, que tratará de averiguar el Ideal Grant, es decir, la reserva de memoria total para el plan de ejecución que permitirá que todos los operadores puedan ejecutarse completamente en memoria. Para realizar este calculo del Ideal Grant se utilizarán las estadísticas (histograma de distribución de los valores de cada columna), en concreto el número de filas, y el tamaño por cada fila.

Es interesante aquí darse cuenta de que estamos trabajando con estimaciones. A modo de ejemplo, supongamos un escenario de una consulta simple con un ORDER BY: el Query Optimizer sabe que ese operador necesitara “estimated rows * estimated row size” bytes. ¿porque decimos que son estimados? Bien, imaginaros que el tipo de datos es NVARCHAR(100)… realmente no sabemos lo que ocupa esa columna en cada fila, solo tenemos una estimación.

Para ver la información de la memoria asignada al workspace de una consulta desde el SSMS (y sin tener que recurrir a leer el XML a mano), podemos simplemente mirar las propiedades del operador principal (el SELECT en el ejemplo), y veremos un nodo llamada MemoryGrantInfo:

image

SQL Server impone un limite máximo a la memoria destinada a los workspaces (sobre el 75% del Buffer Pool), para evitar que una consulta extremadamente pesada impida la correcta ejecución de consultas mucho mas livianas y rápidas en el servidor.

Esto puede parecer mucha memoria, pero imaginaros que tenéis un Data Warehouse con una tabla de hechos que os pesa 500 Gb, y queréis realizar algo ‘tan simple’ como un ‘SELECT * FROM FactTable ORDER BY Date’, ¿Podría vuestro servidor mantener todo el operador en memoria? ¿que sucedería?

Las respuestas, ¡en la próxima entrada! ;)

Resumiendo

Buff.. ¡no ha estado nada mal! En una única entrada hemos hablado de la gestión de memoria en SQL Server, de qué son y como funcionan los iteradores y hemos introducido el concepto de iteradores bloqueantes y sus workspaces asociados. Nos queda mucho por ver aún; de hecho, aún no os he presentado ni siquiera los problemas que queremos resolver, pero no he podido evitar la tentación de aprovechar esta mini serie para ir introduciendo algunos elementos de SQL Server de los que no solemos hablar.

Por cierto, si has llegado hasta aquí, ¡enhorabuena! Definitivamente debes de tener interés en SQL Server o mucho tiempo libre, ya que admito que no es el tema más ameno y ligero para una lectura en temporada navideña.

1: Lo siento, pero no puedo explicaros la razón por la que un plan de Dominación Mundial™ requiere un conocimiento extremo las tripas de SQL Server. Bueno… en realidad si que podría explicarlo, pero tendría que mataros después. Y la verdad es que ya os he cogido cariño y esas cosas, así qué mejor no preguntéis :)
Rock Tip:

Si en la última entrada os ponía un temazo de W.E.T., en esta ocasión no me voy a ir muy lejos: ‘Why do I?’ es el tema que abre Artwork, el disco de debut de los suecos Work of Art. I digo que no me voy muy lejos, ya que el guitarrista, principal compositor y lider de esta banda no es otro que Robert Säll, uno de los lideres de W.E.T. de quien ya os hablaba anteriormente.

¿Que deciros de esta banda? Una de las mayores y más agradables sorpresas de mi vida musical: hablamos de un hard rock melódico de mucha, mucha clase, estilo y elegancia. Si duda una de mis bandas favoritas del momento, aunque he de reconocer que les tengo un cariño especial y poco objetivo. Decidieron hacer el primer concierto de su historia en Madrid, en la venerable sala Ritmo & Compás, y os puedo decir que todos los que tuvimos la suerte de vivir ese momento lo vamos a guardar como uno de los conciertos de nuestras vidas.

Keep Rockin’!

Posted: 30/12/2012 3:51 por Pablo Alvarez | con 2 comment(s) |
Archivado en: ,
Comparte este post:

Comentarios

Chema Pérez ha opinado:

Interesante artículo :)

Lo que no me quedó claro (aunque no es importante) es donde se sitúa el workspace dentro del esquema de memoria que pintas, esta dentro del "buffer pool" o del "non-buffer pool" o de ambos?

Saludos y Feliz año !!

# January 2, 2013 1:56 PM

Pablo Alvarez ha opinado:

Gracias Chema!! Buena pregunta :) En realidad lo respondí un poco en la propia entrada, pero quizá no está muy claro: la memoria para el workspace se solicita al Buffer Pool directamente. En SQL Server 2008 R2 y anteriores es a través del Single-Page Allocator, y en SQL Server 2012 mediante el Anysize Page Allocator.

Estoy preparando la segunda entrada, a ver si puedo publicarla para reyes :)

Un abrazote!!

# January 2, 2013 3:22 PM