Cuellos de botella por memoria
Como se ha hablado en ocasiones anteriores, el recurso más importante para SQL Server es la memoria, en las versiones más nuevas ya todo es controlado por medio del Max Memory setting de SQL Server (bueno casi todo), en versiones 2008 R2 y anteriores esto solo administraba el buffer pool, aunque este es el que tiene el consumo más alto no es todo lo que controla por el Max Memory, ya que quedan fuera ciertos binarios y el CLR por citar unos ejemplos.
Muchas veces vemos problemas que parece ser de IO, CPU, consultas mal diseñadas, falta de índices, lentitud del OS, cuando el problema es realmente por insuficiente memoria o presión de memoria, de la misma manera la presión de memoria puede ser causada por consultas ineficientes.
Entre los más comunes tenemos:
Causas:
- Tener que cargar nuevamente la información debido a que no vive lo suficiente en memoria y sucede un flush (más lecturas, más escrituras, page life expectation bajo, cache hit ratio bajo).
- Lentitud al responder a las consultas (lectura lenta de información genera lecturas y escrituras debido a que la información no queda en memoria).
- Presión al sistema operativo u otras aplicaciones si el max memory setting no esta puesto, en especial en sistemas x64.
- Las instancias compiten por recursos en especial cuando existan varias, cuando el max memory setting de todas es mayor a los recursos del sistema.
- Los clúster deben de tener especial cuidado en el ultimo punto si son activo/activo y varias instancias pueden hacer failover al mismo nodo.
- Competir con productos relacionamos como lo son SSAS contra SQL Server ya que no tienen una configuración única.(como nota SSAS usa un % de la memoria, mientras que SQL usa tamaño en MB).
- Abuso de índices sobre un campo
Efectos:
- Alta paginación cuando no encuentra algo o la memoria es insuficiente, SQL intentara paginar lo más posible para uso como memoria, lo cual puede crear presión en los discos y lentitud generalizada.
- La paginación nos lleva al problema que por su misma lentitud nos causa que el sistema vaya más lento lo cual puede provocar que los tiempos de respuestas no sean óptimos o locks (deadlocks).
- Consultas que no tienen los índices necesarios para estar trabajando pueden desencadenar muchos de los escenarios descritos.
- Varias tareas de mantenimiento, estas pueden causar problemas en la tempdb.
Page Life Expectation, de lo cual hablamos hace poco aquí:
SELECT [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
Este valor debe de estar abajo de 500 de manera recomendada, aunque la formula correcta esta en el post anterior.
Buffer Cache hit ratio, este valor siempre debe de ser positivo, arriba de 90% es bueno, entre mas bajo indica presión de memoria, 70% es malo, cualquier número abajo de este valor es terrible e indicara presión de memoria.
SELECT [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'
Pages per second, el único valor que nos importa aquí es el lazy writer, cualquier valor positivo en el nos indica presión en memoria normalmente.
SELECT [counter_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] IN ('Page reads/sec', 'Page writes/sec', 'Lazy writes/sec')
Memory grants pending
SELECT [cntr_value]
FROM sys.dm_os_performance_counters
WHERE
[object_name] LIKE '%Memory Manager%'
AND [counter_name] = 'Memory Grants Pending'
Aunque ninguno de estos por si solo indica completamente presión y se debe de hacer una captura constante para poder asegurar un resultado óptimo y que no haya sido solo un pico de utilización, existen unos puntos importantes aun de cubrir para máquinas virtuales pero lo cubriré en otro post.
Comentarios
Publicar un comentario