SQL Server: Editing Information Concerning Columnstore Indices

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.

Leave a Reply

Your email address will not be published. Required fields are marked *