Compartir vía

Guía de arquitectura de administración de memoria

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Administrador de memoria virtual de Windows

El Administrador de memoria virtual (VMM) de Windows asigna las regiones confirmadas de espacio de direcciones a la memoria física disponible.

Para obtener más información sobre la cantidad de memoria física admitida por los distintos sistemas operativos, vea la documentación de Windows sobre Límites de memoria para versiones de Windows.

Los sistemas de memoria virtual permiten una mayor asignación de memoria física, de forma que la proporción de memoria virtual a memoria física puede ser superior a 1:1. Como resultado, los programas más grandes se pueden ejecutar en equipos con varias configuraciones de memoria física. No obstante, el uso de una cantidad de memoria virtual significativamente superior al promedio combinado de los espacios de trabajo de todos los procesos puede provocar un rendimiento bajo.

Arquitectura de la memoria de SQL Server

SQL Server adquiere y libera memoria de manera dinámica según sea preciso. Normalmente, no es necesario que un administrador especifique la cantidad de memoria que se debe asignar a SQL Server, aunque todavía existe esta opción y es necesaria en algunos entornos.

Uno de los principales objetivos de diseño de todo el software de base de datos es minimizar la E/S de disco porque las operaciones de lectura y escritura del disco realizan un uso muy intensivo de los recursos. SQL Server crea un grupo de búferes en la memoria para contener las páginas leídas en la base de datos. Gran parte del código de SQL Server está dedicado a minimizar el número de lecturas y escrituras físicas entre el disco y el grupo de búferes. SQL Server intenta encontrar un equilibrio entre dos objetivos:

  • Evitar que el grupo de búferes sea tan grande que todo el sistema se quede con poca memoria.
  • Minimizar la E/S física a los archivos de base de datos al maximizar el tamaño del grupo de búferes.

En un sistema con mucha carga, algunas consultas grandes que necesitan una gran cantidad de memoria para ejecutarse no pueden obtener la cantidad mínima de memoria solicitada y reciben un error de tiempo de espera agotado mientras esperan los recursos de memoria. Para solucionarlo, aumente la opción Espera de consulta. Para una consulta en paralelo, considere la posibilidad de reducir la opción Grado máximo de paralelismo.

En un sistema con mucha carga y mucha presión de la memoria, las consultas con combinaciones de mezcla, orden y mapa de bits en el plan de consulta pueden quitar el mapa de bits si no obtienen la memoria mínima necesaria para dicho mapa de bits. Esto puede afectar al rendimiento de la consulta y, si el proceso de ordenación no cabe en la memoria, puede aumentar el uso de las tablas de trabajo en la base de datos tempdb, lo que hace que tempdb crezca. Para resolver este problema, agregue memoria física u optimice las consultas para que usen otro plan de consulta más rápido.

Memoria convencional (virtual)

Todas las ediciones de SQL Server admiten memoria convencional en la plataforma de 64 bits. El proceso de SQL Server puede acceder al espacio de direcciones virtuales hasta el máximo del sistema operativo en la arquitectura x64 (SQL Server Standard Edition admite hasta 128 GB). Con la arquitectura IA64, el límite era de 7 TB (IA64 no se admite en SQL Server 2012 (11.x) y versiones posteriores). Para obtener más información, vea Límites de memoria para Windows.

Memoria de extensiones de ventanas de direcciones (AWE)

Mediante el uso de extensiones de ventanas de direcciones (AWE) y el privilegio Bloquear páginas en memoria (LPIM) requerido por AWE, puede mantener la mayoría de la memoria de proceso de SQL Server bloqueada en la RAM física en condiciones de memoria virtual bajas. Esto sucede en las asignaciones de AWE de 32 y 64 bits. El bloqueo de memoria se produce porque la memoria de AWE no pasa por el Administrador de memoria virtual en Windows, que controla la paginación de la memoria. La API de asignación de memoria de AWE requiere el privilegio Bloquear páginas en memoria (SeLockMemoryPrivilege); consulte las notas de AllocateUserPhysicalPages. Por tanto, la principal ventaja de usar la API de AWE es mantener la mayor parte de la memoria residente en RAM si hay presión de memoria en el sistema. Para obtener información sobre cómo permitir que SQL Server use AWE, vea Habilitación de la opción de bloqueo de páginas en memoria (Windows).

Si se concede LPIM, recomendamos encarecidamente establecer Memoria de servidor máxima (MB) en un valor específico, en lugar de dejar el valor predeterminado de 2 147 483 647 megabytes (MB). Para obtener más información, consulte Opciones de configuración de memoria del servidor: establecer opciones manualmente y Bloquear páginas en memoria (LPIM).

Si LPIM no está habilitado, SQL Server cambiará al uso de la memoria convencional y, en los casos de agotamiento de memoria del sistema operativo, es posible que el error [MSSQLSERVER_17890] (errors-events/mssqlserver-17890-database-engine-error.md) se notifique en el registro de errores. El error tiene un aspecto similar al siguiente ejemplo:

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

Cambios en la administración de memoria a partir de SQL Server 2012 (11.x)

En versiones anteriores de SQL Server, la asignación de memoria se realizaba mediante cinco mecanismos diferentes:

  • Asignador de página única (SPA), que incluye solo las asignaciones de memoria menores o iguales a 8 KB en el proceso SQL Server. Las opciones de configuración memoria de servidor máxima (MB) y memoria de servidor mínima (MB) determinaban los límites de la memoria física que podía consumir el SPA. El grupo de búferes era simultáneamente el mecanismo para SPA y el mayor consumidor de asignaciones de página única.
  • Asignador de varias páginas (MPA), para las asignaciones de memoria que requieren más de 8 KB.
  • Asignador de CLR, que incluye las pilas CLR de SQL y las asignaciones globales creadas durante la inicialización de CLR.
  • Asignaciones de memoria para pilas de subprocesos en el proceso SQL Server.
  • Asignaciones de Windows directas, para las solicitudes de asignación de memoria que se hacen directamente a Windows. Esto incluye las asignaciones virtuales directas y de uso de pilas de Windows realizadas por los módulos que se cargan en los procesos de SQL Server. Algunos ejemplos de estas solicitudes de asignación de memoria incluyen asignaciones de DLL de procedimiento almacenado extendido, objetos que se crean mediante procedimientos de Automation (llamadas sp_OA) y asignaciones para proveedores de servidores vinculados.

A partir de SQL Server 2012 (11.x), las asignaciones de página única, las asignaciones de varias páginas y las asignaciones de CLR están consolidadas en un Asignador de páginas de cualquier tamaño, y se incluye en los límites de memoria controlados por las opciones de configuración memoria de servidor máxima (MB) y memoria de servidor mínima (MB). Este cambio proporciona una capacidad de ajuste de tamaño más precisa para todos los requisitos de memoria que pasan por el administrador de memoria de SQL Server.

Importante

Revise cuidadosamente las configuraciones actuales de memoria de servidor máxima (MB) y memoria de servidor mínima (MB) después de actualizar a SQL Server 2012 (11.x) y versiones posteriores. Esto se debe a que, a partir de SQL Server 2012 (11.x), estas configuraciones ahora incluyen y representan más asignaciones de memoria en comparación con versiones anteriores. Estos cambios se aplican tanto a versiones de 32 bits como de 64 bits de SQL Server 2012 (11.x) y SQL Server 2014 (12.x), y versiones de 64 bits de SQL Server 2016 (13.x) y versiones posteriores.

En la tabla siguiente se indica si un tipo de asignación de memoria específico está bajo el control de las opciones de configuración memoria de servidor máxima (MB) y memoria de servidor mínima (MB):

Tipo de asignación de memoria SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) y SQL Server 2008 R2 (10.50.x) A partir de SQL Server 2012 (11.x)
Asignaciones de página única Sí Sí, consolidadas bajo las asignaciones de páginas de cualquier tamaño
Asignaciones de varias páginas No Sí, consolidadas bajo las asignaciones de páginas de cualquier tamaño
Asignaciones de CLR No Sí
Memoria de pilas de subprocesos No No
Asignaciones directas de Windows No No

A partir de SQL Server 2012 (11.x), SQL Server podría asignar más memoria que el valor especificado en el valor de memoria de servidor máxima (MB). Este comportamiento se puede producir cuando el valor de Memoria total del servidor (KB) ya ha alcanzado el valor de Memoria del servidor de destino (KB), como se especifica en la memoria de servidor máxima (MB). Si no hay memoria libre contigua suficiente para atender la demanda de solicitudes de memoria de varias páginas (más de 8 KB) debido a la fragmentación de memoria, SQL Server puede realizar compromisos por encima de lo indicado en vez de rechazar la solicitud de memoria.

En cuanto se realiza esta asignación, la tarea en segundo plano Monitor de recursos empieza a indicar a todos los consumidores de memoria que liberen la memoria asignada e intenta llevar el valor de Memoria total del servidor (KB) por debajo de la especificación de Memoria del servidor de destino(KB). Por lo tanto, el uso de memoria de SQL Server podría superar brevemente la configuración establecida por memoria de servidor máxima (MB). En esta situación, la lectura del contador de rendimiento de Memoria total del servidor (KB) superará el valor de memoria de servidor máxima (MB) y de Memoria del servidor de destino (KB).

Este comportamiento se observa normalmente durante las siguientes operaciones:

  • Consultas del índice de almacén de columnas grandes
  • Consultas del Modo por lotes en el almacén de filas grandes
  • Construcciones o reconstrucciones del índice de almacén de columnas, que usan grandes volúmenes de memoria para realizar las operaciones Hash y Sort
  • Operaciones de copia de seguridad que requieren grandes búferes de memoria
  • Operaciones de seguimiento que tienen que almacenar parámetros de entrada grandes

Cambios en memory_to_reserve a partir de SQL Server 2012 (11.x)

En versiones anteriores de SQL Server, el administrador de memoria de SQL Server reservaba una parte del espacio de direcciones virtuales (VAS) del proceso para que la usasen el asignador de varias páginas (MPA), el asignador de CLR, las asignaciones de memoria para pilas de subprocesos en el proceso de SQL Server, y las asignaciones de Windows directas (DWA). Esta parte del espacio de direcciones virtuales también se conoce como región "Mem-To-Leave" o "grupo sin búferes".

El espacio de direcciones virtuales que está reservado para las asignaciones viene determinado por la opción de configuración memory_to_reserve. El valor predeterminado que usa SQL Server es 256 MB.

Dado que el asignador de páginas de cualquier tamaño también controla asignaciones superiores a 8 KB, el valor memory_to_reserve no incluye las asignaciones de varias páginas. A excepción de este cambio, todo lo demás sigue siendo igual con respecto a esta opción de configuración.

En la tabla siguiente se indica si un tipo específico de la asignación de memoria entra en la región memory_to_reserve del espacio de direcciones virtual para el proceso SQL Server:

Tipo de asignación de memoria SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) y SQL Server 2008 R2 (10.50.x) A partir de SQL Server 2012 (11.x)
Asignaciones de página única No No, consolidadas bajo las asignaciones de páginas de cualquier tamaño
Asignaciones de varias páginas Sí No, consolidadas bajo las asignaciones de páginas de cualquier tamaño
Asignaciones de CLR Sí Sí
Memoria de pilas de subprocesos Sí Sí
Asignaciones directas de Windows Sí Sí

Administración dinámica de memoria

El comportamiento predeterminado de administración de memoria del motor de base de datos de SQL Server es adquirir toda la memoria que necesita sin provocar una escasez de memoria en el sistema. El motor de base de datos de SQL Server lo consigue mediante las API de notificación de memoria de Microsoft Windows.

Cuando SQL Server utiliza la memoria de manera dinámica, realiza una consulta periódica en el sistema para determinar la cantidad de memoria libre. El mantenimiento de esta memoria libre evita la paginación en el sistema operativo (SO). Si hay menos memoria libre, SQL Server libera memoria para el sistema operativo. Si se libera más memoria, SQL Server puede asignar más memoria. SQL Server agrega memoria solo cuando su carga de trabajo así lo requiere; un servidor inactivo no aumenta el tamaño de su espacio de direcciones virtual. Si observa que el Administrador de tareas y el Monitor de rendimiento muestran una disminución constante de la memoria disponible cuando SQL Server usa la administración de memoria dinámica, este es el comportamiento predeterminado y no debe percibirse como una pérdida de memoria.

Las opciones de configuraciónn de memoria del servidor controlan la asignación de memoria de SQL Server, compilan la memoria, todas las cachés (incluido el grupo de búferes), las concesiones de memoria de ejecución de consultas, la memoria del administrador de bloqueos y la memoria de CLR1 (básicamente, cualquier distribuidor de memoria que se encuentre en sys.dm_os_memory_clerks).

1 La memoria CLR se administra en asignaciones de max_server_memory a partir de SQL Server 2012 (11.x).

La consulta siguiente devuelve información sobre la memoria asignada actual:

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage,
    process_physical_memory_low AS sql_process_physical_memory_low,
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

Tamaños de la pila

La memoria para pilas de subprocesos1, CLR2, los archivos .dll de procedimientos extendidos, los proveedores de OLE DB a los que se hacen referencias en consultas distribuidas, los objetos de automatización a los que se hacen referencia en instrucciones Transact-SQL y cualquier otra memoria asignada por un DLL que no es de SQL Server no están controladas por la memoria de servidor máxima (MB).

1 Consulte el artículo sobre cómo Configurar el máximo de subprocesos de trabajo (opción de configuración del servidor) para obtener información sobre los subprocesos de trabajo predeterminados calculados para un determinado número de CPU con afinidad en el host actual. Los tamaños de pila de SQL Server son los siguientes:

Arquitectura de SQL Server Arquitectura del SO Tamaño de la pila
x86 (32 bits) x86 (32 bits) 512 KB
x86 (32 bits) x64 (64 bits) 768 KB
x64 (64 bits) x64 (64 bits) 2048 KB
IA64 (Itanium) IA64 (Itanium) 4096 KB

2 La memoria CLR se administra en asignaciones de max_server_memory a partir de SQL Server 2012 (11.x).

SQL Server usa la API de notificación de memoria QueryMemoryResourceNotification para determinar el momento en que el administrador de memoria de SQL Server podría asignar y liberar memoria.

Cuando SQL Server se inicia, calcula el tamaño del espacio de direcciones virtuales del grupo de búferes basándose en varios parámetros, como la cantidad de memoria física en el sistema, el número de subprocesos de servidor y varios parámetros de inicio. SQL Server reserva la cantidad calculada de su espacio de direcciones virtuales del proceso para el grupo de búferes, pero solo adquiere (confirma) la cantidad necesaria de memoria física para la carga actual.

A continuación, la instancia sigue adquiriendo la memoria que necesita para la carga de trabajo. A medida que se conectan más usuarios y se ejecutan consultas, SQL Server adquiere más memoria física según la demanda. Una instancia de SQL Server sigue adquiriendo memoria física hasta que alcanza su asignación de memoria de servidor máxima (MB) o hasta que el sistema operativo indica que ya no existe más memoria libre; libera memoria cuando hay más que la indicada por el valor de memoria de servidor mínima y el sistema operativo indica que hay escasez de memoria libre.

Cuando se inician otras aplicaciones en un equipo que ejecuta una instancia de SQL Server, estos consumen memoria y la cantidad de memoria física disponible cae por debajo del destino de SQL Server. La instancia de SQL Server ajusta su consumo de memoria. Si se detiene otra aplicación y aumenta la cantidad de memoria disponible, la instancia de SQL Server aumenta el tamaño de su asignación de memoria. SQL Server puede liberar y adquirir varios megabytes de memoria cada segundo, lo que le permite ajustarse rápidamente a los cambios de asignación de memoria.

Efectos de las opciones min y max server memory

Las opciones de configuración memoria de servidor mínima y memoria de servidor máxima establecen los límites superior e inferior de la cantidad de memoria que usa el grupo de búferes y otras memorias caché del motor de base de datos del Motor de base de datos. El grupo de búferes no adquiere inmediatamente la cantidad de memoria especificada en Memoria de servidor mínima. El grupo de búferes comienza con la memoria precisa para el inicio. Según aumenta la carga de trabajo del motor de base de datos de SQL Server, se sigue adquiriendo la memoria necesaria para permitir la carga de trabajo. El grupo de búferes no libera nada de la memoria adquirida hasta que alcanza la cantidad especificada en Memoria de servidor mínima. Una vez alcanzado el valor de Memoria de servidor mínima, el grupo de búferes utiliza el algoritmo estándar para adquirir y liberar memoria según sea preciso. La única diferencia es que el grupo de búferes nunca deja que su asignación de memoria baje del nivel especificado en Memoria de servidor mínima y adquiera más memoria del nivel especificado en Memoria de servidor máxima (MB).

Nota:

SQL Server adquiere, como un proceso, más memoria de la especificada en la opción Memoria de servidor máxima (MB). Los componentes tanto internos como externos pueden asignar memoria fuera del grupo de búferes, lo cual consume memoria adicional, pero la memoria asignada en el grupo de búferes normalmente todavía representa la cantidad más grande de memoria que SQL Server consume.

La cantidad de memoria que adquiere el motor de base de datos de SQL Server es totalmente dependiente de la carga de trabajo colocada en la instancia. Es posible que una instancia de SQL Server que no procesa muchas solicitudes nunca pueda alcanzar el valor especificado por la memoria de servidor mínima.

Si se especifica el mismo valor para Memoria de servidor mínima y Memoria de servidor máxima (MB), una vez que la memoria asignada al motor de base de datos de SQL Server alcanza ese valor, el motor de base de datos de SQL Server detiene dinámicamente la adquisición y liberación de la memoria para el grupo de búferes.

Si una instancia de SQL Server se ejecuta en un equipo donde se inician o detienen otras aplicaciones con frecuencia, la asignación y la cancelación de asignación de memoria por parte de la instancia de SQL Server puede ralentizar los inicios de otras aplicaciones. Además, si SQL Server es una de las diversas aplicaciones de servidor que se ejecutan en un único equipo, los administradores del sistema deben controlar la cantidad de memoria asignada a SQL Server. En estos casos, puede utilizar las opciones Memoria de servidor mínima y Memoria de servidor máxima (MB) para controlar cuánta memoria puede utilizar SQL Server. Las opciones memoria de servidor mínima y memoria de servidor máxima se expresan en megabytes. Para más información, incluidas recomendaciones sobre cómo establecer estas configuraciones de memoria, vea Opciones de configuración de memoria del servidor.

Memoria que usan las especificaciones de objetos de SQL Server

La siguiente lista muestra la cantidad de memoria aproximada que usan diferentes objetos en SQL Server. Las cantidades mostradas son estimaciones y pueden variar según el entorno y cómo se crean los objetos:

  • Bloqueo (tal y como se mantiene el Administrador de bloqueos): 64 bytes + 32 bytes por propietario
  • Conexión de usuario: aproximadamente (3 * tamaño_de_paquete_de_red + 94 KB)

El tamaño del paquete de red es el tamaño de los paquetes del flujo de datos tabulares (TDS) que se utilizan para la comunicación entre las aplicaciones y el motor de base de datos de . El tamaño del paquete predeterminado es 4 KB y se controla mediante la opción de configuración Tamaño de paquete de red.

Cuando los conjuntos de resultados activos múltiples (MARS) están habilitados, la conexión de usuario es aproximadamente (3 + 3 * num_logical_connections) * tamaño_de_paquete_de_red + 94 KB.

Efectos de memoria mínima por consulta

La opción min memory per query establece la cantidad mínima de memoria (en kilobytes) que se va a asignar para la ejecución de una consulta. Esto también se conoce como concesión de memoria mínima. Todas las consultas deben esperar hasta que se pueda proteger la memoria mínima solicitada, antes de que se pueda iniciar la ejecución, o bien hasta que se supera el valor especificado en la opción de configuración del servidor Espera de consulta. El tipo de espera que se acumula en este escenario es RESOURCE_SEMAPHORE.

Importante

No establezca la opción de configuración del servidor Memoria mínima por consulta en un valor demasiado alto, especialmente en sistemas muy ocupados, ya que si lo hace, podría provocar:

  • El aumento de la competición por los recursos de memoria.
  • La reducción de la simultaneidad al aumentar la cantidad de memoria para cada consulta única, incluso si la memoria necesaria en tiempo de ejecución es menor que esta configuración.

Para obtener recomendaciones sobre el uso de esta configuración, vea Configurar la opción de configuración del servidor Memoria mínima por consulta.

Consideraciones de concesión de memoria

Para la ejecución del modo de fila, no se puede superar la concesión de memoria inicial bajo ninguna condición. Si se necesita más memoria que la concesión inicial para ejecutar operaciones de hash u orden, estas se desbordarán al disco. Una operación de hash que se desborda es compatible con un archivo de trabajo en tempdb, mientras que una operación de orden que se desborda es compatible con una tabla de trabajo.

Un desbordamiento que se produce durante una operación de orden se conoce como una clase de eventos de advertencias de orden. Las advertencias antes de ordenar indican que las operaciones de orden no caben en la memoria. Esto no incluye las operaciones de orden que implican la creación de índices, solo las operaciones de orden dentro de una consulta (como las de una cláusula ORDER BY en una instrucción SELECT).

Un desbordamiento que se produce durante una operación de hash se conoce como una clase de eventos de advertencia de hash. Tienen lugar cuándo se ha producido una recursividad hash o un cese de hash (salida hash) durante una operación de hash.

  • La recursividad hash se produce cuando no hay suficiente memoria para la entrada generada, lo que causa que ésta se divida en varias particiones que se procesan por separado. Si alguna de estas particiones sigue sin caber en la memoria disponible, se divide en subparticiones, que también se procesan por separado. Este proceso de división continúa hasta que cada partición quepa en la memoria disponible o hasta que se alcance el nivel máximo de recursividad.
  • La salida hash se produce cuando una operación de hash alcanza el nivel máximo de repetición y vuelve a un plan alternativo para procesar el resto de datos con particiones. Estos eventos pueden producir un rendimiento reducido en el servidor.

Para la ejecución del modo por lotes, la concesión de memoria inicial puede aumentar de forma dinámica hasta un umbral interno concreto de forma predeterminada. Este mecanismo de concesión de memoria dinámico está diseñado para permitir la ejecución residente en memoria de las operaciones hash y sort que se ejecutan en el modo de procesamiento por lotes. Si estas operaciones siguen sin caber en la memoria, se desbordarán al disco.

Para obtener más información sobre los modos de ejecución, vea Guía de arquitectura de procesamiento de consultas.

Administración de búfer

El propósito principal de una base de datos de SQL Server es almacenar y recuperar datos, por lo que una E/S de disco intensiva es una de las características principales del Motor de base de datos. Debido a que las operaciones de E/S de disco pueden consumir muchos recursos y tardar bastante tiempo en completarse, SQL Server se centra en incrementar la eficacia de E/S. La administración de búfer es un componente clave para lograr esta eficacia. El componente de administración de búfer consta de dos mecanismos: el administrador de búfer para obtener acceso a las páginas de bases de datos y actualizarlas y la memoria caché del búfer (también conocida como grupo de búferes) para reducir la E/S de archivos de base de datos.

Para obtener una explicación detallada de la E/S de disco en SQL Server, vea Aspectos básicos de E/S de SQL Server.

Cómo funciona la administración de búfer

Un búfer es un página de 8 KB en memoria (el mismo tamaño que una página de índice o de datos). Por tanto, la memoria caché del búfer está dividida en páginas de 8 kB. El administrador de búfer administra las funciones para la lectura de páginas de índice o de datos de los archivos de disco de base de datos en la caché del búfer y para la escritura de páginas modificadas nuevamente en el disco. Una página permanece en la memoria caché del búfer hasta que el administrador de búfer necesita el área del búfer para leer en ella más datos. Los datos solo se vuelven a escribir en el disco si se han modificado. Los datos de la memoria caché del búfer se pueden modificar varias veces antes de que se vuelvan a escribir en el disco. Para más información, consulte Leer páginas y Escribir páginas.

Cuando SQL Server se inicia, calcula el tamaño del espacio de direcciones virtuales de la caché de búferes basándose en varios parámetros, como la cantidad de memoria física en el sistema, el número configurado de subprocesos máximos de servidor y varios parámetros de inicio. SQL Server reserva la cantidad calculada de su espacio de direcciones virtuales del proceso (llamado destino de memoria) para la caché de búferes, pero solo adquiere (confirma) la cantidad necesaria de memoria física para la carga actual. Puede realizar una consulta en las columnas committed_target_kb y committed_kb de la vista de catálogo sys.dm_os_sys_info para devolver el número de páginas reservadas como memoria objetivo y el número de páginas actualmente confirmadas en la caché del búfer, respectivamente.

El intervalo entre el inicio de SQL Server y el momento en que la caché del búfer obtiene su memoria objetivo se llama arranque. Durante este período, las solicitudes de lectura llenan los búferes según sea necesario. Por ejemplo, una solicitud de lectura de una página de 8 KB llena una única página de búfer. Esto significa que el arranque depende del número y el tipo de solicitudes del cliente. El arranque se agiliza mediante la transformación de solicitudes de lectura de una página en solicitudes de ocho páginas alineadas (creando una extensión). Esto permite que el arranque finalice mucho más rápido, especialmente en equipos con mucha memoria. Para obtener más información acerca de las páginas y las extensiones, consulte Guía de arquitectura de páginas y extensiones.

Debido a que el administrador de búfer utiliza la mayor parte de la memoria en el proceso de SQL Server, este coopera con el administrador de memoria para permitir que otros componentes utilicen sus búferes. El administrador de búfer interactúa principalmente con los siguientes componentes:

  • Administrador de recursos, para controlar la utilización de memoria general y, en plataformas de 32 bits, para controlar el uso del espacio de direcciones.
  • Administrador de base de datos y SQL Server Operating System (SQLOS), para operaciones de E/S de archivos de bajo nivel.
  • Administrador de registros, para registros de escritura previa.

Características admitidas

El administrador de búfer admite las características siguientes:

  • El administrador de búfer está preparado para el acceso no uniforme a memoria (NUMA, Non-Uniform Memory Access). Las páginas de la caché del búfer se distribuyen por los nodos NUMA de hardware, que permiten que un subproceso tenga acceso a una página de búfer que esté asignada en el nodo NUMA local y no desde una memoria externa.

  • El administrador de búfer admite la función de Agregar memoria sin interrupción, que permite a los usuarios agregar memoria física sin reiniciar el servidor.

  • El administrador de búfer admite páginas grandes en plataformas de 64 bits. El tamaño de página es específico de la versión de Windows.

    Nota:

    Antes de SQL Server 2012 (11.x), habilitar páginas grandes en SQL Server requiere la marca de seguimiento 834.

  • El administrador de búfer proporciona diagnósticos adicionales que se muestran mediante vistas de administración dinámica. Puede utilizar estas vistas para supervisar diversos recursos del sistema operativo específicos de SQL Server. Por ejemplo, puede usar la vista sys.dm_os_buffer_descriptors para supervisar las páginas de la caché del búfer.

Detección de la presión de memoria

La presión de memoria es una condición resultante de la escasez de memoria y puede dar lugar a:

  • Operaciones de E/S adicionales (por ejemplo, un subproceso en segundo plano de escritura diferida muy activo).
  • Mayor proporción de recompilación.
  • Consultas en ejecución más prolongadas (si existen esperas de concesión de memoria).
  • Ciclos de CPU adicionales.

Esta situación se puede desencadenar debido a causas externas o internas. Las causas externas incluyen:

  • La memoria física (RAM) disponible es baja. Esto hace que el sistema recorte los espacios de trabajo de los procesos actualmente en ejecución, lo que puede provocar una ralentización general. SQL Server puede reducir el destino de confirmación del grupo de búferes e iniciar el recorte de las cachés internas con más frecuencia.
  • La memoria del sistema disponible general (que incluye el archivo de paginación del sistema) es baja. Esto puede provocar un error del sistema en las asignaciones de memoria, ya que no puede paginar la memoria asignada actualmente.

Las causas internas incluyen:

  • Responder a la presión de memoria externa, cuando el Motor de base de datos de SQL Server establece límites de uso de memoria más bajos.
  • La configuración de memoria se redujo manualmente reduciendo la configuración memoria de servidor máxima.
  • Cambios en la distribución de memoria de los componentes internos entre varias cachés.

El Motor de base de datos de SQL Server implementa un marco de trabajo dedicado para detectar y controlar la presión de memoria, como parte de su administración de memoria dinámica. Este marco de trabajo incluye la tarea en segundo plano llamada Monitor de recursos. La tarea Monitor de recursos supervisa el estado de los indicadores de memoria externa e interna. Cuando uno de estos indicadores cambia de estado, calcula la notificación correspondiente y la difunde. Estas notificaciones son mensajes internos desde cada uno de los componentes del motor y se almacenan en búferes en anillo.

Dos búferes en anillo contienen información relevante para la administración dinámica de memoria:

  • El búfer en anillo Monitor de recursos, que realiza el seguimiento de la actividad del Monitor de recursos, como si la presión de memoria se ha señalado o no. Este búfer en anillo tiene información de estado en función de la condición actual de RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY o RESOURCE_MEMVIRTUAL_LOW.
  • El búfer en anillo Agente de memoria, que contiene los registros de las notificaciones de memoria para cada grupo de recursos de Resource Governor. Cuando se detecta la presión de memoria interna, se activa la notificación de memoria insuficiente para los componentes que asignan memoria, para desencadenar acciones diseñadas para equilibrar la memoria entre las cachés.

Los agentes de memoria supervisan la demanda de consumo de memoria de cada componente y, después, en función de la información recopilada, calculan el valor óptimo de memoria para cada uno de estos componentes. Hay un conjunto de agentes para cada grupo de recursos de Resource Governor. Después, esta información se difunde a cada uno de los componentes, que aumentan o reducen su uso según sea necesario.

Para obtener más información sobre los agentes de memoria, vea sys.dm_os_memory_brokers.

Detección de errores

Las páginas de bases de datos pueden utilizar uno de los dos mecanismos opcionales que ayudan a garantizar la integridad de la página. desde el momento en que se escribe en el disco hasta que se vuelve a leer: protección contra página rasgada y protección de suma de comprobación. Estos mecanismos permiten emplear un método independiente para comprobar la corrección, no solo del almacenamiento de datos, sino también de los componentes de hardware, como controladores, cables e incluso el sistema operativo. La protección se agrega a la página justo antes de escribirla en el disco y se comprueba después de que se lea desde el disco.

SQL Server vuelve a intentar cualquier lectura que genere un error con una suma de comprobación, una página rasgada u otros errores de E/S, en cuatro ocasiones. Si la lectura es correcta en cualquiera de los reintentos, se escribe un mensaje en el registro de errores y el comando que ha desencadenado la lectura continúa. Si se produce un error en los intentos de reintento, se produce el error MSSQLSERVER_824 en el comando.

El tipo de protección de página que se utilice es un atributo de la base de datos que contiene la página. La protección de suma de comprobación es la predeterminada para bases de datos creadas en SQL Server 2005 (9.x) y versiones posteriores. El mecanismo de protección de páginas se especifica al crear la base de datos y se puede modificar mediante ALTER DATABASE SET. La configuración de protección de página actual se puede determinar consultando la columna page_verify_option de la vista de catálogo sys.databases o la propiedad IsTornPageDetectionEnabled de la función DATABASEPROPERTYEX.

Nota:

Si se modifica el valor de protección de página, el nuevo valor no afecta a toda la base de datos de forma inmediata. En cambio, las páginas adoptan el nivel de protección actual de la base de datos cuando se vuelven a escribir. Esto significa que es posible que la base de datos esté formada por páginas con distintos tipos de protección.

Protección contra página rasgada

La protección contra página rasgada, que se introdujo en SQL Server 2000 (8.x), es básicamente una forma de detectar errores en las páginas a causa de problemas con el suministro eléctrico. Por ejemplo, es posible que por un problema inesperado con el suministro eléctrico solo se escriba una parte de la página en el disco. Cuando se usa la protección de páginas rasgadas, se almacena un patrón de firma de 2 bits específico por cada sector de 512 bytes de la página de base de datos de 8 kilobytes (KB) en el encabezado de página de la base de datos cuando la página se escribe en disco.

Si la página se lee desde el disco, los bits rasgados almacenados en el encabezado de página se comparan con la información del sector de la página real. El patrón de firma alterna entre los binarios 01 y 10 en cada escritura, por lo que siempre es posible detectar si solo una parte de los sectores han llegado al disco: si hay un bit con el estado incorrecto cuando la página se lee posteriormente, la página se ha escrito de forma incorrecta y se ha detectado una página rasgada. La detección de página rasgada utiliza un mínimo de recursos; sin embargo, no detecta todos los errores causados por errores del hardware de disco. Para obtener información acerca de cómo configurar la detección de páginas rasgadas, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Protección de suma de comprobación

La protección de suma de comprobación, característica implementada en SQL Server 2005 (9.x), proporciona una comprobación de integridad de datos más sólida. Se calcula una suma de comprobación para los datos de cada página que se escribe y se almacena en el encabezado de la página. Cada vez que se lee desde disco una página con una suma de comprobación almacenada, el motor de base de datos vuelve a calcular la suma de comprobación para los datos de la página y muestra el error 824 cuando la nueva suma de comprobación no coincide con la suma almacenada. La protección de suma de comprobación puede detectar más errores que la protección contra página rasgada porque se ve afectada por cada byte de la página; pero, consume una cantidad moderada de recursos.

Cuando la suma de comprobación está habilitada, pueden detectarse los errores debidos a cualquier problema con el suministro eléctrico o a hardware o firmware defectuosos cada vez que el administrador de búfer lea una página del disco. Para obtener información acerca de cómo configurar la suma de comprobación, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Importante

Cuando se actualiza una base de datos de usuario o sistema a SQL Server 2005 (9.x) o posterior, se conserva el valor de PAGE_VERIFY (NONE o TORN_PAGE_DETECTION). Se recomienda utilizar CHECKSUM. Es posible que TORN_PAGE_DETECTION utilice menos recursos, pero proporciona un subconjunto mínimo de la protección de CHECKSUM.

Descripción del acceso no uniforme a memoria

SQL Server está preparado para el acceso no uniforme a memoria (NUMA) y tiene un buen rendimiento en hardware NUMA sin necesidad de establecer ninguna configuración especial. A medida que aumentan la velocidad del reloj y el número de procesadores, resulta cada vez más difícil reducir la latencia de la memoria necesaria para utilizar esta potencia de procesamiento adicional. Para evitarlo, los proveedores de hardware proporcionan cachés L3 grandes, pero esto es solo una solución limitada. La arquitectura NUMA proporciona una solución escalable para este problema.

SQL Server se ha diseñado para aprovechar los equipos basados en NUMA sin necesidad de realizar cambios en las aplicaciones. Para más información, vea Soft-NUMA (SQL Server).

Partición dinámica de objetos de memoria

Los asignadores de montón, denominados objetos de memoria en SQL Server, permiten al motor de base de datos asignar memoria del montón. Se puede realizar un seguimiento de ellos mediante la DMV sys.dm_os_memory_objects.

CMemThread es un tipo de objeto de memoria seguro para subprocesos que permite asignaciones de memoria simultáneas desde varios subprocesos. Para realizar el seguimiento correcto, los objetos CMemThread se basan en construcciones de sincronización (una exclusión mutua) para garantizar que un solo subproceso actualiza datos críticos a la vez.

Nota:

El tipo de objeto CMemThread se utiliza en la base de código del Motor de base de datos para muchas asignaciones diferentes y se puede particionar de manera global, por nodo o por CPU.

Sin embargo, el uso de exclusiones mutuas puede llevar a contención si muchos subprocesos asignan desde el mismo objeto de memoria de un modo muy simultáneo. Por tanto, SQL Server tiene el concepto de objetos de memoria con particiones (PMO) y cada partición se representa mediante un único objeto CMemThread. La creación de particiones de un objeto de memoria se define estáticamente y no se puede cambiar después de la creación. Como los patrones de asignación de memoria varían considerablemente en función de aspectos como el uso de hardware y la memoria, es imposible conseguir el patrón de creación de particiones perfecto de antemano.

En la mayoría de los casos, el uso de una sola partición será suficiente, pero en algunos escenarios esto puede provocar contención, lo que solo se puede evitar con un objeto de memoria muy particionado. No es aconsejable crear particiones de cada objeto de memoria, ya que más particiones pueden afectar a la eficiencia de otras formas y aumentar la fragmentación de la memoria.

Nota:

Antes de SQL Server 2016 (13.x), la marca de seguimiento 8048 se podría usar para forzar que un PMO basada en nodo se convierta en un PMO basada en CPU. A partir de SQL Server 2014 (12.x) SP 2 y SQL Server 2016 (13.x), este comportamiento es dinámico y se controla mediante el motor.

A partir de SQL Server 2014 (12.x) SP2 y SQL Server 2016 (13.x), el Motor de base de datos puede detectar dinámicamente la contención en un objeto CMemThread específico y promover el objeto a una implementación por nodo o por CPU. Una vez promocionado, el PMO sigue promocionado hasta que se reinicie el proceso de SQL Server. La contención de CMemThreadse puede detectar por la presencia de esperas de CMEMTHREAD altas en la DMV sys.dm_os_wait_stats y mediante la observación de las columnas contention_factor, partition_type, exclusive_allocations_count y waiting_tasks_count de la DMV sys.dm_os_memory_objects.

Contenido relacionado