Esta es una instancia CU2 de SQL Server 2016 con 15 GB configurados como uso máximo de RAM. MDOP está en 4. Las bases de datos en este caso son consultadas muy esporádicamente por los usuarios y esta es una réplica secundaria de solo lectura.
El problema: semanalmente todas las consultas entran en el estado RESOURCE_SEMAPHORE debido al agotamiento de la memoria, incluso cuando no se ejecutan otras consultas simultáneamente en este servidor. Después de reiniciar una instancia, las consultas comienzan a funcionar nuevamente. En las imágenes a continuación, notará que nos falta memoria máxima en el espacio de trabajo y que estas son consultas REALMENTE malas (costos de consulta astronómicos).
Antes de reiniciar: solo 1,8 GB de memoria máxima disponible en el espacio de trabajo y TODAS las consultas incorrectas entran en un estado RESOURCE_SEMAPHORE.
Después del reinicio de la instancia: mucha memoria del espacio de trabajo (~11 GB) y ya no hay RESOURCE_SEMAPHORE, incluso para consultas horribles.
Como puede ver, es una consulta repulsiva que solicita ~2 GB de RAM. En esta imagen, SSMS muestra que realmente se otorgó la memoria requerida, porque la instancia se reinició. Antes del reinicio, la concesión de memoria permanece en NULL y la consulta permanece en el estado de espera RESOURCE_SEMAPHORE.
Ahora, lo que nos gustaría saber es: ¿por qué la memoria máxima del espacio de trabajo disminuye con el tiempo y no se libera cuando no hay consultas en ejecución?
Respuesta1
Parece que de una memoria total asignada de 15 728 640 000 bytes, la memoria robada del servidor ocupa 12 828 864 000 bytes, aparentemente no dejando suficiente para las operaciones normales.
La documentación dice en SQL Server, objeto Administrador de memoria:
Memoria del servidor robada (KB)
Especifica la cantidad de memoria que utiliza el servidor para fines distintos de las páginas de la base de datos.
Unrespuestadice mas:
La memoria robada describe los búferes que se utilizan para ordenar o para operaciones hash (memoria del espacio de trabajo de consulta), o para aquellos búferes que se utilizan como un almacén de memoria genérico para asignaciones para almacenar estructuras de datos internas como bloqueos, contexto de transacción e información de conexión. . El proceso de escritura diferida no puede eliminar los buffers robados del grupo de buffers.
La consulta inútil aparentemente realizó una enorme operación de clasificación y SQL Server no libera la memoria asignada. La memoria se toma del Buffer Pool y no se puede utilizar para nada más.
No tienes demasiadas opciones:
- Mejore esa consulta para que no clasifique grandes cantidades de datos.
- Agregue más memoria a SQL Server
- Reinicie SQL Server periódicamente o especialmente después de ejecutar esta consulta.