初次尝试ColumnStore Index

1、首先使用非聚集索引

SET STATISTICS IO ON 
SET STATISTICS TIME ON 
SELECT  MemberId ,
        MAX(AddDT) AS RecentSucDT ,--最近成功订单日期
        COUNT(1) AS SucOrderNum ,--成功订单数
        SUM(VExpd) AS SucAmount ,--成功订单金额
        DATEDIFF(DD, MIN(AddDT), MAX(AddDT)) AS Tenure ,--活跃天数
        DATEDIFF(DD, MAX(AddDT), GETDATE()) AS Rencency--崭新天数
FROM    FactOrders o WITH( INDEX(IX_IsBigOrder_O) )
WHERE   o.StateId = 21
        AND IsBigOrder <> 8
        AND IsBigOrder <> 4
GROUP BY MemberId 

结果

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 16 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1121211 row(s) affected)
Table 'FactOrders'. Scan count 5, logical reads 5137168, physical reads 1, read-ahead reads 57581, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 27471 ms,  elapsed time = 77144 ms.

 

2、使用ColumnStore Index

SET STATISTICS IO ON 
SET STATISTICS TIME ON 
SELECT  MemberId ,
        MAX(AddDT) AS RecentSucDT ,--最近成功订单日期
        COUNT(1) AS SucOrderNum ,--成功订单数
        SUM(VExpd) AS SucAmount ,--成功订单金额
        DATEDIFF(DD, MIN(AddDT), MAX(AddDT)) AS Tenure ,--活跃天数
        DATEDIFF(DD, MAX(AddDT), GETDATE()) AS Rencency--崭新天数
FROM    FactOrders o WITH( INDEX(IX_CS) )
WHERE   o.StateId = 21
        AND IsBigOrder <> 8
        AND IsBigOrder <> 4
GROUP BY MemberId 

结果

SQL Server Execution Times:
   CPU time = 10499 ms,  elapsed time = 19763 ms.

   SQL Server parse and compile time: 
   CPU time = 13 ms, elapsed time = 13 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1121211 row(s) affected)
Table 'FactOrders'. Scan count 2, logical reads 23338, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 10422 ms,  elapsed time = 20762 ms.

 

从结果的logical reads上可以看出明显差异，CPU time和elapsed time分别2倍和3倍之多。

 

3、使用ColumnStore Index不能像使用其它非聚集索引那样没有限制，使用ColumnStoreIndex的限制如下:

    1.一个表只能有一个ColumnStore Index

    2.不能使用过滤索引

    3.索引必须是partition-aligned

    4.被索引的表变成只读表

    5.被索引的列不能是计算列

    6.不能使用Include关键字

由于第4点的限制，以前怕麻烦，所以比较抗拒，从这次的尝试来看，性能提升还是很大的。

计划运行一阵，跟踪瞧瞧如何。