注册
登录
| 关键词： nbsp size id dictionary table column segment INDEX object hobt |
作为 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. www.2cto.com 3，不可以用于： INSERT, UPDATE, DELETE, MERGE 需要 [sql] ALTER INDEX mycolumnstoreindex ON mytable DISABLE; ALTER INDEX mycolumnstoreindex ON mytable REBUILD; 4，增加内存： [sql] ALTER WORKLOAD GROUP [DEFAULT] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT=X) ALTER RESOURCE GOVERNOR RECONFIGURE GO --where X is the percent, say 50. 5，查询索引大小： [sql] -- total size with total_segment_size as ( SELECT SUM (css.on_disk_size)/1024/1024 AS segment_size_mb FROM sys.partitions AS p www.2cto.com 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 www.2cto.com 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 www.2cto.com 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 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 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
