Errores de memoria virtual VMSTAT provocan desconexiones en los clientes y lentitud en SQL Server.
VMSTAT.EXE
El error VMSTAT suele aparecer con un desbordamiento de memoria asociado al alto consumo de componentes en uno o más nodos de Clúster Windows Server 2003 o Windows 2003 R2 con SP2 y SQL Server 2005.
Executing VMSTAT for SQL instance "Instance Name" (pid 8876)...vmstat: cannot open 8876, error 5: Access is denied.
A su vez en el visor de sucesos (Event Viewer) aparecen los siguientes mensajes de error (Application Log, System Log)
event 26040 Server TCP provider has stopped listening on port [port ] due to a failure. Error: 0x2747, state: 1. The server will automatically attempt to reestablish listening.
SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: x.x.x.x]
event 26041 Server TCP provider has successfully reestablished listening on port [ port ].
Los eventos 26040 y 26041 son asociados a finalización de conexiones TCP en las instancias de SQL ocasionados por sobre utilización de recursos. Pero en el system log también puede aparecer el número de evento 333 que indica una falla irrecuperable al leer el registro, flushear los datos y depositar correctamente las escrituras en el disco duro.
Event ID: 333 "An I/O operation initiated by the Registry failed unrecoverably.
The Registry could not read in, or write out, or flush, one of the files that
contain the system's image of the Registry."
Cuando este error aparece podemos tener la certeza de que estamos teniendo un posible cuello de botella en memoria. Hay que buscar el evento 2019, este mensaje indica una mala asignación de memoria libre en el sistema.
event 2019 Srv The server was unable to allocate from the system nonpaged pool because the pool was empty.
Problema/Impacto:
Todos estos errores asociados pueden estar causando la degradación aplicativa en nuestras bases de datos, desconexiones, logins fallidos, lentitud de procesamiento, consumo de CPU alto (más del 90%) y consumo de memoria excesivo. Nuestros clientes se quejaran que la base de datos es inaccesible, no pueden ejecutar queries o bien lentitud en el sistema.
Pasos de Posible Solución:
Aplicar los siguientes 2 procedimientos para mejorar el desempeño de memoria
https://support.microsoft.com/kb/312362/en-us
https://support.microsoft.com/kb/324446/en-us
- Ver que el ASR (Automatic Server Recovery) no este habilitado en el bios de los servidores o nodos de clúster.
- Revisar la consistencia de las DBs de SQL ejecutando un DBCC CHECKDB
Como monitorear este comportamiento:
En el caso que el problema continúe presentándose hay que seguir los siguientes pasos para obtener más detalle de todos los componentes.
Modified the registry to let the system generate a crash on ctrl+scroll next time
this happens https://support.microsoft.com/kb/244139
- Revisar propiamente la configuración del page file . (Checked that there is up to 4Gb of space for the paging file. Dump is set to Kernel (server has 4GB of RAM) )
- Run PerfMon to capture poolmon and perfmon data every 10
minutes
https://support.microsoft.com/kb/177415/en-us
- Monitoreo de Red:
Referencia:
for this entry
Eventlog Type:
Eventlog Source:
Event ID:
Full Description:
The server was unable to allocate from the system nonpaged pool because the pool was empty.
There is Microsoft Knowledge Base article available. Please see https://support.microsoft.com/default.aspx?scid=kb;en-us;272568 for further details.
Parameter Description:
The server was unable to allocate from the system nonpaged pool because the pool was empty.
More Informations:
To check if there is any memory leak, do the following:
Run Performance Monitor.
From the Edit menu, choose Add To Chart.
In the Object field, select Memory.
In the Counter field:
a. Select Pool Nonpaged Bytes and choose Add.
b. Select Pool Paged Bytes and choose Add.
Choose Done and watch Performance Monitor in Chart view mode.
If the Nonpaged value rises rapidly while the TCP/IP sockets program is running then you may be experiencing this problem.
Example configurations under which this problem may occur:
Systems Management Server needs to communicate with an SQL server.
A SQL default client is configured for sockets - not Named Pipes.
TCP/IP is installed on the server, but SQL Server is not set up to use it. SQL Server is designed to use Named Pipes only.
Systems Management Server uses the default SQL client configuration for sockets.
Systems Management Server attempts to access SQL using sockets repeatedly.
CAUSE :
This problem can be caused by a TCP/IP sockets program that continuously attempts to open a socket port that does not exist.
RESOLUTION :
To work around this problem, correct your TCP/IP sockets program so that it does not attempt to open the unsupported socket port.
