通过

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

  • 项目

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例

返回当前数据库中每个内存中 OLTP 表（用户和系统）的内存使用情况统计信息。 系统表具有负对象 ID，用于存储内存中 OLTP 引擎的运行时信息。 与用户对象不同，系统表是内部的，并且只存在于内存中，因此，通过目录视图看不到它们。 系统表用于存储存储中所有数据/增量文件的元数据、合并请求、增量文件的水印来筛选行、删除的表以及恢复和备份的相关信息。 鉴于内存中 OLTP 引擎最多可以有 8,192 个数据和增量文件对，对于大型内存中数据库，系统表占用的内存可能为几兆字节。

有关详细信息，请参阅 内存中 OLTP（内存中优化）。

列名称 数据类型 说明
object_id int 表的对象 ID。 NULL 用于内存中 OLTP 系统表。
memory_allocated_for_table_kb bigint 为此表分配的内存。
memory_used_by_table_kb bigint 表使用的内存，包括行版本。
memory_allocated_for_indexes_kb bigint 为此表中的索引分配的内存。
memory_used_by_indexes_kb bigint 此表中的索引占用的内存。

权限

如果您对当前数据库拥有 VIEW DATABASE STATE 权限，将返回所有行。 否则，将返回一个空行集。

如果您没有 VIEW DATABASE 权限，将为表中您拥有 SELECT 权限的行返回所有列。

只会为拥有 VIEW DATABASE STATE 权限的用户返回系统表。

SQL Server 2022 及更高版本的权限

需要对数据库拥有 VIEW DATABASE PERFORMANCE STATE 权限。

示例

您可以查询以下 DMV 以获取在数据库内为表和索引分配的内存：

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

在数据库中查找所有对象的内存：

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;

使用方案

首先，将最大服务器内存设置为 4GB 作为安全措施。 你可能希望为环境考虑不同的值。

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

为包含内存优化对象的数据库创建资源池。

-- 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

将资源池“PoolHkdb1”绑定到数据库“HkDb1”。 这需要使数据库脱机/联机才能关联池。

--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

在名为 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

将数据加载到表中。

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

将数据加载到表中后，可看到用户定义的表及其使用的存储容量。 例如，表的每行可能大约为 8070 个字节（分配大小为 8K（8192 个字节））。 可查看每个表的索引数以及这些索引使用的存储容量。 例如，1MB 为每个 100K 条目舍入到 2 的下一次幂 (2**17) = 131072，每个为 8 字节。 表可以没有某个索引，在这种情况下，它将显示该索引的内存分配。 其他行可代表系统表

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

下面是输出，分为两部分：

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

以下部分

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;

是：

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

接下来，让我们看一看来自资源池的输出。 请注意，池中使用的内存为 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;

输出：

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

相关内容