sql语句大全之列存储段消除（ColumnStore Segment El

列存储索引是好的！对于数据仓库和报表工作量，它们是真正的性能加速器。与聚集列存储结合，你会在常规行存储索引（聚集索引，非聚集索引）上获得巨大的压缩好处。而且创建聚集列存储索引非常简单：

CREATE CLUSTERED COLUMNSTORE INDEX ccsi ON TableName
GO

但这是你对聚集列存储需要知道的一切？并不是，如你在这篇文章会看到的……

什么是列存储段（ColumnStore Segments）？

在我各个研讨会和公共培训课程期间，我经常开玩笑：一旦你开释使用聚集列存储索引，你就不需要知道索引的更多信息。使用聚集列存储索引很太多的优点，它会带来巨大的性能提升：

• 更好的压缩
• 批处理模式执行
• 更少I/O，更好内存管理
• 段消除

如你从下例子看到的，在SQL Server里创建聚集列存储索引非常简单：

CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactOnlineSales
GO

你只需指定表名，没别的。甚至你不需要担心聚集键列，因为这个概念对列存储索引不适用。很简单，是不是？让我们在适当的地方用刚才的聚集索引运行一个简单的查询：

-- Segment Elimination doesn't work quite well, because
-- we have a lot of overlapping Segments.
SELECT
    DateKey, 
    SUM(SalesAmount) 
FROM FactOnlineSales_Temp
WHERE
    DateKey >= '20090101' 
    AND DateKey <= '20090131'
GROUP BY
    DateKey
GO

这个查询非常快，因为对于查询执行，SQL Server可以使用聚集列存储索引。从STATISTICS IO输出也向你展示了，对于聚集列存储索引不需要很多LOB Logical Reads：

但那些段读取（Segment Read）和段跳过（Segment Skipped）度量呢？

你们也许知道列存储索引内部分成所谓的列存储段（ColumnStore Segments）。一个列存储段通常指定到特定的列和行组。一个行组包含近100万行。下图很好的展示了这个重要概念：

来源：https://www.microsoft.com/en-us/research/publication/enhancements-to-sql-server-column-stores/

什么是列存储段消除（ColumnStore Segment Elimination）？

这里最重要的是，对于每个列存储段，SQL Server内部存储了最小和最大的值。基于这些值，SQL Server可以进行所谓的段消除。段消除意味着SQL Server只读取包含请求数据的那些段（在访问列存储索引时）。你可以认为它是和分区消除一样得方式，在你和分区表打交道的时候。但这里的消除发生在列存储段级别。

如你在刚才的图片所见，在列存储索引访问期间SQL Server不能消除任何段，因为默认情况下，在列存储索引里你没有排列顺序。你数据的排列顺序取决于在执行计划里，在你创建列存储索引时，SQL Server如何读取数据：

 

如你所见，聚集列存储索引通过从最初包含数据的堆表创建。因此在聚集列存储索引里，你没有排列顺序，因此段消除不能很好为你工作。

如何改善情况？在你的数据里首先通过创建传统的行存储聚集索引来强制排序，然后修改它为聚集列存储索引！偶滴神啊……

-- Now we create a traditional RowStore Clustered Index to sort our
-- table data by the column "DateKey".
CREATE CLUSTERED INDEX idx_ci ON FactOnlineSales_Temp(DateKey)
GO

-- "Swap" the Clustered Index through a Clustered ColumnStore Index
CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactOnlineSales_Temp
WITH (DROP_EXISTING = ON)
GO

有了传统的聚集行存储索引就位，当你创建聚集列存储索引时，在执行计划里，查询优化器会引用这个索引：

作为副作用，在聚集列存储索引里，你现在应该有已排序的数据，段消除应该会很好处理：

-- Segment Elimination works better than previously, but still not perfectly.
SELECT
    DateKey, 
    SUM(SalesAmount) 
FROM FactOnlineSales_Temp
WHERE
    DateKey >= '20090101' 
    AND DateKey <= '20090131'
GROUP BY
    DateKey
GO

但当你再次查看STATISTICS IO的输出，SQL Server还是需要读取很多段，只跳过其中几个：

但为什么SQL Server不能跳过所有的段而只跳过几个？问题存在于聚集列存储的创建。当你回头看刚才的执行计划，你会看到ColumnStore Index Insert (Clustered) 运算符是并行运行的——通过多个工作者线程。而且这些工作者线程再次破坏了聚集列存储索引里你数据的排序！你从聚集行存储索引里进行你的数据读取，然后聚集列索引的并行创建重排了你的数据……伤及无辜~~~

你只能通过使用MAXDOP为1的聚集列存储创建来解决这个问题：

CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactOnlineSales_Temp
WITH (DROP_EXISTING = ON, MAXDOP = 1)
GO

这听起来很糟糕，事实也如此！但这是唯一让你在列存储索引里阻止重排你数据的解决方法。当你接下来从聚集列存储数据读取后，你会看到SQL Server终于能跳过所有的段：

 小结

聚集列存储索引很好——真的很好！但默认段消除不能很好进行，因为在你的聚集列存储里没有预定义的排序。因此在你调优你的列存储查询时，你要确保段消除可以正常进行。而且有时候你甚至需要通过使用MAXDOP 1来阻止你的数据排序……

感谢关注！

原文链接：

https://www.sqlpassion.at/archive/2017/01/30/columnstore-segment-elimination