等待状态CXPACKET分析

最近由于两节的原因,一些抓取服务器都频频报告CPU 100%,上去看了下,结果单个的查询和存储过程执行时间都是<=1S,但是到线上就是几秒也不返回数据,情况是第一个等待状态为CXPACKET,其余都是suspended.SQL Server根本就没有等到5S就安排了并发

这里涉及两个值:

cost threshold for parallelism 是默认设定 5S. the estimated cost 高于5S才安排并发

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 4;--假如是8个(核)cpu

GO

RECONFIGURE WITH OVERRIDE;

GO

max degree of parallelism 能最大限制的控制并行导致CPU不可用而造成的短查询的等待

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'cost threshold for parallelism', 10;--将此时间增加

GO

RECONFIGURE WITH OVERRIDE;

GO

也可以单独指定option(maxdop 1)来限制

联机帮助中对这个问题的解释

使用 cost threshold for parallelism 选项指定 Microsoft SQL Server 创建和运行并行查询计划的阈值。仅当运行同一查询的串行计划的估计开销高于在 cost threshold for parallelism 中设置的值时，SQL Server 才创建和运行该查询的并行计划。开销指的是在特定硬件配置中运行串行计划估计需要花费的时间（秒）。只能在对称多处理器系统上设置 cost threshold for parallelism。
并行计划对需时较长的查询通常更加有益；其性能优势将抵消初始化、同步和终止并行计划所需的额外时间开销。短时间和长时间查询混合运行时，可以灵活使用 cost threshold for parallelism 选项。短时间查询使用串行计划运行，而长时间查询使用并行计划运行。cost threshold for parallelism 的值确定哪些查询是短时间查询，因而应该使用串行计划运行。
在某些情况下，即使查询的开销计划小于当前 cost threshold for parallelism 的值，也有可能选择并行计划。出现这种情况，是因为使用并行还是串行计划是根据完成完全优化之前所提供的开销估计确定的。
cost threshold for parallelism 选项可设置为 0 到 32767 之间的任何值。默认值为 5。
在下列情况下，SQL Server 将忽略 cost threshold for parallelism 的值：
计算机只有一个处理器。

 

由于 affinity mask 配置选项的限制，SQL Server 只能使用一个 CPU。

max degree of parallelism 选项设置为 1。

cost threshold for parallelism 选项是一个高级选项。如果使用 sp_configure 系统存储过程来更改此设置，则只有在 show advanced options 设置为 1 时才能更改 cost threshold for parallelism。更改后的设置将立即生效，而不需要重新启动服务器。
示例 以下示例将 cost threshold for parallelism 设置为 10 秒。
sp_configure 'show advanced options', 1;
GO

reconfigure;
GO

sp_configure 'cost threshold for parallelism', 10;
GO

reconfigure;
GO 

 

 

--EOF--