Lesson Learned #340: Storage and Memory allocated by Columnstore Indexes
Published Mar 28 2023 11:29 AM 2,573 Views

Today, we got a new question how much is the size used by a columnstore index table at storage level and memory usage.

 

TSQL to obtain the total number of rows, size per schema, table and index.

Using the view sys.column_store_row_groups (Transact-SQL) - SQL Server | Microsoft Learn we could see the total number of rows and space usage at storage level.

 

 

 

SELECT sys.schemas.name, sys.tables.name, sys.indexes.name, 
 SUM(TOTAL_ROWS) as NumRows, SUM(SIZE_IN_BYTES) / 1024 / 1024 AS TotalSizeInMB
 from sys.column_store_row_groups
INNER JOIN sys.tables  on sys.tables.object_id = sys.column_store_row_groups.object_id
INNER JOIN sys.schemas  on sys.schemas.schema_id = sys.tables.schema_id
INNER JOIN sys.indexes ON sys.indexes.object_id = sys.column_store_row_groups.object_id AND sys.indexes.index_id = sys.column_store_row_groups.index_id
GROUP  BY sys.schemas.name, sys.tables.name, sys.indexes.name
ORDER  BY sys.schemas.name, sys.tables.name, sys.indexes.name

 

 

 

TSQL to obtain the space usage by ColumnStore Index dictionaries per schema, table_name, index name and column name.

Using the view sys.column_store_dictionaries (Transact-SQL) - SQL Server | Microsoft Learn we could see the space usage in the dictionary that column store index. 

 

 

 

select partitions.partition_number, sys.schemas.name, sys.tables.name, columns.name, sum(on_disk_size) / 1024 as TotalSizeInKB
from sys.column_store_dictionaries
inner join sys.partitions on column_store_dictionaries.hobt_id = partitions.hobt_id 
inner join sys.tables on tables.object_id = partitions.object_id 
inner join sys.columns on tables.object_id = columns.object_id and column_store_dictionaries.column_id=columns.column_id
INNER JOIN sys.schemas  on sys.schemas.schema_id = sys.tables.schema_id
group by sys.schemas.name, tables.name, partitions.partition_number, columns.name
order by sys.schemas.name, tables.name, partitions.partition_number, columns.name

 

 

 

TSQL to obtain the space usage by ColumnStore Index dictionaries per schema, table_name and index name.

 

 

 

select partitions.partition_number, sys.schemas.name, sys.tables.name, sum(on_disk_size) / 1024 as TotalSizeInKB
from sys.column_store_dictionaries
inner join sys.partitions on column_store_dictionaries.hobt_id = partitions.hobt_id 
inner join sys.tables on tables.object_id = partitions.object_id 
inner join sys.columns on tables.object_id = columns.object_id and column_store_dictionaries.column_id=columns.column_id
INNER JOIN sys.schemas  on sys.schemas.schema_id = sys.tables.schema_id
group by sys.schemas.name, tables.name, partitions.partition_number
order by sys.schemas.name, tables.name, partitions.partition_number

 

 

 

TSQL to obtain the memory usage usage by ColumnStore.

Using the view sys.dm_column_store_object_pool (Transact-SQL) - SQL Server | Microsoft Learn returns counts and memory usage for columnstore index objects.

 

This view could hep us about memory pressure and capacity plan for creating a new columnstore index table. Note this view will be based on the current workload at server level. Rememberr that a columnstore index stores each column in different segment and it is only needed to read segments into memory for the workload requested. This is different from rowstore indexes, where pages contain all columns in the index and must be read at the same time. 

 

 

select  sys.schemas.name, sys.tables.name, sys.indexes.name,
sys.dm_column_store_object_pool.partition_number, SUM(sys.dm_column_store_object_pool.memory_used_in_bytes) / 1024 / 1024 AS TotalInMB from sys.dm_column_store_object_pool
inner join sys.tables on sys.tables.object_id = sys.dm_column_store_object_pool.object_id 
INNER JOIN sys.schemas  on sys.schemas.schema_id = sys.tables.schema_id
INNER JOIN sys.indexes ON sys.indexes.object_id = sys.dm_column_store_object_pool.object_id AND sys.indexes.index_id = sys.dm_column_store_object_pool.index_id
GROUP BY sys.schemas.name, sys.tables.name, sys.indexes.name,
sys.dm_column_store_object_pool.partition_number
order  BY sys.schemas.name, sys.tables.name, sys.indexes.name,
sys.dm_column_store_object_pool.partition_number

 

 

 

Enjoy!

Version history
Last update:
‎Mar 29 2023 03:44 AM
Updated by: