作为 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
分享到:
相关推荐
explores the architectural differences of B-tree, Bw-tree, and xVelocity columnstore indexes. Finally, it explains core performance topics such as SQL Server query optimization statistics, SQL ...
1. Database Tuning advisor (DTA) Enhancements 2. Column store indexes 3. Online index operations
New in this second edition is coverage of SQL Server 2016 Internals, including In-Memory OLTP, columnstore enhancements, Operational Analytics support, Query Store, JSON, temporal tables, stretch ...
本文主要讲了强制使用索引条件来进行查询,当对于使用默认创建索引进行查询计划时觉得不是最优解,可以尝试使用强制索引来进行对比找出更好得解决方案。简短的内容,深入的理解.有兴趣的朋友可以看下
SQL Server: SELECT region_name + ' ' + store_name FROM Geography WHERE store_name = 'Boston'; (22) Substring ...................................................... ............... ...............13 取...
Indexes in SQL Server store their information using standard B-trees. A B-tree provides fast access to data by searching on a key value of the index. B-trees cluster records with similar keys. The B ...
SQL索引管理器 该工具使您可以快速找出索引的状态,并发现需要维护的数据库。...支持任何版本SQL Server 2008+和Azure 还有很多其他改进:) 最新版本 您可以从下载具有master分支的最新版本的.zip文件。 屏幕截图
字典通常只包含一个索引——一个按单词排序的索引。 当我们修改任何记录并更改聚集索引中索引列的对应值时,数据库可能需要将整行移动到单独的新位置以保持行的排序顺序。 此操作本质上是将更新查询转换为 DELETE,...
Components realize technology to store component properties to/from settings storage such as ini files, registry etc. TMemTableEh component dataset, which hold data in memory. Its possible consider...
Components realize technology to store component properties to/from settings storage such as ini files, registry etc. TMemTableEh component dataset, which hold data in memory. Its possible consider...
Components realize technology to store component properties to/from settings storage such as ini files, registry etc. TMemTableEh component dataset, which hold data in memory. Its possible consider...
Components realize technology to store component properties to/from settings storage such as ini files, registry etc. TMemTableEh component dataset, which hold data in memory. Its possible consider...
Components realize technology to store component properties to/from settings storage such as ini files, registry etc. TMemTableEh component dataset, which hold data in memory. Its possible consider...
Although most of us will use a SQL Server to store and retrieve data sets. There are several situation where an embedded database make sense. When you don't have a SQL Server available When you ...
5.1.4. mysql.server:MySQL服务器启动脚本 5.1.5. mysqld_multi:管理多个MySQL服务器的程序 5.2. mysqlmanager:MySQL实例管理器 5.2.1. 用MySQL实例管理器启动MySQL服务器 5.2.2. 连接到MySQL实例管理器并创建...
5.1.4. mysql.server:MySQL服务器启动脚本 5.1.5. mysqld_multi:管理多个MySQL服务器的程序 5.2. mysqlmanager:MySQL实例管理器 5.2.1. 用MySQL实例管理器启动MySQL服务器 5.2.2. 连接到MySQL实例管理器并创建...
5.1.4. mysql.server:MySQL服务器启动脚本 5.1.5. mysqld_multi:管理多个MySQL服务器的程序 5.2. mysqlmanager:MySQL实例管理器 5.2.1. 用MySQL实例管理器启动MySQL服务器 5.2.2. 连接到MySQL实例管理器并创建...
5.1.4. mysql.server:MySQL服务器启动脚本 5.1.5. mysqld_multi:管理多个MySQL服务器的程序 5.2. mysqlmanager:MySQL实例管理器 5.2.1. 用MySQL实例管理器启动MySQL服务器 5.2.2. 连接到MySQL实例管理器并创建...
5.1.4. mysql.server:MySQL服务器启动脚本 5.1.5. mysqld_multi:管理多个MySQL服务器的程序 5.2. mysqlmanager:MySQL实例管理器 5.2.1. 用MySQL实例管理器启动MySQL服务器 5.2.2. 连接到MySQL实例管理器并...