sys.dm_column_store_object_pool (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

Returns counts of different types of object memory pool usage for columnstore index objects.

Column name Data type Description
database_id int ID of the database. This is unique within an instance of a SQL Server database or an Azure SQL database server.
object_id int ID of the object. The object is one of the object_types.
index_id int ID of the columnstore index.
partition_number bigint 1-based partition number within the index or heap. Every table or view has at least one partition.
column_id int ID of the columnstore column. This is NULL for DELETE_BITMAP.
row_group_id int ID of the rowgroup.
object_type smallint 1 = COLUMN_SEGMENT

2 = COLUMN_SEGMENT_PRIMARY_DICTIONARY

3 = COLUMN_SEGMENT_SECONDARY_DICTIONARY

4 = COLUMN_SEGMENT_BULKINSERT_DICTIONARY

5 = COLUMN_SEGMENT_DELETE_BITMAP
object_type_desc nvarchar(60) COLUMN_SEGMENT - A column segment. object_id is the segment ID. A segment stores all the values for one column within one rowgroup. For example, if a table has 10 columns, there are 10 column segments per rowgroup.

COLUMN_SEGMENT_PRIMARY_DICTIONARY - A global dictionary that contains lookup information for all of the column segments in the table.

COLUMN_SEGMENT_SECONDARY_DICTIONARY - A local dictionary associated with one column.

COLUMN_SEGMENT_BULKINSERT_DICTIONARY - Another representation of the global dictionary. This provides an inverse look up of value to dictionary_id. Used for creating compressed segments as part of Tuple Mover or Bulk Load.

COLUMN_SEGMENT_DELETE_BITMAP - A bitmap that tracks segment deletes. There is one delete bitmap per partition.
access_count int Number of read or write accesses to this object.
memory_used_in_bytes bigint Memory used by this object in the object pool.
object_load_time datetime Clock-time for when object_id was brought into the object pool.

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 DATABASE PERFORMANCE STATE permission on the database.

See also

Index Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)
sys.indexes (Transact-SQL)
sys.objects (Transact-SQL)
Monitor and Tune for Performance
Columnstore indexes: Overview