संपादन करना

के माध्यम से साझा करें

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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 don't appear in this DMV.

Column name Data type Nullable Description
object_id int No ID of the table with the columnstore index.
index_id int No ID of the columnstore index.
partition_number int No 1-based partition number within the index or heap.
row_group_id int No ID of the rowgroup in the columnstore index. This is unique within a partition.
index_scan_count bigint No Number of times the columnstore index partition was scanned. This is the same for all rowgroups in the partition.
scan_count bigint No Number of scans through the rowgroup since the last SQL restart.
delete_buffer_scan_count bigint No 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.
row_group_lock_count bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
row_group_lock_wait_count bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
row_group_lock_wait_in_ms bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
returned_row_count bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
returned_aggregate_count bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
returned_group_count bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
input_groupby_row_count bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
row_group_elimination_count bigint No Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
rowgroup_lock_count bigint N/A Cumulative count of lock requests for this rowgroup since the last SQL Server restart.
rowgroup_lock_wait_count bigint N/A Cumulative number of times the database engine waited on this rowgroup lock since the last SQL Server restart.
rowgroup_lock_wait_in_ms bigint N/A Cumulative number of milliseconds the database engine waited on this rowgroup lock since the last SQL Server 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.

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.

  • In SQL Server 2019 (15.x) and earlier versions, requires VIEW DATABASE STATE permission to return information about all objects within the database, by using the object wildcard @object_id = NULL.

  • In SQL Server 2022 (16.x) and later versions, requires VIEW DATABASE PERFORMANCE STATE permission on the database.

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

Denying VIEW DATABASE [PERFORMANCE] 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 System dynamic management views.

Related content

  • Last updated on