Edit

sys.dm_os_memory_pools (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Returns a row for each object store in the instance of SQL Server. You can use this view to monitor cache memory use and to identify bad caching behavior

Note

To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_os_memory_pools. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Column name Data type Description
memory_pool_address varbinary(8) Memory address of the entry that represents the memory pool. Is not nullable.
pool_id int ID of a specific pool within a set of pools. Is not nullable.
type nvarchar(60) Type of object pool. Is not nullable. For more information, see sys.dm_os_memory_clerks (Transact-SQL).
name nvarchar(256) System-assigned name of this memory object. Is not nullable.
max_free_entries_count bigint Maximum number of free entries that a pool can have. Is not nullable.
free_entries_count bigint Number of free entries currently in the pool. Is not nullable.
removed_in_all_rounds_count bigint Number of entries removed from the pool since the instance of SQL Server was started. Is not nullable.
pdw_node_id int Applies to: Azure Synapse Analytics, Analytics Platform System (PDW)

The identifier for the node that this distribution is on.

Permissions

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Remarks

SQL Server components sometimes use a common pool framework to cache homogeneous, stateless types of data. The pool framework is simpler than cache framework. All entries in the pools are considered equal. Internally, pools are memory clerks and can be used in places where memory clerks are used.

See also

SQL Server Operating System Related Dynamic Management Views (Transact-SQL)