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!
