sys.dm_os_memory_objects (Transact-SQL)sys.dm_os_memory_objects (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

返回当前由 SQL ServerSQL Server 分配的内存对象。Returns memory objects that are currently allocated by SQL ServerSQL Server. 您可以使用 sys.dm_os_memory_objects 分析内存使用情况,并识别可能的内存泄漏。You can use sys.dm_os_memory_objects to analyze memory use and to identify possible memory leaks.

列名称Column name 数据类型Data type 说明Description
memory_object_addressmemory_object_address varbinary(8)varbinary(8) 内存对象的地址。Address of the memory object. 不可为 null。Is not nullable.
parent_addressparent_address varbinary(8)varbinary(8) 父内存对象的地址。Address of the parent memory object. 可以为 Null。Is nullable.
pages_allocated_countpages_allocated_count intint 适用范围SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2008 R2SQL Server 2008 R2.

由该对象分配的页数。Number of pages that are allocated by this object. 不可为 null。Is not nullable.
pages_in_bytespages_in_bytes bigintbigint 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

此内存对象实例分配的内存量(以字节为单位)。Amount of memory in bytes that is allocated by this instance of the memory object. 不可为 null。Is not nullable.
creation_optionscreation_options intint 仅限内部使用。Internal use only. 可以为 Null。Is nullable.
bytes_usedbytes_used bigintbigint 仅限内部使用。Internal use only. 可以为 Null。Is nullable.
type type nvarchar(60)nvarchar(60) 内存对象的类型。Type of memory object.

它指示该内存对象所属的特定组件,或指示内存对象的函数。This indicates some component that this memory object belongs to, or the function of the memory object. 可以为 Null。Is nullable.
name name varchar(128)varchar(128) 仅限内部使用。Internal use only. 可以为 NULL。Nullable.
memory_node_idmemory_node_id smallintsmallint 该内存对象所用的内存节点的 ID。ID of a memory node that is being used by this memory object. 不可为 null。Is not nullable.
creation_timecreation_time datetimedatetime 仅限内部使用。Internal use only. 可以为 Null。Is nullable.
max_pages_allocated_countmax_pages_allocated_count intint 适用范围SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2008 R2SQL Server 2008 R2.

由该内存对象分配的最大页数。Maximum number of pages allocated by this memory object. 不可为 null。Is not nullable.
page_size_in_bytespage_size_in_bytes intint 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

由该对象分配的页的大小(以字节为单位)。Size of pages in bytes allocated by this object. 不可为 null。Is not nullable.
max_pages_in_bytesmax_pages_in_bytes bigintbigint 该内存对象曾经使用的最大内存量。Maximum amount of memory ever used by this memory object. 不可为 null。Is not nullable.
page_allocator_addresspage_allocator_address varbinary(8)varbinary(8) 页分配器的内存地址。Memory address of page allocator. 不可为 null。Is not nullable. 有关详细信息,请参阅 (transact-sql)sys.dm_os_memory_clerks For more information, see sys.dm_os_memory_clerks (Transact-SQL).
creation_stack_addresscreation_stack_address varbinary(8)varbinary(8) 仅限内部使用。Internal use only. 可以为 Null。Is nullable.
sequence_numsequence_num intint 仅限内部使用。Internal use only. 可以为 Null。Is nullable.
partition_typepartition_type intint 适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

分区类型:The type of partition:

0-非分区内存对象0 - Non-partitionable memory object

1-分区内存对象,当前未分区1 - Partitionable memory object, currently not partitioned

2-分区内存对象,按 NUMA 节点进行分区。2 - Partitionable memory object, partitioned by NUMA node. 在具有单个 NUMA 节点的环境中,此项等效于1。In an environment with a single NUMA node this is equivalent to 1.

3-分区内存对象,按 CPU 分区。3 - Partitionable memory object, partitioned by CPU.
contention_factorcontention_factor realreal 适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

一个值,该值指定此内存对象的争用,0表示无争用。A value specifying contention on this memory object, with 0 meaning no contention. 每当指定数量的内存分配在该时间段内反射争用时,该值就会更新。The value is updated whenever a specified number of memory allocations were made reflecting contention during that period. 仅适用于线程安全内存对象。Applies only to thread-safe memory objects.
waiting_tasks_countwaiting_tasks_count bigintbigint 适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

此内存对象上的等待次数。Number of waits on this memory object. 从此内存对象分配内存时,此计数器将递增。This counter is incremented whenever memory is allocated from this memory object. 增量是当前等待访问此内存对象的任务数。The increment is the number of tasks currently waiting for access to this memory object. 仅适用于线程安全内存对象。Applies only to thread-safe memory objects. 这是最大努力,无需保证正确性。This is a best effort value without a correctness guarantee.
exclusive_access_countexclusive_access_count bigintbigint 适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

指定以独占方式访问此内存对象的频率。Specifies how often this memory object was accessed exclusively. 仅适用于线程安全内存对象。Applies only to thread-safe memory objects. 这是最大努力,无需保证正确性。This is a best effort value without a correctness guarantee.
pdw_node_idpdw_node_id intint 适用 于: Azure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data WarehouseApplies to: Azure Synapse AnalyticsAzure Synapse Analytics, 并行数据仓库Parallel Data Warehouse

此分发所在的节点的标识符。The identifier for the node that this distribution is on.

中尚未实现 partition_typecontention_factorwaiting_tasks_countexclusive_access_count SQL 数据库SQL Databasepartition_type, contention_factor, waiting_tasks_count, and exclusive_access_count are not yet implemented in SQL 数据库SQL Database.

权限Permissions

在上 SQL ServerSQL Server ,需要 VIEW SERVER STATE 权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
在 SQL 数据库的基本、S0 和 S1 服务目标上,对于弹性池中的数据库, 服务器管理员 帐户或 Azure Active Directory 管理员 帐户是必需的。On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. 对于所有其他 SQL 数据库服务目标, VIEW DATABASE STATE 数据库中需要该权限。On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

备注Remarks

内存对象是指多个堆。Memory objects are heaps. 它们所提供的分配的粒度比内存分配器所提供的分配的粒度更精细。They provide allocations that have a finer granularity than those provided by memory clerks. SQL ServerSQL Server 组件使用内存对象,而不使用内存分配器。components use memory objects instead of memory clerks. 内存对象使用内存分配器的页分配器接口来分配页。Memory objects use the page allocator interface of the memory clerk to allocate pages. 内存对象不使用虚拟内存接口或共享内存接口。Memory objects do not use virtual or shared memory interfaces. 根据分配模式的不同,组件可以创建不同的内存对象类型来分配任意大小的区域。Depending on the allocation patterns, components can create different types of memory objects to allocate regions of arbitrary size.

内存对象的典型页面大小为 8 KB。The typical page size for a memory object is 8 KB. 但是,增量内存对象的页大小的范围可以从 512 字节到 8 KB。However, incremental memory objects can have page sizes that range from 512 bytes to 8 KB.

备注

页大小不是最大分配。Page size is not a maximum allocation. 相反,页大小是受页分配器支持,并且由内存分配器实现的分配粒度。Instead, page size is allocation granularity that is supported by a page allocator and that is implemented by a memory clerk. 可从内存对象中请求大于 8 KB 的分配。You can request allocations greater than 8 KB from memory objects.

示例Examples

以下示例返回由每种内存对象类型分配的内存量。The following example returns the amount of memory allocated by each memory object type.

SELECT SUM (pages_in_bytes) as 'Bytes Used', type   
FROM sys.dm_os_memory_objects  
GROUP BY type   
ORDER BY 'Bytes Used' DESC;  
GO  

另请参阅See Also

(Transact-sql 的与操作系统相关的动态管理视图 SQL Server) SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
sys.dm_os_memory_clerks (Transact-sql)sys.dm_os_memory_clerks (Transact-SQL)