Enlazar una base de datos con tablas con optimización para memoria a un grupo de recursos de servidor

Artículo
05/30/2023

Se aplica a:SQL Server

Un grupo de recursos de servidor representa un subconjunto de recursos físicos que se pueden regular. De forma predeterminada, las bases de datos de SQL Server están enlazadas a los recursos del grupo de recursos de servidor predeterminado y los consumen. Para proteger SQL Server de manera que una o más tablas optimizadas para memoria no consuman sus recursos, y evitar que otros usuarios consuman memoria que las tablas optimizadas para memoria necesitan, debe crear un grupo de recursos de servidor diferente para administrar el consumo de memoria para la base de datos con tablas optimizadas para memoria.

Una base de datos solo se puede enlazar a un grupo de recursos de servidor. Sin embargo, puede enlazar varias bases de datos al mismo grupo. SQL Server permite enlazar una base de datos sin tablas optimizadas para memoria a un grupo de recursos de servidor, pero ello no tiene ningún efecto. Puede enlazar una base de datos a un grupo de recursos de servidor con nombre si en el futuro desea crear tablas optimizadas para memoria en la base de datos.

Para poder enlazar una base de datos a un grupo de recursos de servidor, tanto la base de datos como el grupo de recursos de servidor deben existir. El enlace surte efecto la próxima vez que la base de datos pase a estar en línea. Consulte Database States para obtener más información.

Para obtener más información acerca de los grupos de recursos de servidor, vea Resource Governor Resource Pool.

Pasos para enlazar una base de datos a un grupo de recursos de servidor

Otro contenido de este tema

Crear la base de datos y el grupo de recursos de servidor

Puede crear la base de datos y el grupo de recursos de servidor en cualquier orden. Lo que importa es que ambos existan antes de enlazar la base de datos al grupo de recursos de servidor.

Creación de la base de datos

El siguiente Transact-SQL crea una base de datos denominada IMOLTP_DB que contendrá una o varias tablas optimizadas para memoria. La ruta de acceso <driveAndPath> debe existir antes de ejecutar este comando.

CREATE DATABASE IMOLTP_DB  
GO  
ALTER DATABASE IMOLTP_DB ADD FILEGROUP IMOLTP_DB_fg CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE IMOLTP_DB ADD FILE( NAME = 'IMOLTP_DB_fg' , FILENAME = 'c:\data\IMOLTP_DB_fg') TO FILEGROUP IMOLTP_DB_fg;  
GO

Determinar el valor mínimo de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT

Una vez que determine las necesidades de memoria para las tablas optimizadas para memoria, debe determinar qué porcentaje de memoria disponible se necesita y establecer los porcentajes de memoria en ese valor o uno superior.

Ejemplo:
En este ejemplo supondremos que en sus cálculos ha determinado que las tablas y los índices optimizados para memoria necesitan 16 GB de memoria. Suponga que tiene 32 GB de memoria asignada para su uso.

A primera vista, podría parecer que necesita establecer MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT en 50 (16 es el 50 % de 32). Sin embargo, ese valor no proporcionaría suficiente memoria a las tablas optimizadas para memoria. Si miramos la tabla siguiente (Porcentaje de memoria disponible para tablas e índices optimizados para memoria), vemos que si hay 32 GB de memoria asignada, solo el 80 % está disponible para tablas e índices optimizados para memoria. Por tanto, calculamos los porcentajes mínimo y máximo en función de la memoria disponible, no de la memoria asignada.

memoryNeedeed = 16
memoryCommitted = 32
availablePercent = 0.8
memoryAvailable = memoryCommitted * availablePercent
percentNeeded = memoryNeeded / memoryAvailable

Es decir, en números reales sería:
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625

Necesita al menos el 62,5 % de la memoria disponible para satisfacer el requisito de 16 GB de las tablas y los índices optimizados para memoria. Puesto que los valores de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT deben ser enteros, los estableceremos como mínimo en el 63 %.

Crear un grupo de recursos de servidor y configurar la memoria

A la hora de configurar memoria para las tablas optimizadas para memoria, el planeamiento de capacidad debe realizarse en función de MIN_MEMORY_PERCENT, no de MAX_MEMORY_PERCENT. Consulte ALTER RESOURCE POOL (Transact-SQL) para obtener información sobre MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT. Esto proporciona una disponibilidad de memoria más predecible en las tablas optimizadas para memoria, ya que MIN_MEMORY_PERCENT produce presión de memoria en otros grupos de recursos de servidor para asegurarse de que se respeta. Para asegurarse de que hay memoria disponible e impedir condiciones de memoria insuficiente, los valores de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT deben ser iguales. Vea la tabla Porcentaje de memoria disponible para tablas e índices optimizados para memoria de abajo para conocer el porcentaje de memoria disponible para las tablas optimizadas para memoria según la cantidad de memoria asignada.

Vea Prácticas recomendadas: usar OLTP en memoria en un entorno de máquinas virtuales para obtener más información sobre cómo trabajar en un entorno de máquinas virtuales.

El siguiente código de Transact-SQL crea un grupo de recursos denominado Pool_IMOLTP con la mitad de la memoria disponible para su uso. Una vez creado el grupo, hay que reconfigurar el Regulador de recursos para incluir Pool_IMOLTP.

-- set MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT to the same value  
CREATE RESOURCE POOL Pool_IMOLTP   
  WITH   
    ( MIN_MEMORY_PERCENT = 63,   
    MAX_MEMORY_PERCENT = 63 );  
GO  
  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

Enlazar la base de datos al grupo

Use la función del sistema sp_xtp_bind_db_resource_pool para enlazar la base de datos al grupo de recursos de servidor. La función utiliza dos parámetros: el nombre de la base de datos y el nombre del grupo de recursos de servidor.

El siguiente Transact-SQL define un enlace de la base de datos IMOLTP_DB al grupo de recursos Pool_IMOLTP. El enlace no surte efecto hasta que la base de datos pase a estar en línea.

EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP'  
GO

La función del sistema sp_xtp_bind_db_resource_pool usa dos parámetros de cadena: database_name y pool_name.

Confirmar el enlace

Confirme el enlace, teniendo en cuenta el identificador del grupo de recursos de servidor para IMOLTP_DB. No puede ser NULL.

SELECT d.database_id, d.name, d.resource_pool_id  
FROM sys.databases d  
GO

Activar el enlace

Debe poner la base de datos sin conexión y volver a ponerla en línea después de enlazarla al grupo de recursos de servidor para que el enlace surta efecto. Si la base de datos se enlazó a otro grupo diferente, esto quita la memoria asignada del grupo de recursos de servidor anterior y las asignaciones de memoria para la tabla y los índices optimizados para memoria provendrán ahora del grupo de recursos de servidor recién enlazado a la base de datos.

USE master  
GO  
  
ALTER DATABASE IMOLTP_DB SET OFFLINE  
GO  
ALTER DATABASE IMOLTP_DB SET ONLINE  
GO  
  
USE IMOLTP_DB  
GO

Ahora, la base de datos está enlazada al grupo de recursos de servidor.

Cambiar MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT en un grupo existente

Si agrega memoria adicional al servidor o si cambia la cantidad de memoria necesaria para las tablas optimizadas para memoria, puede ser necesario modificar el valor de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT. Los pasos siguientes muestran cómo modificar el valor de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT en un grupo de recursos de servidor. Vea la próxima sección para obtener información sobre qué valores se deben usar para MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT. Vea el tema Prácticas recomendadas: usar OLTP en memoria en un entorno de máquinas virtuales para obtener más información.

Utilice ALTER RESOURCE POOL para cambiar el valor de MIN_MEMORY_PERCENT y MAX_MEMORY_PERCENT.
Use ALTER RESOURCE GOVERNOR para reconfigurar el regulador de recursos con los nuevos valores.

Código de ejemplo

ALTER RESOURCE POOL Pool_IMOLTP  
WITH  
     ( MIN_MEMORY_PERCENT = 70,  
       MAX_MEMORY_PERCENT = 70 )   
GO  
  
-- reconfigure the Resource Governor  
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

Porcentaje de memoria disponible para tablas e índices optimizados para memoria

Si asigna una base de datos con tablas optimizadas para memoria y una carga de trabajo de SQL Server al mismo grupo de recursos de servidor, el regulador de recursos establece un umbral interno para uso de OLTP en memoria de modo que los usuarios del grupo no experimenten conflictos al usar el grupo. En general, el umbral para el uso de OLTP en memoria es aproximadamente el 80 % del valor del grupo. En la tabla siguiente se muestran los umbrales reales para diversos tamaños de memoria.

Al crear un grupo de recursos de servidor dedicado para la base de datos de OLTP en memoria , debe evaluar cuánta memoria física necesita para las tablas en memoria después de tener en cuenta las versiones de filas y el aumento de datos. Una vez que se calcula la memoria necesaria, se crea un grupo de recursos con un porcentaje de la memoria de destino de confirmación para la instancia de SQL, tal como se refleja en la columna "committed_target_kb" de la DMV sys.dm_os_sys_info. Por ejemplo, puede crear un grupo de recursos de servidor P1 con el 40 % de la memoria total disponible para la instancia. Fuera de este 40 %, el motor de OLTP en memoria obtiene un porcentaje inferior para almacenar los datos de OLTP en memoria . Esto se hace para asegurarse de que OLTP en memoria no usa toda la memoria de este grupo. Este valor del porcentaje menor depende de la memoria confirmada de destino. En la siguiente tabla se describe la memoria disponible para la base de datos de OLTP en memoria en un grupo de recursos de servidor (designado o predeterminado) antes de que se genere un error de OOM.

Memoria asignada de destino	Porcentaje disponible para tablas en memoria
<= 8 GB	70%
<= 16 GB	75 %
<= 32 GB	80 %
<= 96 GB	85%
>96 GB	90%

Por ejemplo, si la "memoria confirmada de destino" es de 100 GB y calcula que las tablas e índices optimizados para memoria necesitan 60 GB de memoria, puede crear un grupo de recursos de servidor con MAX_MEMORY_PERCENT = 67 (60 GB necesarios / 0,90 = 66,667 GB - redondear hasta 67 GB; 67 GB / 100 GB instalados = 67 %) para garantizar que los objetos de OLTP en memoria tengan los 60 GB que necesitan.

Una vez que una base de datos se ha enlazado a un grupo de recursos de servidor con nombre, utilice la consulta siguiente para ver las asignaciones de memoria en distintos grupos de recursos de servidor.

SELECT pool_id  
     , Name  
     , min_memory_percent  
     , max_memory_percent  
     , max_memory_kb/1024 AS max_memory_mb  
     , used_memory_kb/1024 AS used_memory_mb   
     , target_memory_kb/1024 AS target_memory_mb  
   FROM sys.dm_resource_governor_resource_pools

Esta salida de ejemplo muestra que la memoria usada por los objetos optimizados para memoria es de 1356 MB en el grupo de recursos de servidor, PoolIMOLTP, con un límite superior de 2307 MB. Este límite superior controla la memoria total que el usuario puede emplear y los objetos del sistema optimizados para memoria asignados a este grupo.

Salida de ejemplo
Esta salida es de la base de datos y las tablas que hemos creado antes.

pool_id     Name        min_memory_percent max_memory_percent max_memory_mb used_memory_mb target_memory_mb  
----------- ----------- ------------------ ------------------ ------------- -------------- ----------------   
1           internal    0                  100                3845          125            3845  
2           default     0                  100                3845          32             3845  
259         Pool_IMOLTP 0                  100                3845          1356           2307

Para más información, consulte sys.dm_resource_governor_resource_pools (Transact-SQL).

Si no enlaza la base de datos a un grupo de recursos de servidor con nombre, se enlaza al grupo "default". Puesto que SQL Server usa el grupo de recursos de servidor predeterminado para la mayoría de las demás asignaciones, no podrá supervisar con precisión la memoria usada por las tablas optimizadas para memoria mediante la DMV sys.dm_resource_governor_resource_pools para la base de datos de interés.

Consulte también

sys.sp_xtp_bind_db_resource_pool (Transact-SQL)
sys.sp_xtp_unbind_db_resource_pool (Transact-SQL)
Regulador de recursos
Grupo de recursos de servidor del regulador de recursos
Crear un grupo de recursos de servidor
Cambiar la configuración del grupo de recursos de servidor
Eliminar un grupo de recursos de servidor