SQLServer列存储索引性能总结（5）——列存储等待信息接上⽂：，本⽂介绍列存储相关的锁
爱情包围圈 上周六，我在加班，为公司的Azure SQL DB测试聚集列存储索引，按照⽹上的说法，对堆表建⽴聚集列存储索引应该很快的，何况我使⽤的是SQL DB中最⾼I/O的Pricing Tier——Business Critical vCore 80，号称IOPS 可达204800，Log的吞吐量也可以达96 Mbps。
可是事与愿违，我的表500多列，1300万⾏，堆表⼤⼩100G，创建时间整整50分钟。我很纳闷，我在⼀台普通的Linux VM⾥⾯的SQL Server 2019，对三千万数据建列存储也只是需要2分钟左右，这个太不合理了。所以我删了索引再次创建，并且查看发⽣了什么事。
最好的切⼊点往往就是事务和等待。如果事务没有明显的异常，那很可能就是存在等待/阻塞。由于SQL DB不⽀持很多很好的⼯具如sp_whoisactive，所以只能简单查看sys.sysprocess。发现存在⼏个等待类型——CXPACKET 、cxconsumer和
columnstore_build_throttle。⽽且持续⾮长久的时间。那么下⾯我们来了解⼀下这三个等待类型是关于什么的。CXPACKET
高职教育
很好理解也挺常见，你可以理解为出现了并⾏执⾏，如果服务器有多CPU，那么这个类型是很常见的。除⾮你把并⾏度设为1，不过这样⽐较不合理。特别是对于我公司环境⾥⾯偏DW类型的应⽤⽽⾔。
这个类型主要是SQL Server认为需要进⾏并⾏执⾏某个ssion的SQL，使⽤多线程（Thread）来实现，线程之间出现了阻塞或者等待（⽔桶效应）。
我们在TempDB上建⼀个表然后循环插⼊80万数据，然后更新统计信息：
u tempdb
go
月饼的来历的故事CREATE TABLE orders (d_id INT, o_id INT, o_amount INT, o_description CHAR(2000))超过的英语
CREATE UNIQUE CLUSTERED INDEX test ON orders(d_id, o_id)
go
BEGIN TRAN
DECLARE@i INT
SET@i=1
绿色环保海报WHILE@i<=800000
BEGIN
INSERT INTO orders VALUES(@i%8,@i, RAND()*800000, REPLICATE('a',2000))
SET@i=@i+1
END
COMMIT TRAN
GO
UPDATE STATISTICS orders WITH fullscan
GO
CREATE TABLE#department (d_id INT)
INSERT INTO#department VALUES(0)
INSERT INTO#department VALUES(1)
INSERT INTO#department VALUES(2)
INSERT INTO#department VALUES(3)
INSERT INTO#department VALUES(4)
INSERT INTO#department VALUES(5)
INSERT INTO#department VALUES(6)
INSERT INTO#department VALUES(7)
GO
接下来清空统计信息，我们使⽤OPTION(MAXDOP 1)来限定不允许并⾏，然后使⽤SET STATISTICS TIME ON来统计⼀下时间，并且检查CXPACKET的信息：
SET STATISTICS time ON
GO
DBCC sqlperf('sys.dm_os_wait_stats', clear)
DECLARE@order_amount INT
SELECT@order_amount=MAX(o_amount)
FROM orders o INNER JOIN#department d ON (o.d_id = d.d_id)
OPTION(maxdop 1)
SELECT*FROM sys.dm_os_wait_stats
WHERE wait_type ='CXPACKET';
执⾏时间如下：
SQL Server Execution Times:
CPU time=815 ms, elapd time=815 ms.
接下来按同样的⽅式测试不限制并⾏度，也就是MAXDOP为0：
DBCC sqlperf('sys.dm_os_wait_stats', clear)
DECLARE@order_amount INT
SELECT@order_amount=MAX(o_amount)
FROM orders o INNER JOIN#department d ON (o.d_id = d.d_id)
OPTION(maxdop 0)
SELECT*FROM sys.dm_os_wait_stats
WHERE wait_type ='CXPACKET'
GO
执⾏时间如下：
SQL Server Execution Times:
CPU time=983 ms, elapd time=306 ms.
从这两个测试可以对⽐出，并⾏执⾏⾸先可能出现waiting，然后CPU time会⾼，因为使⽤的CPU数量相对也多。但是从elapd time也就是运⾏时间来看，并⾏执⾏的时间相对较短。
但是这并不代表什么，对于OLTP系统⽽⾔，由于操作相对频繁，量少，并⾏执⾏并不能从中获益太多。对DW/OLAP⽽⾔，则可以充分利⽤CPU资源。所以对于我个⼈⽽⾔，由于长期运维OLTP系统，所以并不很喜欢看到这种等待状态。
COLUMNSTORE_BUILD_THROTTLE
这个等待类型会在列存储索引创建和重建的过程中出现。主要原因是第⼀个线程会决定内存的使⽤量和可能需要的线程数，这就导致了所有的线程都需要等待第⼀个线程分析并完成这些信息收集和处理。所以出现了这部分的等待。
如果没有⾜够内存，那么总线程数就会降低，这个过程也会算⼊等待时间。
有⼀个相关的扩展事件——column_store_index_build_throttle。
这⼀类等待也通常会在内存压⼒或者Dictionary（后续章节会说到） 压⼒发⽣时出现。
下⾯创建⼀个测试来演⽰dictionary压⼒和哪些⾏组会被⾮常明显地修剪。
DROP TABLE IF EXISTS dbo.t_colstore;
刚毅是什么意思CREATE TABLE dbo.t_colstore (
c1 int NOT NULL,
c2 INT NOT NULL,
c3 char(40)NOT NULL,
c4 char(800)NOT NULL
);
t nocount on
declare@outerloop int=0
declare@i int=0
while(@outerloop<1100000)
begin
Select@i=0
马和什么属相最配
begin tran
while(@i<2000)
begin
inrt t_colstore values(@i+@outerloop,@i+@outerloop,'a',
concat (CONVERT(varchar,@i+@outerloop),(replicate ('b',750)))) t@i+=1;
end
commit
t@outerloop=@outerloop+@i
t@i=0
end
钮祜禄go
然后开启实际执⾏计划并创建聚集列存储索引：
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON dbo.t_colstore;
从inrt运算符的属性中的“WaitStats”可以看到，其中会有⼀个等待状态是COLUMNSTORE_BUILD_THROTTLE，也就是说有1.183秒（来⾃WaitTimeMS）是⽤于第⼀个线程的修剪操作。出现这种情况，⼀般就需要修改表设计。
cxconsumer
这是从SQL 2016 SP2开始引⼊的新等待状态。这个等待状态意味着有并⾏计划在运⾏。良性 CXPACKET 等待现在显⽰为CXCONSUMER 等待，可以安全地忽略。
不过凡事也有个但是，对于执⾏计划操作符⽽⾔，有⽣产者（producer）和消耗者（consumer）线程。在并⾏过程中，⽣产者线程会实际影响性能，导致消耗者线程只能等待⽣产者提供数据。
对于这种情况，切记不要单纯降低MAXDOP，需要优化性能。
但是在我的环境中⽐较悲剧，因为这是创建聚集列存储索引，这⼀点很难进⾏优化。
总结
综合前⾯所述，在有⾜够CPU的前提下，不限制MAXDOP的话，创建⼤表的索引（不管是否列存储），都可能会使⽤并⾏运⾏，这样就会出现线程之间的等待，并⾏执⾏对于OLTP来说并不⼀定是好事，不过也不⼀定是坏事。另外由于本系列是基于列存储的，所以更重要的我想介绍COLUMNSTORE_BUILD_THROTTLE这种等待类型。这种等待类型出现的时候，注意检查是否有内存压⼒和Dictionary的压⼒，另外表的设计是否合理。关于这部分在后续⽂章再介绍。
在本⼈⼯作的那个例⼦中，我分别试了1/4/8/16/24/32 的MAXDOP，但是发现越⼤的MAXDOP配置，速度越快。所以凡是都有个例外。
下⼀篇：
