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

本主题适用于:yesSQL Server(从 2008 开始)yesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel 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 DescriptionDescription
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 2012SQL Server 2012SQL Server 2017SQL Server 2017Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

此内存对象实例分配的内存量(以字节为单位)。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.
typetype 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 varchar (128)varchar(128) 仅限内部使用。Internal use only. 可以为 NULL。Nullable.
memory_node_idmemory_node_id intsmallint 该内存对象所用的内存节点的 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 2012SQL Server 2012SQL Server 2017SQL Server 2017Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

由该对象分配的页的大小(以字节为单位)。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. 有关详细信息,请参阅sys.dm_os_memory_clerks (Transact SQL ).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 分区的类型: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 使用 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 此内存对象等待数。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 指定以独占方式访问此内存对象的频率。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 SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseApplies to: Azure SQL 数据仓库Azure SQL Data Warehouse, 并行数据仓库Parallel Data Warehouse

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

partition_typecontention_factorwaiting_tasks_count,和exclusive_access_count尚未实现在 SQL DatabaseSQL Databasepartition_type, contention_factor, waiting_tasks_count, and exclusive_access_count are not yet implemented in SQL DatabaseSQL Database.

PermissionsPermissions

SQL ServerSQL Server,需要VIEW SERVER STATE权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
SQL DatabaseSQL Database高级层,需要VIEW DATABASE STATE数据库中的权限。On SQL DatabaseSQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. SQL DatabaseSQL Database标准版和基本层,需要服务器管理员Azure Active Directory 管理员帐户。On SQL DatabaseSQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

注释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.

为内存对象的典型的页大小为 8KB。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

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