Edit

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

  • Article

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

Returns current row-level I/O, locking, and access method activity for compressed rowgroups in a columnstore index. Use sys.dm_db_column_store_row_group_operational_stats to track the length of time a user query must wait to read or write to a compressed rowgroup or partition of a columnstore index, and identify rowgroups that are encountering significant I/O activity or hot spots.

In-memory columnstore indexes do not appear in this DMV.

Column name Data type Description
object_id int ID of the table with the columnstore index.
index_id int ID of the columnstore index.
partition_number int 1-based partition number within the index or heap.
row_group_id int ID of the rowgroup in the columnstore index. This is unique within a partition.
scan_count int Number of scans through the rowgroup since the last SQL restart.
delete_buffer_scan_count int Number of times the delete buffer was used to determine deleted rows in this rowgroup. This includes accessing the in-memory hashtable and the underlying B-tree.
index_scan_count int Number of times the columnstore index partition was scanned. This is the same for all rowgroups in the partition.
rowgroup_lock_count bigint Cumulative count of lock requests for this rowgroup since the last SQL restart.
rowgroup_lock_wait_count bigint Cumulative number of times the database engine waited on this rowgroup lock since the last SQL restart.
rowgroup_lock_wait_in_ms bigint Cumulative number of milliseconds the database engine waited on this rowgroup lock since the last SQL restart.

Note

SQL Server documentation uses the term B-tree generally in reference to indexes. In rowstore indexes, SQL Server implements a B+ tree. This does not apply to columnstore indexes or in-memory data stores. For more information, see the SQL Server and Azure SQL index architecture and design guide.

Permissions

Requires the following permissions:

  • CONTROL permission on the table specified by object_id.

  • VIEW DATABASE STATE permission to return information about all objects within the database, by using the object wildcard @object_id = NULL

Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

For more information, see Dynamic Management Views and Functions (Transact-SQL).

Permissions for SQL Server 2022 and later

Requires VIEW DATABASE PERFORMANCE STATE permission on the database.

See Also

Dynamic Management Views and Functions (Transact-SQL)
Index Related Dynamic Management Views and Functions (Transact-SQL)
Monitor and Tune for Performance
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)