As mentioned previously in the “Index Storage” section, principal storage for metadata concerning columnstore indices is called a directory The metadata includes information about the allocation status of segments and dictionaries. The directory also contains additional metadata about the number of rows, size, and minimum and maximum values inside each of the segments.
If you want to display this information, you can use the following dynamic management views:
- column_store_segments
- column_store_row_groups
- column_store_dictionaries
The following subsections describe these DMVs.
1. sys.column_store_segments
The sys.column_store_segments view contains a row for each column in a columnstore index. Example 27.4 displays information about segments of existing columnstore indices.
Example 27.4
SELECT i.name, p.object_id, p.index_id, i.type_desc,
COUNT(*) AS number_of_segments
FROM sys.column_store_segments AS s
INNER JOIN sys.partitions AS p
ON s.hobt_id = p.hobt_id
INNER JOIN sys.indexes AS i
ON p.object_id = i.object_id
WHERE i.type = 6
GROUP BY i.name, p.object_id, p.index_id, i.type_desc ;
The result of Example 27.4 is
The SELECT statement in Example 27.4 displays the name of the index, its ID, and the index type (NONCLUSTERED COLUMNSTORE). The last column displays the number of assigned segments.
2. sys.column_store_row_groups
The sys.column_store_row_groups view provides metadata information in relation to a clustered columnstore index on a per-segment basis. 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. Use sys.column_store_row_groups to determine which segments have a high percentage of deleted rows and should be rebuilt. Example 27.5 shows the use of this DMV.
Example 27.5
USE sample;
SELECT i.object_id, o.name,
i.name AS IndexName, i.index_id, i.type_desc
FROM sys.indexes AS i
JOIN sys.column_store_row_groups AS row_group
ON i.object_id = row_group.object_id
JOIN sys.objects o ON i.object_id = o.object_id
AND i.index_id = row_group.index_id;
The result is
The query in Example 27.5 joins three tables, sys.column_store_row_groups, sys.indexes, and sys.objects, to display the name of the table and names and IDs of associated columnstore indices. The last column of the output, type_desc, displays the type of the associated index.
3. sys.column_store_dictionaries
The sys.column_store_dictionaries DMV contains a row for each dictionary used in columnstore indices. Dictionaries are used to encode some, but not all, data types; therefore, not all columns in a columnstore index have dictionaries.
The most important columns of the DMV are hobt_id, column_id, and dictionary_id. The hobt_id column value is the unique identifier of the B-tree index (hobt) for the table that has the corresponding columnstore index. The column_id column value is the ID of the columnstore column, starting with 1. The first column has ID = 1, the second column has ID = 2, and so on. The dictionary_id column value specifies the ID of the corresponding dictionary. The value 0 represents the global dictionary that is shared across all column segments (one for each row group) for that column. The value <> 0 specifies a local dictionary.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.