sys.column_store_row_groups (Transact-SQL)sys.column_store_row_groups (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

在各个段的基础上提供聚集列存储索引信息,以便帮助管理员作出系统管理决定。Provides clustered columnstore index information on a per-segment basis to help the administrator make system management decisions. sys.column_store_row_groups包含列 (包括那些已标记为已删除) 以物理方式存储的行的总数和标记为已删除的行数的列。sys.column_store_row_groups has a column for the total number of rows physically stored (including those marked as deleted) and a column for the number of rows marked as deleted. 使用sys.column_store_row_groups来确定哪些行组具有较高百分比的已删除的行和应重新生成。Use sys.column_store_row_groups to determine which row groups have a high percentage of deleted rows and should be rebuilt.

列名Column name 数据类型Data type 描述Description
object_idobject_id intint 对其定义此索引的表的 ID。The id of the table on which this index is defined.
index_idindex_id intint 具有此列存储索引的表的索引 ID。ID of the index for the table that has this columnstore index.
partition_numberpartition_number intint 保留行组 row_group_id 的表分区的 ID。ID of the table partition that holds row group row_group_id. 您可以使用 partition_number 将此 DMV 联接到 sys.partitions。You can use partition_number to join this DMV to sys.partitions.
row_group_idrow_group_id intint 与此行组关联的行组编号。The row group number associated with this row group. 这在分区中是唯一的。This is unique within the partition.

-1 = 内存中表的结尾。-1 = tail of an in-memory table.
delta_store_hobt_iddelta_store_hobt_id bigintbigint 增量存储区中打开的行组的 hobt_id。The hobt_id for OPEN row group in the delta store.

如果在增量存储行组不为 NULL。NULL if the row group is not in the delta store.

对于内存中表的结尾为 NULL。NULL for the tail of an in-memory table.
Statestate tinyinttinyint 与 state_description 关联的 ID 号。ID number associated with the state_description.

0 = INVISIBLE0 = INVISIBLE

1 = OPEN1 = OPEN

2 = CLOSED2 = CLOSED

3 = COMPRESSED3 = COMPRESSED

4 = 逻辑删除4 = TOMBSTONE
state_descriptionstate_description nvarchar(60)nvarchar(60) 行组的持久状态的说明:Description of the persistent state of the row group:

从增量存储中的数据进行生成过程中不可见的隐藏压缩的段。INVISIBLE -A hidden compressed segment in the process of being built from data in a delta store. 读操作将使用增量存储区,直至完成不可见的压缩段。Read actions will use the delta store until the invisible compressed segment is completed. 然后,新段变为可见,并删除源增量存储区。Then the new segment is made visible, and the source delta store is removed.

打开-正在接受新记录的读/写行组。OPEN - A read/write row group that is accepting new records. 开放的行组仍采用行存储格式,并且尚未压缩成列存储格式。An open row group is still in rowstore format and has not been compressed to columnstore format.

已关闭-已填充,但尚未由元组搬运者进程压缩的行组。CLOSED - A row group that has been filled, but not yet compressed by the tuple mover process.

压缩的已填充和压缩的行组。COMPRESSED - A row group that has filled and compressed.
total_rowstotal_rows bigintbigint 行组中物理存储的总行数。Total rows physically stored in the row group. 一些行可能已删除,但它们仍被存储。Some may have been deleted but they are still stored. 一个行组中的最大行数为 1,048,576(十六进制 FFFFF)。The maximum number of rows in a row group is 1,048,576 (hexadecimal FFFFF).
deleted_rowsdeleted_rows bigintbigint 行组中标记为已删除的总行数。Total rows in the row group marked deleted. 对于 DELTA 行组,此值始终为 0。This is always 0 for DELTA row groups.
size_in_bytessize_in_bytes bigintbigint 对于 DELTA 和 COLUMNSTORE 行组,指的是此行组中所有数据的大小(不包括元数据和共享字典),以字节为单位。Size in bytes of all the data in this row group (not including metadata or shared dictionaries), for both DELTA and COLUMNSTORE rowgroups.

备注Remarks

针对每个表中具有聚合或非聚合列存储索引的每个列存储行组返回一行。Returns one row for each columnstore row group for each table having a clustered or nonclustered columnstore index.

使用sys.column_store_row_groups以确定包含在行组和行组的大小的行数。Use sys.column_store_row_groups to determine the number of rows included in the row group and the size of the row group.

当行组中的已删除行数量增长到占总行数的较大百分比时,该表的效率将下降。When the number of deleted rows in a row group grows to a large percentage of the total rows, the table becomes less efficient. 重新生成列存储索引以减少表的大小,同时减少读取该表所需的磁盘 I/O。Rebuild the columnstore index to reduce the size of the table, reducing the disk I/O required to read the table. 若要重新生成列存储索引使用重新生成的选项ALTER INDEX语句。To rebuild the columnstore index use the REBUILD option of the ALTER INDEX statement.

可更新列存储首先将新数据插入打开行组,这是采用行存储格式,有时也称为 delta 表。The updateable columnstore first inserts new data into an OPEN rowgroup, which is in rowstore format, and is also sometimes referred to as a delta table. 一旦打开的行组已满,其状态更改为已关闭Once an open rowgroup is full, its state changes to CLOSED. 已关闭行组将由元组搬运者进程压缩为列存储格式和状态将变为压缩A closed rowgroup is compressed into columnstore format by the tuple mover and the state changes to COMPRESSED. 元组搬运者是一个后台进程,它定期唤醒并检查是否有任何关闭的行组正准备要压缩成列存储行组。The tuple mover is a background process that periodically wakes up and checks whether there are any closed rowgroups that are ready to compress into a columnstore rowgroup. 元组搬运者还取消分配其中已删除每个行的行组。The tuple mover also deallocates any rowgroups in which every row has been deleted. 已解除分配的行组标记为逻辑删除Deallocated rowgroups are marked as TOMBSTONE. 若要立即运行元组搬运者进程,请使用REORGANIZE的选项ALTER INDEX语句。To run tuple mover immediately, use the REORGANIZE option of the ALTER INDEX statement.

如果列存储行组已填充,它将进行压缩并停止接受新行。When a columnstore row group has filled, it is compressed, and stops accepting new rows. 当从压缩组中删除行时,这些行将保留但标记为已删除。When rows are deleted from a compressed group, they remain but are marked as deleted. 对压缩组的更新将实现为压缩组中的删除以及对打开组的插入。Updates to a compressed group are implemented as a delete from the compressed group, and an insert to an open group.

权限Permissions

返回表的信息,如果用户具有VIEW DEFINITION表的权限。Returns information for a table if the user has VIEW DEFINITION permission on the table.

目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

示例Examples

下面的示例联接sys.column_store_row_groups表与其他系统表,以返回有关特定表的信息。The following example joins the sys.column_store_row_groups table to other system tables to return information about specific tables. 计算的 PercentFull 列是对行组效率的估计。The calculated PercentFull column is an estimate of the efficiency of the row group. 若要查找有关单个表删除的注释连字符的前面其中子句,并提供表名称。To find information on a single table remove the comment hyphens in front of the WHERE clause and provide a table name.

SELECT i.object_id, object_name(i.object_id) AS TableName,   
i.name AS IndexName, i.index_id, i.type_desc,   
CSRowGroups.*,   
100*(total_rows - ISNULL(deleted_rows,0))/total_rows AS PercentFull    
FROM sys.indexes AS i  
JOIN sys.column_store_row_groups AS CSRowGroups  
    ON i.object_id = CSRowGroups.object_id  
AND i.index_id = CSRowGroups.index_id   
--WHERE object_name(i.object_id) = '<table_name>'   
ORDER BY object_name(i.object_id), i.name, row_group_id;  

请参阅See Also

对象目录视图 (Transact-SQL) Object Catalog Views (Transact-SQL)
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
查询 SQL Server 系统目录常见问题 Querying the SQL Server System Catalog FAQ
sys.columns (Transact-SQL) sys.columns (Transact-SQL)
sys.all_columns (TRANSACT-SQL) sys.all_columns (Transact-SQL)
sys.computed_columns (TRANSACT-SQL) sys.computed_columns (Transact-SQL)
列存储索引指南 Columnstore Indexes Guide
sys.column_store_dictionaries (TRANSACT-SQL) sys.column_store_dictionaries (Transact-SQL)
sys.column_store_segments (Transact-SQL)sys.column_store_segments (Transact-SQL)