sys.dm_db_xtp_table_memory_stats (Transact-SQL)

  • Artículo

Se aplica a: SQL ServerAzure SQL Database Azure SQL Instancia administrada

Devuelve estadísticas de uso de memoria para cada tabla OLTP en memoria (usuario y sistema) de la base de datos actual. Las tablas del sistema tienen identificadores de objeto negativos y se usan para almacenar información en tiempo de ejecución para el motor OLTP en memoria. A diferencia de los objetos de usuario, las tablas del sistema son internas y solo existen en memoria; por tanto, no son visible mediante vistas de catálogo. Las tablas del sistema se usan para almacenar información como metadatos para todos los archivos de datos o delta en el almacenamiento, combinar solicitudes, marcas de agua para archivos delta para filtrar filas, tablas quitadas e información relevante para la recuperación y las copias de seguridad. Dado que el motor OLTP en memoria puede tener hasta 8192 pares de archivos delta y de datos, para bases de datos grandes en memoria, la memoria tomada por las tablas del sistema puede ser unos pocos megabytes.

Para obtener más información, vea OLTP en memoria (optimización en memoria).

Nombre de la columna Tipo de datos Descripción
object_id int Identificador de objeto de la tabla. NULL para las tablas del sistema OLTP en memoria.
memory_allocated_for_table_kb bigint La memoria asignada para esta tabla.
memory_used_by_table_kb bigint Memoria utilizada por la tabla, incluidas las versiones de fila.
memory_allocated_for_indexes_kb bigint La memoria asignada para los índices en esta tabla.
memory_used_by_indexes_kb bigint La memoria usada por los índices en esta tabla.

Permisos

Se devuelven todas las filas si tiene el permiso VIEW DATABASE STATE en la base de datos actual. De lo contrario, se devuelve un conjunto de filas vacío.

Si no tiene permiso DATABASE STATE, todas las columnas se devolverán para las filas en las tablas en las que tenga el permiso SELECT.

Las tablas del sistema solo se devuelven para los usuarios con el permiso VIEW DATABASE STATE.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VIEW DATABASE PERFORMANCE STATE en la base de datos.

Ejemplos

Puede consultar la DMV siguiente para obtener la memoria asignada para las tablas y los índices en la base de datos:

-- finding memory for objects  
SELECT OBJECT_NAME(object_id), *   
FROM sys.dm_db_xtp_table_memory_stats;

Para buscar memoria para todos los objetos dentro de la base de datos:

SELECT SUM( memory_allocated_for_indexes_kb + memory_allocated_for_table_kb) AS  
 memoryallocated_objects_in_kb   
FROM sys.dm_db_xtp_table_memory_stats;

Escenario de usuario

En primer lugar, establezca la memoria máxima del servidor en 4 GB como medida de seguridad. Es posible que quiera considerar un valor diferente para su entorno.

-- set max server memory to 4 GB  
EXEC sp_configure 'max server memory (MB)', 4048  
go  
  
RECONFIGURE  
go

Cree un grupo de recursos para la base de datos que contenga los objetos optimizados para memoria.

-- create a resource pool for the database with memory-optimized objects  
CREATE RESOURCE POOL PoolHkDb1 WITH (MAX_MEMORY_PERCENT = 50);  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
go

Enlace el grupo de recursos "PoolHkdb1" a la base de datos "HkDb1". Esto requiere desconectar o conectar la base de datos para asociar el grupo.

--bind the pool to the database  
EXEC sp_xtp_bind_db_resource_pool 'HkDb1', 'PoolHkdb1'  
go  
  
-- take database offline/online to associate the pool  
use master  
go  
  
alter database HkDb1 set offline  
go  
alter database HkDb1 set online  
go

Cree las tablas siguientes en una base de datos denominada HkDb1.

USE HkDb1  
GO
  
CREATE TABLE dbo.t1 (  
       c1 int NOT NULL,  
       c2 char(40) NOT NULL,  
       c3 char(8000) NOT NULL,  
  
       CONSTRAINT [pk_t1_c1] PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 100000)  
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
  
CREATE TABLE dbo.t2 (  
       c1 int NOT NULL,  
       c2 char(40) NOT NULL,  
       c3 char(8000) NOT NULL,  
  
       CONSTRAINT [pk_t2_c1] PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 100000)  
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO  
  
CREATE TABLE dbo.t3 (  
       c1 int NOT NULL,  
       c2 char(40) NOT NULL,  
       c3 char(8000) NOT NULL,  
  
       CONSTRAINT [pk_t3_c1] PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 1000000)  
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
GO

Cargue los datos en la tabla.

-- load 150K rows  
DECLARE @i int = 0  
WHILE (@i <= 150000)  
BEGIN  
       insert t1 values (@i, 'a', replicate ('b', 8000))  
       set @i += 1;  
END  
GO

Cuando los datos se cargan en una tabla, puede ver las tablas definidas por el usuario y cuánto almacenamiento utilizan. Por ejemplo, cada fila de una tabla puede ser de aproximadamente 8070 bytes (el tamaño de asignación es de 8 K (8192 bytes)). Puede ver los índices por tabla y cuánto almacenamiento usa el índice. Por ejemplo, 1 MB son 100K entradas redondeadas a la siguiente potencia de 2 (2**17) = 131072 de 8 bytes cada uno. Una tabla puede no tener un índice, en cuyo caso se muestra la asignación de memoria para el índice. Otras filas pueden representar las tablas del sistema

select convert(char(10), object_name(object_id)) as Name,*   
from sys.dm_db_xtp_table_memory_stats;

Estos son los resultados, en dos partes:

Name       object_id   memory_allocated_for_table_kb memory_used_by_table_kb  
---------- ----------- ----------------------------- -----------------------  
t3         629577281   0                             0  
t1         565577053   1372928                       1202351  
t2         597577167   0                             0  
NULL       -6          0                             0  
NULL       -5          0                             0  
NULL       -4          0                             0  
NULL       -3          0                             0  
NULL       -2          192                           25  
  
memory_allocated_for_indexes_kb memory_used_by_indexes_kb  
------------------------------- -------------------------  
8192                            8192  
1024                            1024  
8192                            8192  
2                               2  
24                              24  
2                               2  
2                               2  
16                              16

El resultado de,

select  sum(allocated_bytes)/(1024*1024) as total_allocated_MB,   
       sum(used_bytes)/(1024*1024) as total_used_MB  
from sys.dm_db_xtp_memory_consumers;

es:

total_allocated_MB   total_used_MB  
-------------------- --------------------  
1357                 1191

A continuación, veamos los resultados del grupo de recursos de servidor. Tenga en cuenta que la memoria usada en el grupo es de 1356 MB.

select pool_id,convert(char(10), name) as Name, min_memory_percent, max_memory_percent,   
   max_memory_kb/1024 as max_memory_mb  
from sys.dm_resource_governor_resource_pools; 
  
select used_memory_kb/1024 as used_memory_mb ,target_memory_kb/1024 as target_memory_mb  
from sys.dm_resource_governor_resource_pools;

Salida:

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

Contenido relacionado