Clustered Columnstore Index Catalog Views Split By Partition

If you are using partitions with the new Clustered Columnstore Indexes then it’s helpful to know the number of dictionaries/segments each partition has. This is because partitions have their dictionaries and segments isolated so that switching of partition is still kept as a meta data task only. Below are two queries for checking the catalog views for both dictionaries and segments and grouping them by partition


SELECT i.name, p.object_id, p.index_id, i.type_desc, p.partition_number,
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 = 5
GROUP BY i.name, p.object_id, p.index_id, i.type_desc, partition_number ;
GO

SELECT i.name, p.object_id, p.index_id, i.type_desc, p.partition_number,
COUNT(*) AS number_of_dictionaries
FROM sys.column_store_dictionaries 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 = 5
GROUP BY i.name, p.object_id, p.index_id, i.type_desc, p.partition_number
GO

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s