SQL SERVER 2012 COLUMNSTORE INDEX-之一
来源：未知 责任编辑：责任编辑 发表时间:2013-11-18 20:57 点击:次
作为 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
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 进入详细评论页>>
