Guía de arquitectura de administración de memoria

Se aplica a: SQL Server (todas las versiones admitidas) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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 memoria de SQL Server

SQL Server adquiere y libera memoria de manera dinámica según sea preciso. Normalmente, un administrador no tiene que especificar la cantidad de memoria que se debe asignar a SQL Server, aunque la opción sigue existiendo 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 muy cargado, algunas consultas grandes que requieren una gran cantidad de memoria para ejecutarse no pueden obtener la cantidad mínima de memoria solicitada y recibir un error de tiempo de espera mientras esperan 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 muy cargado bajo presión de memoria, las consultas con combinación de mezcla, ordenación y mapa de bits en el plan de consulta pueden quitar el mapa de bits cuando las consultas no obtienen la memoria mínima necesaria para el mapa de bits. Esto puede afectar al rendimiento de las consultas y, si el proceso de ordenación no puede caber en la memoria, puede aumentar el uso de tablas de trabajo en tempdb la base de datos, lo que hace tempdb crecer. Para resolver este problema, agregue memoria física o ajuste las consultas para usar un plan de consulta diferente y más rápido.

Memoria convencional (virtual)

Todas las ediciones 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). Consulte Límites de memoria para Windows para obtener más información.

Memoria de extensiones de Windows de direcciones (AWE)

Mediante el uso de extensiones de ventanas de direcciones (AWE) y el privilegio Bloquear páginas en memoria (LPIM) requeridas por AWE, puede mantener la mayoría de SQL Server memoria de proceso bloqueada en 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 AWE no pasa por el Administrador de memoria virtual en Windows, que controla la paginación de 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 lo 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 usen AWE, vea Habilitar la opción Bloquear páginas en memoria.

Si se concede LPIM, se recomienda encarecidamente establecer la memoria máxima del servidor (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, vea Configuración del servidor 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, se puede notificar el error 17890 en el registro de errores. El error es similar al ejemplo siguiente:

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 realizó 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 asignaciones 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 Single-Page, las asignaciones de varias páginas y las asignaciones de CLR se consolidan en un asignador de páginas de "Cualquier tamaño" y se incluyen en los límites de memoria controlados por las opciones de configuración memoria máxima del servidor (MB) y memoria mínima del servidor (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 detenidamente las configuraciones actuales de memoria de servidor máxima (MB) y memoria mínima del servidor (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 a las versiones de 32 y 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í, 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
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 máximo de memoria del servidor (MB). Este comportamiento puede producirse cuando el valor memoria total del servidor (KB) ya ha alcanzado la configuración memoria del servidor de destino (KB), según lo especificado por memoria máxima del servidor (MB). Si no hay memoria libre contigua suficiente para atender a 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 las solicitudes 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 total del servidor (KB) . Por lo tanto, SQL Server uso de memoria podría superar brevemente la configuración de memoria máxima del servidor (MB). En esta situación, la lectura del contador de rendimiento Memoria total del servidor (KB) superará la configuración de memoria máxima del servidor (MB) y memoria de servidor de destino (KB).

Este comportamiento se observa normalmente durante las siguientes operaciones:

  • Consultas de índice de almacén de columnas grandes
  • Modo por lotes grande en consultas de almacén de filas
  • Compilaciones de índice de almacén de columnas (re),que usan grandes volúmenes de memoria para realizar operaciones hash y ordenar
  • Operaciones de copia de seguridad que requieren búferes de memoria grandes
  • 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 apartó una parte del espacio de direcciones virtuales (VAS) del proceso para que lo use el asignador de varias páginas (MPA),CLR Allocator, asignaciones de memoria para pilas de subprocesos en el proceso de SQL Server y asignaciones directas de Windows (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 estas 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ágina "cualquier tamaño" también controla las asignaciones superiores a 8 KB, el valor de 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 No, consolidadas bajo las asignaciones de páginas de cualquier tamaño
Asignaciones de CLR
Memoria de pilas de subprocesos
Asignaciones directas de Windows

Administración de memoria dinámica

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 hay más memoria libre, SQL Server puede asignar más memoria. SQL Server agrega memoria solo cuando su carga de trabajo requiere más memoria; un servidor en reposo no aumenta el tamaño de su espacio de direcciones virtual. Si observa que el Administrador de tareas y Monitor de rendimiento muestran una disminución constante en 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.

La Memoria de servidor máxima controla la asignación de memoria de SQL Server, 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 pila

La memoria de las pilas de subprocesos 1, CLR 2, archivos de procedimientos extendidos .dll, los proveedores OLE DB a los que hacen referencia las consultas distribuidas, los objetos de automatización a los que se hace referencia en instrucciones Transact-SQL y cualquier memoria asignada por un archivo DLL que no sea de SQL Server, no se controlan mediante memoria de servidor máxima (MB).

1 Consulte el artículo sobre cómo configurar la opción de configuración máxima del servidor de subprocesos de trabajo para obtener información sobre los subprocesos de trabajo predeterminados calculados para un número determinado 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 cuándo el administrador de memoria de SQL Server puede asignar memoria y liberar memoria.

Cuando se inicia SQL Server, calcula el tamaño del espacio de direcciones virtuales para el grupo de búferes en función de 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 adquierendo memoria física hasta que alcanza su destino de asignación de memoria de servidor máxima (MB) o el sistema operativo indica que ya no hay un exceso de memoria libre; libera memoria cuando tiene más de la configuración de memoria de servidor mínima y el sistema operativo indica que hay una escasez de memoria libre.

Cuando se inician otras aplicaciones en un equipo que ejecuta una instancia de SQL Server, 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 min server memory y max server memory establecen los límites superior e inferior de la cantidad de memoria que usa el grupo de búferes y otras cachés del motor de base de datos de Motor de base de datos. El grupo de búferes no adquiere inmediatamente la cantidad de memoria especificada en memoria mínima del servidor. 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 ninguna de las memorias adquiridas hasta que alcanza la cantidad especificada en memoria mínima del servidor. 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 quita su asignación de memoria por debajo del nivel especificado en memoria mínima del servidor y nunca adquiere más memoria que el nivel especificado en memoria máxima del servidor (MB).

Nota:

SQL Server como proceso adquiere más memoria que la especificada por la opción memoria máxima del servidor (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 todavía representa normalmente la cantidad más grande de memoria que consume SQL Server.

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. Una instancia de SQL Server que no procesa muchas solicitudes nunca puede alcanzar memoria mínima del servidor.

Si se especifica el mismo valor para la memoria mínima del servidor y la memoria máxima del servidor (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 deja de liberar y adquirir memoria dinámicamente para el grupo de búferes.

Si una instancia de SQL Server se está ejecutando en un equipo donde se inician o detienen otras aplicaciones con frecuencia, la asignación y cancelación de asignación de memoria por parte de la instancia de SQL Server puede ralentizar el inicio 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 pueden necesitar controlar la cantidad de memoria asignada a SQL Server. En estos casos, puede usar las opciones memoria mínima del servidor y memoria máxima del servidor (MB) para controlar la cantidad de memoria que SQL Server puede usar. 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, consulte 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 lo mantiene el Administrador de bloqueos): 64 bytes + 32 bytes por propietario
  • Conexión de usuario: aproximadamente (3 * network_packet_size + 94 KB)

El tamaño del paquete de red es el tamaño de los paquetes de flujo TDS utilizados para comunicarse entre las aplicaciones y Motor de base de datos. 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 se habilitan varios conjuntos de resultados activos (MARS), la conexión de usuario es aproximadamente (3 + 3 * num_logical_connections) * network_packet_size + 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 mínima de memoria por servidor de consultas demasiado alta, especialmente en sistemas muy ocupados, ya que hacerlo podría dar lugar a:

  • 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, la concesión de memoria inicial no se puede superar en 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 hash que desborda es compatible con un workfile en tempdb, mientras que una operación de ordenación que desborda es compatible con una tabla de trabajo.

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

Un desbordamiento que se produce durante una operación hash se conoce como advertencia 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 la entrada de compilación no cabe en la memoria disponible, lo que da lugar a la división de entrada 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 aún no caben en la memoria, se volcarán en el 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 hacer la E/S muy eficaz. 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.

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 vuelven a escribirse 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 se inicia SQL Server, calcula el tamaño del espacio de direcciones virtuales para la caché del búfer en función de varios parámetros, como la cantidad de memoria física en el sistema, el número configurado de subprocesos de servidor máximo 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 única solicitud de lectura de página de 8 KB rellena una sola 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 sobre las páginas y extensiones, vea 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 , 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 bases 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:

    En versiones anteriores a SQL Server 2012 (11.x), es necesaria una marca de seguimiento 834 para habilitar las páginas grandes en SQL Server.

  • El administrador de búferes proporciona diagnósticos adicionales que se exponen a través de vistas de administración dinámica. Puede usar estas vistas para supervisar varios 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.

E/S de disco

El administrador de búfer solo realiza tareas de lectura y escritura en la base de datos. Las otras operaciones con archivos, como la apertura, el cierre, la extensión y la reducción, las realizan el administrador de base de datos y los componentes del administrador de archivos.

Las operaciones de E/S de disco que realiza el administrador de búfer tienen las siguientes características:

  • Todas las operaciones de E/S se realizan de forma asincrónica, lo que permite que el subproceso de llamada siga con el procesamiento mientras la operación de E/S se realiza en segundo plano.
  • Todas las E/S se emiten en los subprocesos de llamada a menos que la opción de E/S de afinidad esté en uso. La opción de máscara de afinidad de E/S enlaza la E/S del disco de SQL Server a un subconjunto específico de unidades CPU. En entornos de procesamiento de transacciones en línea (OLTP) de SQL Server de grandes prestaciones, esta extensión puede mejorar el rendimiento de los subprocesos de SQL Server que emiten E/S.
  • Las operaciones de E/S de múltiples páginas se logran con E/S por dispersión y recopilación, que permite transferir datos a áreas no contiguas de memoria, o desde ellas. Esto significa que SQL Server puede llenar o vaciar rápidamente la caché del búfer y, a la vez, evitar múltiples solicitudes de E/S física.

Solicitudes de E/S largas

El administrador de búfer informa sobre cualquier solicitud de E/S que haya quedado pendiente durante al menos 15 segundos. Esto ayuda al administrador del sistema a distinguir entre problemas de SQL Server y problemas del subsistema de E/S. El mensaje de error 833 se notifica y aparece en el registro de errores de SQL Server de la siguiente forma:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

Una E/S larga puede ser de lectura o escritura; no se indica actualmente en el mensaje. Los mensajes de E/S larga son advertencias, no errores. No indican problemas con SQL Server pero con el sistema de E/S subyacente. Los mensajes se notifican para ayudar a los administradores del sistema a encontrar la causa de los tiempos de respuesta largos de SQL Server con mayor rapidez y a distinguir problemas que estén fuera del control de SQL Server. Por lo tanto, no requieren ninguna acción, pero el administrador del sistema debe investigar por qué la solicitud de E/S tardó tanto tiempo y si el tiempo es justificable.

Causas de solicitudes de E/S largas

Un mensaje de E/S largo puede indicar que una E/S está bloqueada permanentemente y nunca se completará (conocida como E/S perdida) o simplemente que no se ha completado todavía. No es posible indicar a partir del mensaje qué escenario es el caso, aunque una E/S perdida a menudo provocará un tiempo de espera de bloqueo temporal.

Las E/S largas suelen indicar una carga de trabajo de SQL Server demasiado intensa para el subsistema de disco. Se puede indicar un subsistema de disco inadecuado cuando:

  • Aparecen múltiples mensajes de E/S largas en el registro de errores durante una carga de trabajo pesada de SQL Server .
  • Monitor de rendimiento contadores muestran latencias de disco largas, colas de disco largas o ningún tiempo de inactividad del disco.

Otra posible causa de las E/S largas es que un componente de la ruta de acceso de E/S (por ejemplo, un controlador o el firmware) posponga de forma continua el servicio para una solicitud de E/S antigua en favor de dar servicio a solicitudes nuevas que están más cerca de la posición actual del cabezal del disco. La técnica común de procesamiento de solicitudes en prioridad en función de cuáles están más cerca de la posición actual del encabezado de lectura y escritura se conoce como "búsqueda de ascensor". Esto puede ser difícil de corroborar con la herramienta de Monitor de rendimiento porque la mayoría de las E/S se están atiendo rápidamente. Las solicitudes de E/S largas pueden agravarse con cargas de trabajo que realicen un gran número de operaciones de E/S secuenciales, como copias de seguridad y restauración, recorridos de tabla, ordenaciones, creación de índices, cargas masivas y puestas a cero de archivos.

Las E/S largas aisladas que no aparecen relacionadas con ninguna de las condiciones anteriores pueden deberse a un problema de hardware o controlador. El registro de eventos del sistema puede contener un evento relacionado que ayude a diagnosticar el problema.

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 memorias caché 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 de anillo tiene información de estado en función de la condición actual de RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADYo 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 base de datos pueden usar 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 de 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 lee 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 se desarrolla correctamente en uno de los reintentos, se escribe un mensaje en el registro de errores y el comando que ha desencadenado la lectura continúa. Si los reintentos no se realizan correctamente, el comando genera el mensaje de error 824.

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 protección predeterminada para bases de datos creadas en SQL Server 2005 (9.x) y en versiones posteriores. El mecanismo de protección de páginas se especifica en el momento de creación de la base de datos y se puede modificar mediante ALTER DATABASE SET. Puede determinar la configuración de protección de página actual consultando la page_verify_option columna en la vista de catálogo sys.databases o la IsTornPageDetectionEnabled propiedad de la función DATABASEPROPERTYEX .

Nota:

Si se modifica la configuración de protección de página, la nueva configuración 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 la base de datos puede estar compuesta de páginas con distintos tipos de protección.

Protección contra página rasgada

La protección de página rasgada, introducida en SQL Server 2000 (8.x), es principalmente una forma de detectar daños en las páginas debido a errores de energía. Por ejemplo, es posible que por un problema 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 se alterna entre binario 01 y 10 con cada escritura, por lo que siempre es posible indicar cuándo solo se ha realizado una parte de los sectores en el disco: si un bit está en estado incorrecto cuando se lee la página más adelante, la página se escribió incorrectamente y se detecta una página rasgada. La detección de páginas rasgadas utiliza recursos mínimos; sin embargo, no detecta todos los errores causados por errores de hardware de disco. Para obtener información sobre cómo establecer 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 tiene en cuenta cada byte de la página; sin embargo, 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 sobre cómo establecer 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 encarecidamente usar CHECKSUM. TORN_PAGE_DETECTION puede usar menos recursos, pero proporciona un subconjunto mínimo de la CHECKSUM protección.

Descripción del acceso a memoria no uniforme

SQL Server está preparado para el acceso no uniforme a memoria (NUMA) y realiza 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: Cómo: Configurar SQL Server para que use Soft-NUMA.

Partición dinámica de objetos de memoria

Los asignadores de montón, llamados objetos de memoria en SQL Server, permiten a 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 un 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 de Motor de base de datos para muchas asignaciones diferentes y se puede particionar globalmente, 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 lo 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. Dado que los patrones de asignación de memoria varían considerablemente en función de aspectos como el uso de hardware y memoria, es imposible conseguir el patrón de particionamiento perfecto de antemano.

En la mayoría de los casos, el uso de una sola partición bastará, pero en algunos escenarios esto puede provocar contención, lo que solo se puede evitar con un objeto de memoria con particiones elevadas. No es conveniente particionar cada objeto de memoria, ya que más particiones pueden dar lugar a otras ineficiencias y aumentar la fragmentación de 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) SP2 y SQL Server 2016 (13.x), este comportamiento es dinámico y lo controla el motor.

A partir de SQL Server 2014 (12.x) SP2 y SQL Server 2016 (13.x), 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 SQL Server. La contención de CMemThread se puede detectar mediante la presencia de esperas elevadas de CMEMTHREAD en la DMV de sys.dm_os_wait_stats y observando las columnas contention_factorde DMV de sys.dm_os_memory_objects , partition_typeexclusive_allocations_count, y waiting_tasks_count.

Pasos siguientes