作者:帝京 | 来源:互联网 | 2018-07-11 07:49
初次尝试ColumnStoreIndexPostedon1、首先使用非聚集索引TIMEONSELECTMemberId,((VExpd)(DD,(DD,FactOrdersoWITH(INDEX(IX_IsBigOrder_O))IsBigOrderIsBigOrderMemberId结果SQLServerparseandcompiletime:CPUtimems.SQLServe
初次尝试ColumnStore IndexPosted on 1、首先使用非聚集索引 TIME ON SELECT MemberId , ((VExpd) (DD, (DD, FactOrders o WITH ( INDEX (IX_IsBigOrder_O) ) IsBigOrder IsBigOrder MemberId 结果 SQL Server parse and compile time:CPU time ms.SQL Serve
初次尝试ColumnStore Index
Posted on
1、首先使用非聚集索引
TIME ON
SELECT MemberId ,
((VExpd) (DD, (DD, FactOrders o WITH( INDEX(IX_IsBigOrder_O) )
IsBigOrder IsBigOrder MemberId
结果
SQL Server parse and compile time:
CPU time ms.
SQL Server Execution Times:
CPU time ms.
SQL Server Execution Times:
CPU time ms.
(1121211 row(s) affected)
. Scan ahead reads 57581, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
. Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
. Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time ms.
2、使用ColumnStore Index
TIME ON
SELECT MemberId ,
((VExpd) (DD, (DD, FactOrders o WITH( INDEX(IX_CS) )
IsBigOrder IsBigOrder MemberId
结果
SQL Server Execution Times:
CPU time ms.
SQL Server parse and compile time:
CPU time ms.
SQL Server Execution Times:
CPU time ms.
SQL Server Execution Times:
CPU time ms.
(1121211 row(s) affected)
. Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
. Scan ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time 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点的限制,以前怕麻烦,所以比较抗拒,从这次的尝试来看,香港空间,性能提升还是很大的。
计划运行一阵,跟踪瞧瞧如何。
,网站空间