s.on_disk_size)/1024/1024 AS segment_size_mb
FROM sys.partitions AS p
JOIN sys.column_store_segments AS css
ON p.hobt_id = css.hobt_id
group by p.object_id
) ,
dictionary_size_by_table AS (
SELECT
p.object_id AS table_id,
SUM (csd.on_disk_size)/1024/1024 AS dictionary_size_mb
FROM sys.partitions AS p
JOIN sys.column_store_dictionaries AS csd
ON p.hobt_id = csd.hobt_id
www.2cto.com
group by p.object_id
)
select
t.name AS table_name,
s.segment_size_mb,
d.dictionary_size_mb,
s.segment_size_mb + isnull(d.dictionary_size_mb, 0) as total_size_mb
from dictionary_size_by_table d
JOIN sys.tables AS t
ON t.object_id = d.table_id
left outer join segment_size_by_table s
on d.table_id = s.table_id
order by total_size_mb desc
go
-- size per column
www.2cto.com
with segment_size_by_column as (
SELECT
p.object_id as table_id,
css.column_id,
SUM (css.on_disk_size)/1024/1024.0 AS segment_size_mb
FROM sys.partitions AS p
JOIN sys.column_store_segments AS css
ON p.hobt_id = css.hobt_id
GROUP BY p.object_id, css.column_id
),
dictionary_size_by_column as (
SELECT
p.object_id as table_id,
www.2cto.com
csd.column_id,
SUM (csd.on_disk_size)/1024/1024.0 AS dictionary_size_mb
FROM sys.partitions AS p
JOIN sys.column_store_dictionaries AS csd
ON p.hobt_id = csd.hobt_id
GROUP BY p.object_id, csd.column_id
)
select
t.name as table_name,
c.name as column_name,
s.segment_size_mb,
www.2cto.com
d.dictionary_size_mb,
s.segment_size_mb + isnull(d.dictionary_size_mb, 0) total_size_mb
from segment_size_by_column s
join sys.tables AS t
ON t.object_id = s.table_id
join sys.columns AS c
ON c.object_id = s.table_id
and c.column_id = s.column_id
left outer join dictionary_size_by_column d
www.2cto.com
on s.table_id = d.table_id
and s.column_id = d.column_id
order by total_size_mb desc
go
作者 incognito007