Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todaysys.dm_db_column_store_row_group_operational_stats (Transact-SQL)
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
Documentation uses the term B-tree generally in reference to indexes. In rowstore indexes, the Database Engine implements a B+ tree. This does not apply to columnstore indexes or indexes on memory-optimized tables. For more information, see the SQL Server and Azure SQL index architecture and design guide.
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).
Requires VIEW DATABASE PERFORMANCE STATE permission on the database.
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)