MS SQL Server: aumenta la memoria asignada

Question

Mi problema es que SQL Server tarda mucho en aumentar su uso de memoria para instancias con TB de RAM, mientras recibimos esperas intermitentes de MEMORY_ALLOCATION_EXT que ralentizan nuestro procesamiento hasta que SQL Server alcanza su memoria máxima.

Tenemos instancias en clúster de conmutación por error (FCI) de SQL Server 2019 Enterprise Edition con terabytes de memoria en los nodos. En los casos de uso habituales, solo permitimos 1 instancia de SQL Server por nodo, por lo que configuramos la memoria máxima del servidor cerca de ~ 85% de la memoria en el nodo, pero también configuramos la memoria mínima del servidor relativamente baja en caso de que SQL Server falle. a otro nodo y debe conectarse con una huella de memoria reducida para avanzar.

• Soy bastante consciente de que si configuro la memoria mínima más alta, SQL Server consumirá la memoria de una vez. Resulta que SQL Server no asigna la memoria mínima al inicio.
• Soy consciente de que SQL Server consumirá dinámicamente más memoria del sistema operativo a medida que la necesite y que eventualmente alcanzará la memoria máxima del servidor.
• Soy consciente de que ejecutar una consulta grande o DBCC checkdb que extrae una gran cantidad de datos obligará a SQL Server a consumir más memoria del sistema operativo.

¿Hay otras formas de obligar a SQL Server a aumentar su uso de memoria rápidamente?

Answer 1

Soy bastante consciente de que si configuro la memoria mínima más alta, SQL Server consumirá la memoria de una vez.

No, esto no es correcto, la memoria mínima del servidor no tiene nada que ver con la rapidez con la que SQL Server la consumirá si se configura. La memoria mínima del servidor significa la cantidad mínima de memoria, si se consume, después de la cual SQL Server no liberará memoria por debajo de este valor en condiciones normales si se le pide que lo haga. Una vez que se alcanza el valor mínimo del servidor, SQL Server no recortará sus cachés ni liberará memoria por debajo de este valor.

Soy consciente de que SQL Server consumirá dinámicamente más memoria del sistema operativo a medida que la necesite y que eventualmente alcanzará la memoria máxima del servidor.

Sí, correcto, solo agregar SQl Server en ciertas condiciones también puede consumir memoria mayor que el valor especificado en la memoria máxima del servidor. Debería leer la Guía de administración de memoria

¿Hay otras formas de obligar a SQL Server a aumentar su uso de memoria rápidamente?

Hay otra forma que obliga a SQL Server a reservar más memoria durante el inicio y se denomina " páginas grandes ". Estas son características de la edición empresarial y permiten que SQL Server reserve rápidamente algo de memoria libre durante el inicio, la cuenta de servicio de SQL Server también debe tener páginas bloqueadas en el privilegio de memoria porque la asignación de páginas grandes se realiza mediante la función VirtualAlloc () del sistema operativo Windows y el sistema operativo debería tener más de 8 GB de RAM, si estas condiciones se cumplen automáticamente, se habilita la asignación de páginas grandes. Una vez habilitado, aparecerá como se muestra a continuación en el registro de errores

2009-06-04 12:21:08.16 Server      Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server      Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server      Large Page Allocated: 32MB

El tamaño de página normal de la memoria es de 4 KB en el sistema X64, pero cuando se habilita la página grande, el tamaño será de 2 MB. También tenga en cuenta que LargePageSupport está habilitado y usado por el motor incluso si no habilita el indicador de seguimiento 834. Pero no se usa mucha memoria para esto y la memoria del grupo de búfer no se usa a menos que el indicador de rastreo 834 esté habilitado. Ver el blog compartido.

Answer 2

SQL Server no utilizará la memoria para el grupo de búferes (su mayor uso) hasta que necesite cargar páginas desde el almacenamiento porque están referenciadas. A menos que esté haciendo algo extraño para ralentizar la asignación de memoria ^[†] , es poco probable que su retraso sea así, pero al leer los datos que se asignan para retener. Sospecho que al mismo tiempo que esas esperas de asignación de memoria, tiene esperas relacionadas con IO coincidentes.

Para obligar al servidor SQL a cargar cosas en la memoria y, por lo tanto, asignar memoria si está disponible y es necesaria, acceda a las cosas para que necesiten cargarse. Después de iniciar su instancia de SQL, ejecute una carga de trabajo de solo lectura para que el conjunto de trabajo común normal de su aplicación se cargue en el grupo de búfer.

No lo haga solo, SELECT * FROM HugeTableya que es probable que esto cargue un mal equilibrio de datos (no el conjunto de trabajo principal de las aplicaciones), por lo que el conjunto de trabajo principal aún deberá cargarse desde el disco / red en el próximo acceso de todos modos para que tenga el mismo IO retrasos. Además, si HugeTablees realmente enorme, SQL Server puede ver que no todo encajará y no se molestará en mantener una cantidad significativa en la memoria de todos modos [‡].

^{[†] Ejecutando en una VM en un host sobrecargado, ¿lo que parece una RAM real son realmente páginas en disco?}

^{[‡] En circunstancias normales, no desea que un escaneo inesperado de tabla / índice en un objeto grande elimine todo lo demás de la memoria. Un límite de cuánto se mantiene de un objeto para una consulta es una optimización para intentar evitar que esto suceda).}