如何定义表是否适合集群columnstore索引

Question

我读过(这里，这里和这里)关于SQL Server 2014引入的集群columnstore索引。基本上,现在:

• 列存储索引可以更新
• 可以修改表模式(不删除列存储索引)
• 基表的结构可以是柱状
• 压缩效果节省的空间(使用列存储索引)可以节省40%到50%的初始空间用于表)

另外，它们还支持:

• 行模式和批处理模式
• BULK INSERT语句
• 更多数据类型

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

1. 不支持的数据类型
2. 其他索引不能创建

但正如所说:

对于聚集的列存储索引，所有筛选器的可能性都是已经覆盖;查询处理器，使用段消除，将是能够只考虑查询子句所需的段。在不能应用分段消除的列，所有扫描会比B-Tree索引扫描快，因为数据被压缩了需要更少的I/O操作

我对以下产品感兴趣:

• 上面的语句是否说当存在大量重复值时，集群列存储索引总是比B-Tree索引更好地提取数据?
• 例如，当表有很多列时，聚类列存储索引和非聚类B-Tree covering索引的性能如何?
• 我可以在一个表上组合集群和非集群列存储索引吗?
• 最重要的是，谁能告诉如何确定一个表是否是列存储索引的好候选人?

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

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

Answer 1

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

关于你的问题:

1)上面的语句是否说明当存在大量重复值时，集群列存储索引总是比B-Tree索引更好地提取数据

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

2)当表有很多列(例如

)时，聚集列存储索引和非聚集b树覆盖索引的性能如何?

• Columnstore Index具有比Page或Row更好的压缩，可用于Row Store，批处理模式将在处理端产生最大的差异，正如已经提到的，甚至读取相同大小的页面&对于Columnstore Indexes
，区段应该更快

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

• 不，目前不可能。

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

• 您正在扫描的任何表格&处理大量数据(超过100万行)，或者甚至整个扫描超过100K的表都是可以考虑的。对于要在其中构建Clustered Columnstore索引的表，所使用的技术有一些限制，下面是我正在使用的查询:

---

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