Exercise 3: Exploring columnstore index metadata and statistics

In this exercise, you will look at the various catalog views available to examine columnstore index metadata and statistics.

At the end of this exercise, you will understand how to:

1. Retrieve information on columnstore row counts, page count, disk size and segments
2. Retrieve information regarding columns covered in the columnstore index.

Task 1 – Viewing the Columnstore Index Definition Information

1. Copy and paste the following SQL code into the New Query area of the SQL Management and press F5 in order to view the columns included within the columnstore index we created in an earlier exercise:

   SQL

   SELECT i.object_id, i.name, i.type, i.type_desc, c.index_column_id, c.is_included_column FROM sys.indexes as i INNER JOIN sys.index_columns as c ON i.index_id = c.index_id AND i.object_id = c.object_id WHERE name = 'IX_CS_FactProductInventory'

   This returns the following result set (notice the index column id showing which columns were included and notice that each column is marked as is_included_column):

   Figure 1

   Result Set

Task 2 – Exploring Columnstore Index Statistics

1. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to view a count of entries per column in the columnstore index and their associated on disk size (size in bytes):

   SQL

   SELECT hobt_id, column_id, entry_count, on_disk_size FROM sys.column_store_dictionaries

   This returns the following result set:

   Figure 2

   Count of entries in the Columnstore Index

2. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to view statistics on row count, encoding type and on disk size:

   SQL

   SELECT hobt_id, column_id, row_count, encoding_type, on_disk_size FROM sys.column_store_segments

   This returns the following result set:

   Figure 3

   Statistics Result Set