SQL SERVER 2012 COLUMNSTORE INDEX - 之一

作为 SQL SERVER 2012 一大卖点的 CLOUMNSTORE INDEX，先来认识一下。

 

1，支持数据类型：

int, big int, small int, tiny int, money, smallmoney, bit, float, real, char(n), varchar(n), nchar(n), nvarchar(n), date, datetime, datetime2, small datetime, time, datetimeoffset with precision <=2, decimal or numeric with precision <= 18

 

2，不支持数据类型：

decimal or numeric with precision > 18, datetimeoffset with precision > 2, binary, varbinary, image, text, ntext, varchar(max), nvarchar(max), cursor, hierarchyid, timestamp, uniqueidentifier, sqlvariant, xml.

 

3，不可以用于：

INSERT, UPDATE, DELETE, MERGE

需要

ALTER INDEX mycolumnstoreindex ON mytable DISABLE;

ALTER INDEX mycolumnstoreindex ON mytable REBUILD;

 

4，增加内存：

ALTER WORKLOAD GROUP [DEFAULT] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT=X)
 ALTER RESOURCE GOVERNOR RECONFIGURE 
GO 

--where X is the percent, say 50.
 

 

5，查询索引大小：

-- total size 

with total_segment_size as ( 

    SELECT  

        SUM (css.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 

) 

, 

total_dictionary_size as ( 

    SELECT 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 

) 

select  

    segment_size_mb,  

    dictionary_size_mb, 

    segment_size_mb + isnull(dictionary_size_mb, 0) as total_size_mb 

from total_segment_size  

left outer join total_dictionary_size 

    on 1 = 1 

go 

  
 
-- size per index 

with segment_size_by_index AS ( 

SELECT  

    p.object_id as table_id, 

    p.index_id as index_id, 

    SUM (css.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, p.index_id 

) , 

dictionary_size_by_index AS ( 

SELECT  

    p.object_id as table_id, 

    p.index_id as index_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 

group by p.object_id, p.index_id 

) 

select  

    object_name(s.table_id) table_name, 

    i.name as index_name, 

    s.segment_size_mb, 

    d.dictionary_size_mb, 

    s.segment_size_mb + isnull(d.dictionary_size_mb, 0) as total_size_mb 

from segment_size_by_index s 

JOIN sys.indexes AS i 

    ON i.object_id = s.table_id 

    and i.index_id = s.index_id 

left outer join dictionary_size_by_index d 

    on s.table_id = s.table_id 

    and s.index_id = d.index_id 

order by total_size_mb desc 

go 

  
 
-- size per table 

with segment_size_by_table AS ( 

SELECT  

    p.object_id as table_id, 

    SUM (css.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 

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 

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, 

        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, 

    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 

    on s.table_id = d.table_id 

    and s.column_id = d.column_id 

order by total_size_mb desc 

go