Memoria, memoria, memoria…

  • Article

En esta entrada vamos a ver que recomendaciones tenemos para configurar la memoria:

¿Que es eso de ‘max server memory’?

La eterna pregunta de ¿cuánta memoria doy a mi servidor?

¿Que problemas pueden surgir si tengo poca memoria?

Empecemos por la primera pregunta:

¿Que es eso de ‘max server memory’? (y de paso “lock pages in memory”)

‘max server memory’ es una de las configuraciones avanzadas de memoria que tiene SQL Server, esta opción limita el tamaño máximo que puede tener el Buffer Pool (Cache usada para las páginas de las bases de datos).

Las recomendaciones sobre el valor que debemos tener en este parámetro se pueden encontrar aquí:

https://msdn.microsoft.com/en-us/library/ms178067.aspx Por defecto SQL maneja su propia memoria, en la mayoría de los casos que SQL Server es el único proceso que requiere grandes cantidades de memoria no es ningún problema. Pero si tenemos otras tareas que sean grandes consumidoras, por ejemplo más de una instancia de SQL Server, o un trabajo de SSIS que trabaje con gran cantidad de datos entonces es cuando pueden surgir los problemas.

Lock Pages in Memory es una función que nos proporciona Windows para que una aplicación pueda bloquear memoria física y esta no sea paginada. Esto le viene muy bien a SQL Server, evidentemente por temas de seguridad ningún proceso puede hacer uso de esta característica si no tiene los permisos necesarios, para que SQL Server haga uso simplemente tenemos que darle los permisos y reiniciar la instancia. Nunca habilitar Lock Pages in Memory sin antes haber establecido un valor para “max server memory”

https://msdn.microsoft.com/en-us/library/ms190730.aspx

Las recomendaciones generales son las siguientes:

  • Entornos de 32bits
    • Menos de 4GB
      • ¡¡No habilitar lock pages in memory!!
      • No establecer el valor de max server memory
    • Mas de 4GB
  • Entornos de 64bits
    • Windows 2003 (y R2)
      • Establecer max server memory (RAM física – 4GB)
      • Establecer lock pages in memory
    • Windows 2008 y superiores
      • Depende de la situacion, aunque generalmente no establecer ninguna de las opciones suele funcionar bien en cuanto tenemos más de un proceso compitiendo por la memoria podemos tener problemas, en este caso la recomendación es establecer lock pages in memory y max server memory a un valor igual a la RAM física menos 4GB para otros procesos y Kernel, si vemos que este valor es demasiado bajo podemos jugar con los valores RAM física – 2GB y RAM física – 4GB
    • En cualquier entorno (x64)
      • Si vemos el siguiente warning en el errorlog de SQL Server A significant part of sql server process memory has been paged out. This may result in a performance deberemos establecer el valor de “max server memory” y habilitar “lock pages in memory”

Por que recomendamos lock pages in memory para Windows 2003

Windows 2003 tiene un algoritmo de paginación muy agresivo y puede paginar una gran porción de la memoria de  SQL Server afectando negativamente al rendimiento. ( https://support.microsoft.com/kb/918483 )

 

A continuación

La eterna pregunta de ¿cuánta memoria doy a mi servidor?

El máximo consumidor de memoria en SQL Server es el buffer pool, por tanto deberemos dimensionar según la cantidad de datos que va a mover SQL Server, por ejemplo si nuestras bases de datos ocupan 10GB tener 20GB no va ayudar mucho más que tener 16 ya que prácticamente todos los datos van a residir en memoria. Para tomar la decisión de ampliar la memoria contamos principalmente con dos contadores de rendimiento dentro del grupo Buffer Manager (https://msdn.microsoft.com/en-us/library/aa173929(v=sql.80).aspx), a saber:

-   Buffer Cache Hit Ratio: Porcentaje de páginas que han sido encontradas en memoria sin necesidad de acceder al disco. La recomendación es que este contador se mantenga en un 99%

- Page life expectancy: Número de segundos que una pagina está en el buffer sin que sea referenciada. Si este contador se mantiene por debajo de los 300 segundos deberemos pensar en aumentar la cantidad de RAM del sistema.

También es posible que debido a la falta de un índice tengamos estos valores bajos ya que en vez de hacer un “seek” sobre un índice hacemos un “scan” necesitando mas memoria para resolver la query, por tanto antes de ampliar RAM deberemos estudiar si optimizando nuestras queries podemos reducir el consumo de RAM.

 

y por último

¿Que problemas pueden surgir si tengo poca memoria?

El principal problema que veremos es un mayor acceso a los discos con la consiguiente perdida de rendimiento. Intermitentemente SQL Server imprime en el errorlog información de memoria si detecta que hay fallo de memoria, en casos extremos SQL puede dejar de responder generando volcados de memoria, generalmente de tipo Non-yielding IOCP Listener seguido de un mensaje relaccionado con la memoria como por ejemplo: “LazyWriter: warning, no free buffers found”

2012-08-13 09:18:32.62 Server **Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000
2012-08-13 09:18:32.65 Server ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.txt
2012-08-13 09:18:32.65 Server * *******************************************************************************
2012-08-13 09:18:32.65 Server *
2012-08-13 09:18:32.65 Server * BEGIN STACK DUMP:
2012-08-13 09:18:32.65 Server * 08/13/12 09:18:32 spid 0
2012-08-13 09:18:32.65 Server *
2012-08-13 09:18:32.65 Server * Non-yielding IOCP Listener
2012-08-13 09:18:32.65 Server *
2012-08-13 09:18:32.65 Server * *******************************************************************************
2012-08-13 09:18:32.65 Server * -------------------------------------------------------------------------------
2012-08-13 09:18:32.65 Server * Short Stack Dump
2012-08-13 09:18:32.92 Server Stack Signature for the dump is 0x0000000000000278
2012-08-13 09:18:47.46 spid2s LazyWriter: warning, no free buffers found.

Con esto termino este articulo, cuidado con asignar poca memoria al servidor!

Pablo Gavela López – Microsoft Customer Support Services