clustered-index - 如何定义表是否是聚集列存储索引的良好候选者？

Question

我已阅读( here 、 here 和 here )关于 SQL Server 2014 中引入的聚集列存储索引。基本上，现在:

列存储索引可以更新

可以修改表架构(不删除列存储索引)

基表的结构可以是柱状

压缩效果节省的空间(使用列存储索引，您
可以节省 40% 到 50% 的初始空间用于
表)

此外，它们还支持:

行模式和批处理模式处理

BULK INSERT 语句

更多数据类型

据我所知，有一些限制，例如:

不支持的数据类型

无法创建其他索引

但正如它所说:

With a clustered column store index, all filter possibilities are already covered; Query Processor, using Segment Elimination, will be able to consider only the segments required by the query clauses. On the columns where it cannot apply the Segment Elimination, all scans will be faster than B-Tree index scans because data are compressed so less I/O operations will be required.

我对以下内容感兴趣:

上面的说法是不是说当存在大量重复值时，聚集列存储索引总是比 B 树索引更适合提取数据？

聚集列存储索引和非聚集B-Tree的性能如何covering索引，例如当表有很多列时？

我可以在一个表上同时使用聚集和非聚集列存储索引吗？

最重要的是，谁能告诉我如何确定一个表是否适合列存储索引？

据说最好的候选者是不经常执行更新/删除/插入操作的表。例如，我有一个存储大小超过 17 GB(大约 7000 万行)的表，并且不断插入和删除新记录。另一方面，执行了大量使用其列的查询。或者我有一个存储大小约为 40 GB(大约 6000 万行)的表，每天执行许多插入操作 - 不经常查询它，但我想减小它的大小。

我知道答案主要是运行生产测试，但在此之前我需要选择更好的候选人。

Answer 1

Clustered Columnstore 最重要的限制之一是它们的锁定，您可以在此处找到一些详细信息:http://www.nikoport.com/2013/07/07/clustered-columnstore-indexes-part-8-locking/

关于您的问题:

1) 上面的说法是不是说当存在大量重复值时，聚集列存储索引总是比 B 树索引更适合提取数据

批处理模式不仅可以更快地扫描重复项，而且当从 Segment 中读取所有数据时，对于数据读取，列存储索引的机制更有效。

2)聚集列存储索引和非聚集B-Tree覆盖索引之间的性能如何，例如当表有很多列时

列存储索引比页或行具有明显更好的压缩，可用于行存储，批处理模式将在处理方面产生最大的不同，正如已经提到的，对于列存储索引，即使读取相同大小的页面和范围也应该更快

3) 我可以在一个表上同时使用聚集和非聚集列存储索引吗

不，目前这是不可能的。

4) ... 谁能告诉我如何定义一个表是否适合列存储索引？

您正在大量(超过 100 万行)扫描和处理的任何表，或者 可能是 甚至整个表扫描超过 100K 可能是考虑的候选者。
与要在其中构建聚集列存储索引的表相关的使用技术有一些限制，这是我正在使用的查询:

select object_schema_name( t.object_id ) as 'Schema'
, object_name (t.object_id) as 'Table'
, sum(p.rows) as 'Row Count'
, cast( sum(a.total_pages) * 8.0 / 1024. / 1024 
    as decimal(16,3)) as 'size in GB'
    , (select count(*) from sys.columns as col
    where t.object_id = col.object_id ) as 'Cols Count'
, (select count(*) 
        from sys.columns as col
        join sys.types as tp
        on col.system_type_id = tp.system_type_id
        where t.object_id = col.object_id and 
             UPPER(tp.name) in ('VARCHAR','NVARCHAR') 
   ) as 'String Columns'
, (select sum(col.max_length) 
        from sys.columns as col
        join sys.types as tp
        on col.system_type_id = tp.system_type_id
        where t.object_id = col.object_id 
  ) as 'Cols Max Length'
, (select count(*) 
        from sys.columns as col
        join sys.types as tp
        on col.system_type_id = tp.system_type_id
        where t.object_id = col.object_id and 
             (UPPER(tp.name) in ('TEXT','NTEXT','TIMESTAMP','HIERARCHYID','SQL_VARIANT','XML','GEOGRAPHY','GEOMETRY') OR
              (UPPER(tp.name) in ('VARCHAR','NVARCHAR') and (col.max_length = 8000 or col.max_length = -1)) 
             )
   ) as 'Unsupported Columns'
, (select count(*)
        from sys.objects
        where type = 'PK' AND parent_object_id = t.object_id ) as 'Primary Key'
, (select count(*)
        from sys.objects
        where type = 'F' AND parent_object_id = t.object_id ) as 'Foreign Keys'
, (select count(*)
        from sys.objects
        where type in ('UQ','D','C') AND parent_object_id = t.object_id ) as 'Constraints'
, (select count(*)
        from sys.objects
        where type in ('TA','TR') AND parent_object_id = t.object_id ) as 'Triggers'
, t.is_tracked_by_cdc as 'CDC'
, t.is_memory_optimized as 'Hekaton'
, t.is_replicated as 'Replication'
, coalesce(t.filestream_data_space_id,0,1) as 'FileStream'
, t.is_filetable as 'FileTable'
from sys.tables t
inner join sys.partitions as p 
    ON t.object_id = p.object_id
INNER JOIN sys.allocation_units as a 
    ON p.partition_id = a.container_id
where p.data_compression in (0,1,2) -- None, Row, Page
group by t.object_id, t.is_tracked_by_cdc,  t.is_memory_optimized, t.is_filetable, t.is_replicated, t.filestream_data_space_id
having sum(p.rows) > 1000000
order by sum(p.rows) desc