guía de arquitectura de administración de memoriaMemory Management Architecture Guide

SE APLICA A: síSQL Server síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Administrador de memoria virtual de WindowsWindows Virtual Memory Manager

El Administrador de memoria virtual (VMM) de Windows asigna las regiones confirmadas de espacio de direcciones a la memoria física disponible.The committed regions of address space are mapped to the available physical memory by the Windows Virtual Memory Manager (VMM).

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.For more information on the amount of physical memory supported by different operating systems, see the Windows documentation on Memory Limits for Windows Releases.

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.Virtual memory systems allow the over-commitment of physical memory, so that the ratio of virtual-to-physical memory can exceed 1:1. Como resultado, los programas más grandes se pueden ejecutar en equipos con una diversidad de configuraciones de memoria física.As a result, larger programs can run on computers with a variety of physical memory configurations. 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.However, using significantly more virtual memory than the combined average working sets of all the processes can cause poor performance.

Arquitectura de la memoria de SQL ServerSQL Server Memory Architecture

SQL ServerSQL Server adquiere y libera memoria de manera dinámica según sea preciso.dynamically acquires and frees memory as required. Normalmente, no es necesario que un administrador especifique la cantidad de memoria que se debe asignar a SQL ServerSQL Server, aunque todavía existe esta opción y es necesaria en algunos entornos.Typically, an administrator does not have to specify how much memory should be allocated to SQL ServerSQL Server, although the option still exists and is required in some environments.

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.One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. SQL ServerSQL Server crea un grupo de búferes en la memoria para contener las páginas leídas en la base de datos.builds a buffer pool in memory to hold pages read from the database. Gran parte del código de SQL ServerSQL Server está dedicado a minimizar el número de lecturas y escrituras físicas entre el disco y el grupo de búferes.Much of the code in SQL ServerSQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. SQL ServerSQL Server intenta encontrar un equilibrio entre dos objetivos:tries to reach a balance between two goals:

  • Evitar que el grupo de búferes sea tan grande que todo el sistema se quede con poca memoria.Keep the buffer pool from becoming so big that the entire system is low on memory.
  • Minimizar la E/S física a los archivos de base de datos al maximizar el tamaño del grupo de búferes.Minimize physical I/O to the database files by maximizing the size of the buffer pool.

Nota

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.In a heavily loaded system, some large queries that require a large amount of memory to run cannot get the minimum amount of requested memory and receive a time-out error while waiting for memory resources. Para solucionarlo, aumente la opción Espera de consulta.To resolve this, increase the query wait Option. Para una consulta en paralelo, considere la posibilidad de reducir la opción Grado máximo de paralelismo.For a parallel query, consider reducing the max degree of parallelism Option.

Nota

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.In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. 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.This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. Para resolver este problema, agregue memoria física u optimice las consultas para que usen otro plan de consulta más rápido.To resolve this problem add physical memory or tune the queries to use a different and faster query plan.

Proporcionar la cantidad máxima de memoria a SQL ServerSQL ServerProviding the maximum amount of memory to SQL ServerSQL Server

Mediante AWE y el privilegio Bloquear páginas en memoria, puede proporcionar las siguientes cantidades de memoria al Motor de base de datos de SQL ServerSQL Server .By using AWE and the Locked Pages in Memory privilege, you can provide the following amounts of memory to the SQL ServerSQL Server Database Engine.

Nota

En la siguiente tabla se incluye una columna para las versiones de 32 bits que ya no están disponibles.The following table includes a column for 32-bit versions, which are no longer available.

32 bits 132-bit 1 64 bits64-bit
Memoria convencionalConventional memory Todas las ediciones de SQL ServerSQL Server .All SQL ServerSQL Server editions. Hasta el límite de espacio de direcciones virtuales del proceso:Up to process virtual address space limit:
- 2 GB- 2 GB
- 3 GB con el parámetro de arranque /3gb 2- 3 GB with /3gb boot parameter 2
- 4 GB en WOW64 3- 4 GB on WOW64 3
Todas las ediciones de SQL ServerSQL Server .All SQL ServerSQL Server editions. Hasta el límite de espacio de direcciones virtuales del proceso:Up to process virtual address space limit:
- 7 TB con la arquitectura IA64 (IA64 no se admite en SQL Server 2012 (11.x)SQL Server 2012 (11.x) y versiones superiores)- 7 TB with IA64 architecture (IA64 not supported in SQL Server 2012 (11.x)SQL Server 2012 (11.x) and above)
- Sistema operativo máximo con arquitectura x64 4- Operating system maximum with x64 architecture 4
Mecanismo AWE (permite a SQL ServerSQL Server superar el límite del espacio de direcciones virtuales del proceso en plataformas de 32 bits).AWE mechanism (Allows SQL ServerSQL Server to go beyond the process virtual address space limit on 32-bit platform.) SQL ServerSQL Server ediciones Standard, Enterprise y Developer: el grupo de búferes es capaz de acceder hasta 64 GB de memoria.Standard, Enterprise, and Developer editions: Buffer pool is capable of accessing up to 64 GB of memory. No aplicable 5Not applicable 5
Privilegio del sistema operativo (OS) Bloquear páginas en la memoria (permite bloquear memoria física e impedir la paginación en el sistema operativo de la memoria bloqueada). 6Lock pages in memory operating system (OS) privilege (allows locking physical memory, preventing OS paging of the locked memory.) 6 SQL ServerSQL Server ediciones Standard, Enterprise y Developer: necesario para que el proceso de SQL ServerSQL Server use el mecanismo AWE.Standard, Enterprise, and Developer editions: Required for SQL ServerSQL Server process to use AWE mechanism. La memoria asignada a través del mecanismo AWE no se puede paginar.Memory allocated through AWE mechanism cannot be paged out.
Si se concede este privilegio sin habilitar AWE, no tiene efecto en el servidor.Granting this privilege without enabling AWE has no effect on the server.
Solo se debe usar cuando sea necesario, principalmente si hay algún indicio de que el proceso sqlservr se está transfiriendo al almacenamiento auxiliar. En este caso, se informará sobre el error 17890 en el registro de errores, 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: ##%.Only used when necessary, namely if there are signs that sqlservr process is being paged out. In this case, error 17890 will be reported in the Errorlog, resembling the following example: 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: ##%.

1 Las versiones de 32 bits no están disponibles a partir de SQL Server 2014 (12.x)SQL Server 2014 (12.x).1 32-bit versions are not available starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x).
2 /3gb es un parámetro de arranque del sistema operativo.2 /3gb is an operating system boot parameter. Para obtener más información, visite MSDN Library.For more information, visit the MSDN Library.
3 WOW64 (Windows on Windows 64) es un modo en el que SQL ServerSQL Server de 32 bits se ejecuta en un sistema operativo de 64 bits.3 WOW64 (Windows on Windows 64) is a mode in which 32-bit SQL ServerSQL Server runs on a 64-bit operating system.
4 SQL ServerSQL Server Standard Edition admite hasta 128 GB.4 SQL ServerSQL Server Standard Edition supports up to 128 GB. SQL ServerSQL Server Enterprise Edition admite el máximo del sistema operativo máximo.Enterprise Edition supports the operating system maximum.
5 Tenga en cuenta que la opción sp_configure awe enabled estaba presente en SQL ServerSQL Serverde 64 bits, pero se omite.5 Note that the sp_configure awe enabled option was present on 64-bit SQL ServerSQL Server, but it is ignored.
6 Si se concede el privilegio Bloquear páginas en la memoria (LPIM) (con compatibilidad de 32 bits para AWE o directamente en 64 bits), se recomienda establecer también la opción Memoria de servidor máxima.6 If lock pages in memory privilege (LPIM) is granted (either on 32-bit for AWE support or on 64-bit by itself), we recommend also setting max server memory. Para obtener más información sobre LPIM, consulte Opciones de configuración de memoria del servidor.For more information on LPIM, refer to Server Memory Server Configuration Options

Nota

Las versiones anteriores de SQL ServerSQL Server pueden ejecutarse en un sistema operativo de 32 bits.Older versions of SQL ServerSQL Server could run on a 32-bit operating system. Para acceder a más de 4 gigabytes (GB) de memoria en un sistema operativo de 32 bits, se requieren las extensiones de ventana de dirección (AWE) para administrar la memoria.Accessing more than 4 gigabytes (GB) of memory on a 32-bit operating system required Address Windowing Extensions (AWE) to manage the memory. Esto no es necesario cuando SQL ServerSQL Server se ejecuta en sistemas operativos de 64 bits.This is not necessary when SQL ServerSQL Server is running on 64-bit operation systems. Para más información acerca de AWE, consulte Espacio de dirección del proceso y Administrar la memoria para bases de datos de gran tamaño en la documentación de SQL Server 2008SQL Server 2008.For more information about AWE, see Process Address Space and Managing Memory for Large Databases in the SQL Server 2008SQL Server 2008 documentation.

Cambios en la administración de memoria a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x)Changes to Memory Management starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)

En versiones anteriores de SQL ServerSQL Server (SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 y SQL Server 2008 R2SQL Server 2008 R2), la asignación de memoria se realizaba mediante cinco mecanismos diferentes:In earlier versions of SQL ServerSQL Server ( SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2), memory allocation was done using five different mechanisms:

  • Asignador de página única (SPA) , que incluye solo las asignaciones de memoria menores o iguales a 8 KB en el proceso SQL ServerSQL Server.Single-Page Allocator (SPA), including only memory allocations that were less than, or equal to 8-KB in the SQL ServerSQL Server process. 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.The max server memory (MB) and min server memory (MB) configuration options determined the limits of physical memory that the SPA consumed. El grupo de búferes era el mecanismo para SPA y el mayor consumidor de asignaciones de página única.THe buffer pool was simultaneously the mechanism for SPA, and the largest consumer of single-page allocations.
  • Asignador de varias páginas (MPA) , para las asignaciones de memoria que solicitan más de 8 KB.Multi-Page Allocator (MPA), for memory allocations that request more than 8-KB.
  • Asignador de CLR, que incluye las pilas CLR de SQL y las asignaciones globales creadas durante la inicialización de CLR.CLR Allocator, including the SQL CLR heaps and its global allocations that are created during CLR initialization.
  • Asignaciones de memoria para pilas de subprocesos en el proceso SQL ServerSQL Server.Memory allocations for thread stacks in the SQL ServerSQL Server process.
  • Asignaciones de Windows directas, para las solicitudes de asignación de memoria que se hacen directamente a Windows.Direct Windows allocations (DWA), for memory allocation requests made directly to 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 ServerSQL Server.These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL ServerSQL Server process. 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.Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

A partir de SQL Server 2012 (11.x)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) .Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), Single-Page allocations, Multi-Page allocations and CLR allocations are all consolidated into a "Any size" Page Allocator, and it's included in memory limits that are controlled by max server memory (MB) and min server memory (MB) configuration options. 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 ServerSQL Server.This change provided a more accurate sizing ability for all memory requirements that go through the SQL ServerSQL Server memory manager.

Importante

Revise cuidadosamente las configuraciones actuales de memoria de servidor máxima (MB) y memoria de servidor mínima (MB) después de actualizar SQL Server 2012 (11.x)SQL Server 2012 (11.x) a SQL Server 2017SQL Server 2017.Carefully review your current max server memory (MB) and min server memory (MB) configurations after you upgrade to SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017. Esto se debe a que, a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), estas configuraciones ahora incluyen y representan más asignaciones de memoria en comparación con versiones anteriores.This is because starting in SQL Server 2012 (11.x)SQL Server 2012 (11.x), such configurations now include and account for more memory allocations compared to earlier versions. Estos cambios se aplican tanto a las versiones de 32 como de 64 bits de SQL Server 2012 (11.x)SQL Server 2012 (11.x) y SQL Server 2014 (12.x)SQL Server 2014 (12.x), así como a las versiones de 64 bits de SQL Server 2016 (13.x)SQL Server 2016 (13.x) hasta SQL Server 2017SQL Server 2017.These changes apply to both 32-bit and 64-bit versions of SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 64-bit versions of SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017.

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) :The following table indicates whether a specific type of memory allocation is controlled by the max server memory (MB) and min server memory (MB) configuration options:

Tipo de asignación de memoriaType of memory allocation SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 y SQL Server 2008 R2SQL Server 2008 R2, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2 A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x)Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)
Asignaciones de página únicaSingle-page allocations Yes Sí, consolidadas bajo las asignaciones de páginas de cualquier tamañoYes, consolidated into "any size" page allocations
Asignaciones de varias páginasMulti-page allocations NoNo Sí, consolidadas bajo las asignaciones de páginas de cualquier tamañoYes, consolidated into "any size" page allocations
Asignaciones de CLRCLR allocations NoNo Yes
Memoria de pilas de subprocesosThread stacks memory NoNo NoNo
Asignaciones directas de WindowsDirect allocations from Windows NoNo NoNo

A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), SQL ServerSQL Server podría asignar más memoria que el valor especificado en el valor de memoria de servidor máxima.Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), SQL ServerSQL Server might allocate more memory than the value specified in the max server memory setting. Esto puede ocurrir cuando el valor de Memoria total del servidor (KB ) ya ha alcanzado la configuración de Memoria total del servidor (KB) (tal y como se especifica en la memoria de servidor máxima).This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting (as specified by max server memory). 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 ServerSQL Server puede realizar compromisos por encima de lo indicado en vez de rechazar las solicitudes de memoria.If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, SQL ServerSQL Server can perform over-commitment instead of rejecting the memory request.

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) .As soon as this allocation is performed, the Resource Monitor background task starts to signal all memory consumers to release the allocated memory, and tries to bring the Total Server Memory (KB) value below the Target Server Memory (KB) specification. Por lo tanto, el uso de memoria de SQL ServerSQL Server podría superar brevemente la configuración establecida por memoria de servidor máxima.Therefore, SQL ServerSQL Server memory usage could briefly exceed the max server memory setting. 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 y de Memoria total del servidor (KB) .In this situation, the Total Server Memory (KB) performance counter reading will exceed the max server memory and Target Server Memory (KB) settings.

Este comportamiento se observa normalmente durante las siguientes operaciones:This behavior is typically observed during the following operations:

  • Consultas del índice de almacén de columnas grandes.Large Columnstore index queries.
  • Construcciones o reconstrucciones del índice de almacén de columnas, que usan grandes volúmenes de memoria para realizar las operaciones Hash y Sort.Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations.
  • Operaciones de copia de seguridad que requieren grandes búferes de memoria.Backup operations that require large memory buffers.
  • Operaciones de seguimiento que tienen que almacenar parámetros de entrada grandes.Tracing operations that have to store large input parameters.

Cambios en "memory_to_reserve" a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x)Changes to "memory_to_reserve" starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)

En versiones anteriores de SQL Server (SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 y SQL Server 2008 R2SQL Server 2008 R2), el administrador de memoria de SQL ServerSQL 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) .In earlier versions of SQL Server ( SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2), the SQL ServerSQL Server memory manager set aside a part of the process virtual address space (VAS) for use by the Multi-Page Allocator (MPA), CLR Allocator, memory allocations for thread stacks in the SQL Server process, and Direct Windows allocations (DWA). Esta parte del espacio de direcciones virtuales también se conoce como región "Mem-To-Leave" o "grupo sin búferes".This part of the virtual address space is also known as "Mem-To-Leave" or "non-Buffer Pool" region.

El espacio de direcciones virtuales que está reservado para las asignaciones viene determinado por la opción de configuración memory_to_reserve .The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. El valor predeterminado que usa SQL ServerSQL Server es 256 MB.The default value that SQL ServerSQL Server uses is 256 MB. Para invalidar el valor predeterminado, use el parámetro de inicio -g de SQL ServerSQL Server.To override the default value, use the SQL ServerSQL Server -g startup parameter. Consulte la página de documentación sobre Opciones de inicio del servicio de motor de base de datos para obtener información sobre el parámetro de inicio -g.Refer to the documentation page on Database Engine Service Startup Options for information on the -g startup parameter.

Dado que a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x) el nuevo 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.Because starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), the new "any size" page allocator also handles allocations greater than 8 KB, the memory_to_reserve value does not include the multi-page allocations. A excepción de este cambio, todo lo demás sigue siendo igual con respecto a esta opción de configuración.Except for this change, everything else remains the same with this configuration option.

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 ServerSQL Server:The following table indicates whether a specific type of memory allocation falls into the memory_to_reserve region of the virtual address space for the SQL ServerSQL Server process:

Tipo de asignación de memoriaType of memory allocation SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 y SQL Server 2008 R2SQL Server 2008 R2, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2 A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x)Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)
Asignaciones de página únicaSingle-page allocations NoNo No, consolidadas bajo las asignaciones de páginas de cualquier tamañoNo, consolidated into "any size" page allocations
Asignaciones de varias páginasMulti-page allocations Yes No, consolidadas bajo las asignaciones de páginas de cualquier tamañoNo, consolidated into "any size" page allocations
Asignaciones de CLRCLR allocations Yes Yes
Memoria de pilas de subprocesosThread stacks memory Yes Yes
Asignaciones directas de WindowsDirect allocations from Windows Yes Yes

Administración dinámica de memoriaDynamic Memory Management

El comportamiento predeterminado de administración de memoria del Motor de base de datos de SQL ServerSQL Server Database Engine es adquirir toda la memoria que necesita sin provocar una escasez de memoria en el sistema.The default memory management behavior of the Motor de base de datos de SQL ServerSQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. El Motor de base de datos de SQL ServerSQL Server Database Engine lo consigue mediante las API de notificación de memoria de Microsoft Windows.The Motor de base de datos de SQL ServerSQL Server Database Engine does this by using the Memory Notification APIs in Microsoft Windows.

Cuando SQL ServerSQL Server utiliza la memoria de manera dinámica, realiza una consulta periódica en el sistema para determinar la cantidad de memoria libre.When SQL ServerSQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. El mantenimiento de esta memoria libre evita la paginación en el sistema operativo (SO).Maintaining this free memory prevents the operating system (OS) from paging. Si hay menos memoria libre, SQL ServerSQL Server libera memoria para el sistema operativo.If less memory is free, SQL ServerSQL Server releases memory to the OS. Si hay más memoria libre, SQL ServerSQL Server puede asignar más memoria.If more memory is free, SQL ServerSQL Server may allocate more memory. SQL ServerSQL 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.adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space.

La Memoria de servidor máxima controla la asignación de memoria de SQL ServerSQL 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 ).Max server memory controls the SQL ServerSQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in 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)SQL Server 2012 (11.x).1 CLR memory is managed under max_server_memory allocations starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x).

La consulta siguiente devuelve información sobre la memoria asignada actual:The following query returns information about currently allocated memory:

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;  

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-SQLTransact-SQL y cualquier otra memoria asignada por un DLL que no es de SQL ServerSQL Server no están controladas por la memoria de servidor máxima. Memory for thread stacks1, CLR2, extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in Transact-SQLTransact-SQL statements, and any memory allocated by a non SQL ServerSQL Server DLL are not controlled by max server memory.

1 Consulte la página de documentación sobre cómo Establecer la opción de configuración del servidor Máximo de subprocesos de trabajo para obtener información sobre los subprocesos de trabajo predeterminados calculados para un determinado número de CPU con afinidad en el host actual.1 Refer to the documentation page on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host. Los tamaños de pila de SQL ServerSQL Server son los siguientes:SQL ServerSQL Server stack sizes are as follows:

Arquitectura de SQL ServerSQL Server Architecture Arquitectura del SOOS Architecture Tamaño de la pilaStack Size
x86 (32 bits)x86 (32-bit) x86 (32 bits)x86 (32-bit) 512 KB512 KB
x86 (32 bits)x86 (32-bit) x64 (64 bits)x64 (64-bit) 768 KB768 KB
x64 (64 bits)x64 (64-bit) x64 (64 bits)x64 (64-bit) 2048 KB2048 KB
IA64 (Itanium)IA64 (Itanium) IA64 (Itanium)IA64 (Itanium) 4096 KB4096 KB

2 La memoria CLR se administra en asignaciones de max_server_memory a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x).2 CLR memory is managed under max_server_memory allocations starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x).

SQL ServerSQL Server usa la API de notificación de memoria QueryMemoryResourceNotification para determinar el momento en que el Administrador de memoria de SQL ServerSQL Server puede asignar y liberar memoria.uses the memory notification API QueryMemoryResourceNotification to determine when the SQL ServerSQL Server Memory Manager may allocate memory and release memory.

Cuando SQL ServerSQL Server se inicia, calcula el tamaño del espacio de direcciones virtuales del grupo de búferes basándose en un número de 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.When SQL ServerSQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL ServerSQL 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.reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.

A continuación, la instancia sigue adquiriendo la memoria que necesita para la carga de trabajo.The instance then continues to acquire memory as needed to support the workload. A medida que se conectan más usuarios y se ejecutan consultas, SQL ServerSQL Server adquiere la memoria física adicional según la demanda.As more users connect and run queries, SQL ServerSQL Server acquires the additional physical memory on demand. Una instancia de SQL ServerSQL Server sigue adquiriendo memoria física hasta que alcanza su asignación de memoria de servidor máxima o hasta que el sistema operativo indica que ya no existe más memoria libre; libera memoria cuando se supera el valor de memoria de servidor mínima y el sistema operativo indica que hay escasez de memoria libre.A SQL ServerSQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or the OS indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and the OS indicates that there is a shortage of free memory.

Cuando se inician otras aplicaciones en un equipo que ejecuta una instancia de SQL ServerSQL Server, consumen memoria y la cantidad de memoria física disponible cae por debajo del destino de SQL ServerSQL Server .As other applications are started on a computer running an instance of SQL ServerSQL Server, they consume memory and the amount of free physical memory drops below the SQL ServerSQL Server target. La instancia de SQL ServerSQL Server ajusta su consumo de memoria.The instance of SQL ServerSQL Server adjusts its memory consumption. Si se detiene otra aplicación y aumenta la cantidad de memoria disponible, la instancia de SQL ServerSQL Server aumenta el tamaño de su asignación de memoria.If another application is stopped and more memory becomes available, the instance of SQL ServerSQL Server increases the size of its memory allocation. SQL ServerSQL 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.can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

Efectos de las opciones min y max server memoryEffects of min and 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 memorias caché del motor de base de datos de SQL ServerSQL Server.The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool and other caches of the SQL ServerSQL Server Database Engine. El grupo de búferes no adquiere inmediatamente la cantidad de memoria especificada en Memoria de servidor mínima.The buffer pool does not immediately acquire the amount of memory specified in min server memory. El grupo de búferes comienza con la memoria precisa para el inicio.The buffer pool starts with only the memory required to initialize. Según aumenta la carga de trabajo del Motor de base de datos de SQL ServerSQL Server Database Engine, se sigue adquiriendo la memoria necesaria para permitir la carga de trabajo.As the Motor de base de datos de SQL ServerSQL Server Database Engine workload increases, it keeps acquiring the memory required to support the workload. El grupo de búferes no libera nada de la memoria adquirida hasta que alcanza la cantidad especificada en Memoria de servidor mínima.The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. 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.Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. 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.The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.

Nota

SQL ServerSQL Server adquiere, como un proceso, más memoria de la especificada en la opción max server memory.as a process acquires more memory than specified by max server memory option. 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 ServerSQL Server.Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually still represents the largest portion of memory consumed by SQL ServerSQL Server.

La cantidad de memoria que adquiere el Motor de base de datos de SQL ServerSQL Server Database Engine es totalmente dependiente de la carga de trabajo colocada en la instancia.The amount of memory acquired by the Motor de base de datos de SQL ServerSQL Server Database Engine is entirely dependent on the workload placed on the instance. Una instancia de SQL ServerSQL Server que no procesa muchas solicitudes nunca podrá alcanzar el nivel de min server memory.A SQL ServerSQL Server instance that is not processing many requests may never reach min server memory.

Si se especifica el mismo valor para memoria de servidor mínima y memoria de servidor máxima, una vez que la memoria asignada a Motor de base de datos de SQL ServerSQL Server Database Engine alcanza ese valor, Motor de base de datos de SQL ServerSQL Server Database Engine detiene dinámicamente la adquisición y liberación de la memoria para el grupo de búferes.If the same value is specified for both min server memory and max server memory, then once the memory allocated to the Motor de base de datos de SQL ServerSQL Server Database Engine reaches that value, the Motor de base de datos de SQL ServerSQL Server Database Engine stops dynamically freeing and acquiring memory for the buffer pool.

Si una instancia de SQL ServerSQL 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 ServerSQL Server puede ralentizar el inicio de otras aplicaciones.If an instance of SQL ServerSQL Server is running on a computer where other applications are frequently stopped or started, the allocation and deallocation of memory by the instance of SQL ServerSQL Server may slow the startup times of other applications. Además, si SQL ServerSQL 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 ServerSQL Server.Also, if SQL ServerSQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL ServerSQL Server. En estos casos, puede utilizar las opciones Memoria de servidor mínima y Memoria de servidor máxima para controlar cuánta memoria puede usar SQL ServerSQL Server .In these cases, you can use the min server memory and max server memory options to control how much memory SQL ServerSQL Server can use. Las opciones memoria de servidor mínima y memoria de servidor máxima se expresan en megabytes.The min server memory and max server memory options are specified in megabytes. Para obtener más información, vea Opciones de configuración de memoria del servidor.For more information, see Server Memory Configuration Options.

Memoria que usan las especificaciones de objetos de SQL ServerMemory used by SQL Server objects specifications

La siguiente lista muestra la cantidad de memoria aproximada que usan diferentes objetos en SQL ServerSQL Server.The following list describes the approximate amount of memory used by different objects in SQL ServerSQL Server. Las cantidades mostradas son estimaciones y pueden variar según el entorno y cómo se crean los objetos:The amounts listed are estimates and can vary depending on the environment and how objects are created:

  • Bloqueo (tal y como lo mantiene el Administrador de bloqueos): 64 bytes + 32 bytes por propietarioLock (as maintained by the Lock Manager): 64 bytes + 32 bytes per owner
  • Conexión de usuario: aproximadamente (3 * tamaño_del_paquete_de_red + 94 kb)User connection: Approximately (3 * network_packet_size + 94 kb)

El tamaño del paquete de red es el tamaño de los paquetes del esquema de datos tabulares (TDS) que se utilizan para la comunicación entre las aplicaciones y el motor de base de datos de SQL ServerSQL Server.The network packet size is the size of the tabular data scheme (TDS) packets that are used to communicate between applications and the SQL ServerSQL Server Database Engine. 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.The default packet size is 4 KB, and is controlled by the network packet size configuration option.

Cuando los conjuntos de resultados activos múltiples están habilitados, la conexión de usuario es aproximadamente (3 + 3 *num_logical_connections)* network_packet_size + 94 KBWhen multiple active result sets (MARS) are enabled, the user connection is approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB

Efectos de memoria mínima por consultaEffects of min memory per query

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.The min memory per query configuration option establishes the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query. Esto también se conoce como concesión de memoria mínima.This is also known as the minimum memory grant. 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.All queries must wait until the minimum memory requested can be secured, before execution can start, or until the value specified in the query wait server configuration option is exceeded. El tipo de espera que se acumula en este escenario es RESOURCE_SEMAPHORE.The wait type that is accumulated in this scenario is 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:Do not set the min memory per query server configuration option too high, especially on very busy systems, because doing so could lead to:

  • El aumento de la competición por los recursos de memoria.Increased competition for memory resources.
  • 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.Decreased concurrency by increasing the amount of memory for every single query, even if the required memory at runtime is lower that this configuration.

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.For recommendations on using this configuration, see Configure the min memory per query Server Configuration Option.

Consideraciones de concesión de memoriaMemory grant considerations

Para la ejecución del modo de fila, no se puede superar la concesión de memoria inicial bajo ninguna condición.For row mode execution, the initial memory grant cannot be exceeded under any condition. Si se necesita más memoria que la concesión inicial para ejecutar operaciones de hash u orden, estas se desbordarán al disco.If more memory than the initial grant is needed to execute hash or sort operations, then these will spill to disk. 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.A hash operation that spills is supported by a Workfile in TempDB, while a sort operation that spills is supported by a Worktable.

Un desbordamiento que se produzca durante una operación de orden se conoce como una advertencia antes de ordenar.A spill that occurs during a Sort operation is known as a Sort Warning. Las advertencias antes de ordenar indican que las operaciones de orden no caben en la memoria.Sort warnings indicate that sort operations do not fit into memory. 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).This does not include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).

Un desbordamiento que se produzca durante una operación de hash se conoce como una advertencia hash.A spill that occurs during a hash operation is known as a Hash Warning. Tienen lugar cuándo se ha producido una recursividad hash o un cese de hash (salida hash) durante una operación de hash.These occur when a hash recursion or cessation of hashing (hash bailout) has occurred during a hashing operation.

  • 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.Hash recursion occurs when the build input does not fit into available memory, resulting in the split of input into multiple partitions that are processed separately. Si alguna de estas particiones sigue sin caber en la memoria disponible, se vuelve a dividir en subparticiones, que también se procesan por separado.If any of these partitions still do not fit into available memory, it is split into sub-partitions, which are also processed separately. 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.This splitting process continues until each partition fits into available memory or until the maximum recursion level is reached.
  • 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.Hash bailout occurs when a hashing operation reaches its maximum recursion level and shifts to an alternate plan to process the remaining partitioned data. Estos eventos pueden producir un rendimiento reducido en el servidor.These events can cause reduced performance in your server.

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.For batch mode execution, the initial memory grant can dynamically increase up to a certain internal threshold by default. Este mecanismo de concesión de memoria dinámico está diseñado para permitir la ejecución residente de memoria de las operaciones de hash u orden que se ejecutan en el modo por lotes.This dynamic memory grant mechanism is designed to allow memory resident execution of hash or sort operations running in batch mode. Si estas operaciones siguen sin caber en la memoria, se desbordarán al disco.If these operations still do not fit into memory, then these will spill to disk.

Para obtener más información sobre los modos de ejecución, vea Guía de arquitectura de procesamiento de consultas.For more information on execution modes, see the Query Processing Architecture Guide.

Administración de búferBuffer management

El propósito principal de una base de datos de SQL ServerSQL 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.The primary purpose of a SQL ServerSQL Server database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. Debido a que las operaciones de E/S de disco pueden consumir muchos recursos y tardar bastante tiempo en completarse, SQL ServerSQL Server se centra en hacer la E/S muy eficaz.And because disk I/O operations can consume many resources and take a relatively long time to finish, SQL ServerSQL Server focuses on making I/O highly efficient. La administración de búfer es un componente clave para lograr esta eficacia.Buffer management is a key component in achieving this efficiency. 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.The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer cache (also called the buffer pool), to reduce database file I/O.

Cómo funciona la administración de búferHow buffer management works

Un búfer es una página de 8 KB en memoria (el mismo tamaño que una página de índice o de datos).A buffer is an 8 KB page in memory, the same size as a data or index page. Por tanto, la memoria caché del búfer está dividida en páginas de 8 KB.Thus, the buffer cache is divided into 8 KB pages. 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.The buffer manager manages the functions for reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk. 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.A page remains in the buffer cache until the buffer manager needs the buffer area to read in more data. Los datos solo vuelven a escribirse en el disco si se han modificado.Data is written back to disk only if it is modified. Los datos de la memoria caché del búfer se pueden modificar varias veces antes de que se vuelvan a escribir en el disco.Data in the buffer cache can be modified multiple times before being written back to disk. Para más información, consulte Leer páginas y Escribir páginas.For more information, see Reading Pages and Writing Pages.

Cuando SQL ServerSQL Server se inicia, calcula el tamaño del espacio de direcciones virtuales de la caché de búferes basándose en un número de 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.When SQL ServerSQL Server starts, it computes the size of virtual address space for the buffer cache based on a number of parameters such as the amount of physical memory on the system, the configured number of maximum server threads, and various startup parameters. SQL ServerSQL 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.reserves this computed amount of its process virtual address space (called the memory target) for the buffer cache, but it acquires (commits) only the required amount of physical memory for the current load. Puede realizar una consulta en las columnas bpool_commit_target y bpool_committed en 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.You can query the bpool_commit_target and bpool_committed columns in the sys.dm_os_sys_info catalog view to return the number of pages reserved as the memory target and the number of pages currently committed in the buffer cache, respectively.

El intervalo entre el inicio de SQL ServerSQL Server y el momento en que la caché del búfer obtiene su memoria objetivo se llama arranque.The interval between SQL ServerSQL Server startup and when the buffer cache obtains its memory target is called ramp-up. Durante este período, las solicitudes de lectura llenan los búferes según sea necesario.During this time, read requests fill the buffers as needed. Por ejemplo, una solicitud de lectura de una página de 8 KB llena una única página de búfer.For example, a single 8 KB page read request fills a single buffer page. Esto significa que el arranque depende del número y el tipo de solicitudes del cliente.This means the ramp-up depends on the number and type of client requests. 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).Ramp-up is expedited by transforming single page read requests into aligned eight page requests (making up one extent). Esto permite que el arranque finalice mucho más rápido, especialmente en equipos con mucha memoria.This allows the ramp-up to finish much faster, especially on machines with a lot of memory. Para obtener más información acerca de las páginas y las extensiones, vea Guía de arquitectura de páginas y extensiones.For more information about pages and extents, refer to Pages and Extents Architecture Guide.

Debido a que el administrador de búfer utiliza la mayor parte de la memoria en el proceso de SQL ServerSQL Server , coopera con el administrador de memoria para permitir que otros componentes utilicen sus búferes.Because the buffer manager uses most of the memory in the SQL ServerSQL Server process, it cooperates with the memory manager to allow other components to use its buffers. El administrador de búfer interactúa principalmente con los siguientes componentes:The buffer manager interacts primarily with the following components:

  • 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.Resource manager to control overall memory usage and, in 32-bit platforms, to control address space usage.
  • Administrador de bases de datos y SQL ServerSQL Server Operating System (SQLOS), para operaciones de E/S de archivos de bajo nivel.Database manager and the SQL ServerSQL Server Operating System (SQLOS) for low-level file I/O operations.
  • Administrador de registros, para registros de escritura anticipada.Log manager for write-ahead logging.

Características admitidasSupported Features

El administrador de búfer admite las características siguientes:The buffer manager supports the following features:

  • El administrador de búfer está preparado para el acceso no uniforme a memoria (NUMA, Non-Uniform Memory Access) .The buffer manager is non-uniform memory access (NUMA) aware. 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.Buffer cache pages are distributed across hardware NUMA nodes, which allows a thread to access a buffer page that is allocated on the local NUMA node rather than from foreign memory.

  • 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.The buffer manager supports Hot Add Memory, which allows users to add physical memory without restarting the server.

  • El administrador de búfer admite páginas grandes en plataformas de 64 bits.The buffer manager supports large pages on 64-bit platforms. El tamaño de página es específico de la versión de Windows.The page size is specific to the version of Windows.

    Nota

    En versiones anteriores a SQL Server 2012 (11.x)SQL Server 2012 (11.x), es necesaria una marca de seguimiento 834 para habilitar las páginas grandes en SQL ServerSQL Server.Prior to SQL Server 2012 (11.x)SQL Server 2012 (11.x), enabling large pages in SQL ServerSQL Server requires trace flag 834.

  • El administrador de búfer proporciona diagnósticos adicionales que se muestran mediante vistas de administración dinámica.The buffer manager provides additional diagnostics that are exposed through dynamic management views. Puede utilizar estas vistas para supervisar diversos recursos del sistema operativo específicos de SQL ServerSQL Server.You can use these views to monitor a variety of operating system resources that are specific to SQL ServerSQL Server. Por ejemplo, puede usar la vista sys.dm_os_buffer_descriptors para supervisar las páginas de la caché del búfer.For example, you can use the sys.dm_os_buffer_descriptors view to monitor the pages in the buffer cache.

E/S de discoDisk I/O

El administrador de búfer solo realiza tareas de lectura y escritura en la base de datos.The buffer manager only performs reads and writes to the database. 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.Other file and database operations such as open, close, extend, and shrink are performed by the database manager and file manager components.

Las operaciones de E/S de disco que realiza el administrador de búfer tienen las siguientes características:Disk I/O operations by the buffer manager have the following characteristics:

  • 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.All I/Os are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.
  • Todas las operaciones de E/S se emiten en los subprocesos de llamada a menos que la opción affinity I/O (E/S de afinidad) esté en uso.All I/Os are issued in the calling threads unless the affinity I/O option is in use. La opción de máscara de afinidad de E/S enlaza la E/S del disco de SQL ServerSQL Server a un subconjunto específico de unidades CPU.The affinity I/O mask option binds SQL ServerSQL Server disk I/O to a specified subset of CPUs. En entornos de procesamiento de transacciones en línea (OLTP) de SQL ServerSQL Server de grandes prestaciones, esta extensión puede mejorar el rendimiento de los subprocesos de SQL ServerSQL Server que emiten E/S.In high-end SQL ServerSQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL ServerSQL Server threads issuing I/Os.
  • 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.Multiple page I/Os are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory. Esto significa que SQL ServerSQL 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.This means that SQL ServerSQL Server can quickly fill or flush the buffer cache while avoiding multiple physical I/O requests.

Solicitudes de E/S largasLong I/O requests

El administrador de búfer informa sobre cualquier solicitud de E/S que haya quedado pendiente durante al menos 15 segundos.The buffer manager reports on any I/O request that has been outstanding for at least 15 seconds. Esto ayuda al administrador del sistema a distinguir entre problemas de SQL ServerSQL Server y problemas del subsistema de E/S.This helps the system administrator distinguish between SQL ServerSQL Server problems and I/O subsystem problems. El mensaje de error 833 se notifica y aparece en el registro de errores de SQL ServerSQL Server de la siguiente forma:Error message 833 is reported and appears in the SQL ServerSQL Server error log as follows:

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 de escritura, pero esto no se indica actualmente en el mensaje.A long I/O may be either a read or a write; it is not currently indicated in the message. Los mensajes de E/S larga son advertencias, no errores.Long-I/O messages are warnings, not errors. No indican problemas con SQL ServerSQL Server, sino con el sistema de E/S subyacente.They do not indicate problems with SQL ServerSQL Server but with the underlying I/O system. Los mensajes se notifican para ayudar a los administradores del sistema a encontrar la causa de los tiempos de respuesta largos de SQL ServerSQL Server con mayor rapidez y a distinguir problemas que estén fuera del control de SQL ServerSQL Server.The messages are reported to help the system administrator find the cause of poor SQL ServerSQL Server response times more quickly, and to distinguish problems that are outside the control of SQL ServerSQL Server. Por eso, no es necesario tomar ninguna acción, pero el administrador del sistema debe investigar por qué la solicitud de E/S tardó tanto tiempo y si ese tiempo es justificable.As such, they do not require any action, but the system administrator should investigate why the I/O request took so long, and whether the time is justifiable.

Causas de solicitudes de E/S largasCauses of Long-I/O Requests

Un mensaje de E/S larga puede indicar que una E/S está permanente bloqueada y que nunca se completará (lo que también se conoce como E/S perdida) o que simplemente no se completó aún.A long-I/O message may indicate that an I/O is permanently blocked and will never complete (known as lost I/O), or merely that it just has not completed yet. No es posible saber con los datos del mensaje de qué caso se trata, aunque una E/S perdida casi siempre llevará a un tiempo de espera de bloqueo temporal.It is not possible to tell from the message which scenario is the case, although a lost I/O will often lead to a latch timeout.

Las E/S largas suelen indicar una carga de trabajo de SQL ServerSQL Server demasiado intensa para el subsistema de disco.Long I/Os often indicate a SQL ServerSQL Server workload that is too intense for the disk subsystem. Se puede indicar un subsistema de disco inadecuado cuando:An inadequate disk subsystem may be indicated when:

  • Aparecen múltiples mensajes de E/S largas en el registro de errores durante una carga de trabajo pesada de SQL ServerSQL Server .Multiple long I/O messages appear in the error log during a heavy SQL ServerSQL Server workload.
  • Los contadores de rendimiento muestran latencias de disco prolongadas, colas de disco largas o no muestran el tiempo de inactividad de disco.Perfmon counters show long disk latencies, long disk queues, or no disk idle time.

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.Long I/Os may also be caused by a component in the I/O path (for example, a driver, controller, or firmware) continually postponing servicing an old I/O request in favor of servicing newer requests that are closer to the current position of the disk head. La técnica corriente de procesar solicitudes según su prioridad sobre la base de las que están más cerca de la posición actual del cabezal de lectura/escritura se conoce como "búsqueda de elevador".The common technique of processing requests in priority based upon which ones are closest to the current position of the read/write head is known as "elevator seeking." Esto puede resultar difícil de corroborar con la herramienta Monitor del sistema de Windows (PERFMON.EXE) porque a la mayor parte de las E/S se las da servicio inmediatamente.This may be difficult to corroborate with the Windows System Monitor (PERFMON.EXE) tool because most I/Os are being serviced promptly. 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.Long I/O requests can be aggravated by workloads that perform large amounts of sequential I/O, such as backup and restore, table scans, sorting, creating indexes, bulk loads, and zeroing out files.

Las E/S largas aisladas que no están relacionadas con ninguna de las situaciones anteriores pueden estar causadas por un problema con el hardware o el controlador.Isolated long I/Os that do not appear related to any of the previous conditions may be caused by a hardware or driver problem. El registro de eventos del sistema puede contener un evento relacionado que ayude a diagnosticar el problema.The system event log may contain a related event that helps to diagnose the problem.

Detección de la presión de memoriaMemory pressure detection

La presión de memoria es una condición resultante de la escasez de memoria y puede dar lugar a:Memory pressure is a condition resulting from memory shortage, and can result in:

  • Operaciones de E/S adicionales (por ejemplo, un subproceso en segundo plano de escritura diferida muy activo).Extra I/Os (such as very active lazy writer background thread)
  • Mayor proporción de recompilación.Higher recompile ratio
  • Consultas en ejecución más prolongadas (si existen esperas de concesión de memoria).Longer running queries (if memory grant waits exist)
  • Ciclos de CPU adicionales.Extra CPU cycles

Esta situación se puede desencadenar debido a causas externas o internas.This situation can be triggered by external or internal causes. Las causas externas incluyen:External causes include:

  • La memoria física (RAM) disponible es baja.Available physical memory (RAM) is low. 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.This causes the system to trim working sets of currently running processes, which may result in overall slowdown. SQL ServerSQL 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.may reduce the commit target of the buffer pool and start trimming internal caches more often.
  • La memoria del sistema disponible general (que incluye el archivo de paginación del sistema) es baja.Overall available system memory (which includes the system page file) is low. Esto puede provocar un error del sistema en las asignaciones de memoria, ya que no puede paginar la memoria asignada actualmente.This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. Las causas internas incluyen:Internal causes include:
  • Responder a la presión de memoria externa, cuando el Motor de base de datos de SQL ServerSQL Server Database Engine establece límites de uso de memoria más bajos.Responding to the external memory pressure, when the Motor de base de datos de SQL ServerSQL Server Database Engine sets lower memory usage caps.
  • La configuración de memoria se redujo manualmente reduciendo la configuración memoria de servidor máxima.Memory settings were manually lowered by reducing the max server memory configuration.
  • Cambios en la distribución de memoria de los componentes internos entre varias cachés.Changes in memory distribution of internal components between the several caches.

El Motor de base de datos de SQL ServerSQL Server Database Engine 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.The Motor de base de datos de SQL ServerSQL Server Database Engine implements a framework dedicated to detecting and handling memory pressure, as part of its dynamic memory management. Este marco de trabajo incluye la tarea en segundo plano denominada Monitor de recursos.This framework includes the backgroud task called Resource Monitor. La tarea Monitor de recursos supervisa el estado de los indicadores de memoria externa e interna.The Resource Monitor task monitors the state of external and internal memory indicators. Cuando uno de estos indicadores cambia de estado, calcula la notificación correspondiente y la difunde.Once one of these indicators changes status, it calculates the corresponding notification and it broadcasts it. Estas notificaciones son mensajes internos desde cada uno de los componentes del motor y se almacenan en búferes en anillo.These notifications are internal messages from each of the engine components, and stored in ring buffers.

Dos búferes en anillo contienen información relevante para la administración dinámica de memoria:Two ring buffers hold information relevant to dynamic memory management:

  • 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.The Resource Monitor ring buffer, which tracks Resource Monitor activity like was memory pressure signaled or not. 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.This ring buffer has status information depending on the current condition of RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY, or 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.The Memory Broker ring buffer, which contains records of memory notifications for each Resource Governor resource pool. 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.As internal memory pressure is detected, low memory notification is turned on for components that allocate memory, to trigger actions meant to balance the memory between caches.

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.Memory brokers monitor the demand consumption of memory by each component and then based on the information collected, it calculates and optimal value of memory for each of these components. Hay un conjunto de agentes para cada grupo de recursos de Resource Governor.There is a set of brokers for each Resource Governor resource pool. Después, esta información se difunde a cada uno de los componentes, que aumentan o reducen su uso según sea necesario.This information is then broadcast to each of the components, which grow or shrink their usage as required. Para obtener más información sobre los agentes de memoria, vea sys.dm_os_memory_brokers.For more information about memory brokers, see sys.dm_os_memory_brokers.

Detección de erroresError Detection

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.Database pages can use one of two optional mechanisms that help insure the integrity of the page from the time it is written to disk until it is read again: torn page protection and checksum protection. 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.These mechanisms allow an independent method of verifying the correctness of not only the data storage, but hardware components such as controllers, drivers, cables, and even the operating system. 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.The protection is added to the page just before writing it to disk, and verified after it is read from disk.

SQL ServerSQL 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.will retry any read that fails with a checksum, torn page, or other I/O error four times. 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.If the read is successful in any one of the retry attempts, a message will be written to the error log and the command that triggered the read will continue. Si los reintentos no se realizan correctamente, el comando genera el mensaje de error 824.If the retry attempts fail, the command will fail with error message 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.The kind of page protection used is an attribute of the database containing the page. La protección de suma de comprobación es la protección predeterminada para bases de datos creadas en SQL Server 2005 (9.x)SQL Server 2005 (9.x) y en versiones posteriores.Checksum protection is the default protection for databases created in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later. El mecanismo de protección de páginas se especifica al crear la base de datos y se puede modificar con ALTER DATABASE SET.The page protection mechanism is specified at database creation time, and may be altered by using 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.You can determine the current page protection setting by querying the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.

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.If the page protection setting is changed, the new setting does not immediately affect the entire database. En cambio, las páginas adoptan el nivel de protección actual de la base de datos cuando se vuelven a escribir.Instead, pages adopt the current protection level of the database whenever they are written next. Esto significa que la base de datos puede estar compuesta de páginas con distintos tipos de protección.This means that the database may be composed of pages with different kinds of protection.

Protección contra página rasgadaTorn Page Protection

La protección contra página rasgada, que se introdujo en SQL ServerSQL Server 2000, es básicamente una forma de detectar errores en las páginas a causa de problemas con el suministro eléctrico.Torn page protection, introduced in SQL ServerSQL Server 2000, is primarily a way of detecting page corruptions due to power failures. 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.For example, an unexpected power failure may leave only part of a page written to disk. 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.When torn page protection is used, a specific 2-bit signature pattern for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. 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.When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. El patrón de firma alterna entre los binarios 01 y 10 en cada escritura, por lo que siempre es posible detectar las ocasiones en que solo una parte de los sectores las hicieron en el disco: si hay un bit con el estado incorrecto cuando la página se lee posteriormente, la página se escribió de forma incorrecta y se detecta una página rasgada.The signature pattern alternates between binary 01 and 10 with every write, so it is always possible to tell when only a portion of the sectors made it to disk: if a bit is in the wrong state when the page is later read, the page was written incorrectly and a torn page is detected. 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.Torn page detection uses minimal resources; however, it does not detect all errors caused by disk hardware failures. Para obtener información acerca de cómo configurar la detección de páginas rasgadas, vea ALTER DATABASE SET Options (Transact-SQL) (Opciones de ALTER DATABASE SET (Transact-SQL).For information on setting torn page detection, see ALTER DATABASE SET Options (Transact-SQL).

Protección de suma de comprobaciónChecksum Protection

La protección de suma de comprobación, característica implementada en SQL Server 2005 (9.x)SQL Server 2005 (9.x), proporciona una comprobación de integridad de datos más sólida.Checksum protection, introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x), provides stronger data integrity checking. 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.A checksum is calculated for the data in each page that is written, and stored in the page header. 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.Whenever a page with a stored checksum is read from disk, the database engine recalculates the checksum for the data in the page and raises error 824 if the new checksum is different from the stored checksum. 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 de recursos considerable.Checksum protection can catch more errors than torn page protection because it is affected by every byte of the page, however, it is moderately resource intensive. 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.When checksum is enabled, errors caused by power failures and flawed hardware or firmware can be detected any time the buffer manager reads a page from disk. Para obtener información acerca de cómo configurar la suma de comprobación, vea ALTER DATABASE SET Options (Transact-SQL) (Opciones de ALTER DATABASE SET (Transact-SQL)).For information on setting checksum, see ALTER DATABASE SET Options (Transact-SQL).

Importante

Si una base de datos de usuario o del sistema se actualiza a SQL Server 2005 (9.x)SQL Server 2005 (9.x) o una versión posterior, se conserva el valor de PAGE_VERIFY (NONE o TORN_PAGE_DETECTION).When a user or system database is upgraded to SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. Se recomienda utilizar CHECKSUM.We recommend that you use CHECKSUM. Es posible que TORN_PAGE_DETECTION utilice menos recursos, pero proporciona en cambio un subconjunto mínimo de la protección de CHECKSUM.TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.

Descripción del acceso no uniforme a memoriaUnderstanding Non-uniform Memory Access

SQL ServerSQL 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.is non-uniform memory access (NUMA) aware, and performs well on NUMA hardware without special configuration. 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.As clock speed and the number of processors increase, it becomes increasingly difficult to reduce the memory latency required to use this additional processing power. Para evitarlo, los proveedores de hardware proporcionan cachés L3 grandes, pero esto es solo una solución limitada.To circumvent this, hardware vendors provide large L3 caches, but this is only a limited solution. La arquitectura NUMA proporciona una solución escalable para este problema.NUMA architecture provides a scalable solution to this problem. SQL ServerSQL Server se ha diseñado para aprovechar los equipos basados en NUMA sin necesidad de realizar cambios en las aplicaciones.has been designed to take advantage of NUMA-based computers without requiring any application changes. Para más información, vea: Cómo: Configurar SQL Server para que use Soft-NUMA.For more information, see How to: Configure SQL Server to Use Soft-NUMA.

Consulte tambiénSee Also

Opciones de configuración de memoria del servidor Server Memory Server Configuration Options
Leer páginas Reading Pages
Escribir páginas Writing Pages
Cómo: Configurar SQL Server para que use Soft-NUMA How to: Configure SQL Server to Use Soft-NUMA
Requisitos para utilizar las tablas con optimización para memoria Requirements for Using Memory-Optimized Tables
Resolver problemas de memoria insuficienteResolve Out Of Memory Issues Using Memory-Optimized Tables