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