sys.dm_column_store_object_pool (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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




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.


On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

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