欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SQL SERVER CXPACKET-Parallelism Wait Type 的惯用解决方案

程序员文章站 2024-01-22 09:56:40
...

最近我的两个库出现,出现较多的CXPACKET等待,在网上找了一下资料。其中有篇一个SQL Server专栏作家的文章不错,也 解决 了我的一些疑问,就翻译在这里。 翻译整理仅用于传播资讯之目的。 原文出处: http://blog.sqlauthority.com/2011/02/06/sql-server-c

最近我的两个库出现,出现较多的CXPACKET等待,在网上找了一下资料。其中有篇一个SQL Server专栏作家的文章不错,也解决了我的一些疑问,就翻译在这里。

翻译整理仅用于传播资讯之目的。

原文出处:http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/

翻译整理:Joe.TJ

CXPACKET 已经成为所有等待类型中最常见的一种了。我通常会在多CPU系统的前五位等待类型统计中看见CXPACKET.

联机丛书:

当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时,可以考虑降低并行度。

CXPACKET 解释:

当为SQL查询创建一个并行操作时,会有多个线程去执行这个查询。每个查询处理不同的数据集或行集。

因为某些原因,一个或多个线程滞后,而产生了CXPACKET等待状态。

有一个组织/协调(organizer/coordinator)线程(Thread 0),它需要等待所有线程完成并聚合数据来呈现给客户端。

组织线程必须等待所有线程完成处理才能进行下一步。由于组织线程等待缓慢的线程完成处理所产生的等待,就叫CXPACKET等待。

请注意,并不是所有的CXPACKET等待类型都是不好的事情。你也许会遇某个CXPACKET等待是完全有意义的案例,有时它也是不可避免的。

如果你在任何查询上禁止此种等待,那么查询也许会变慢,因为不能为它执行并行操作。

减少CXPACKET等待:

我们不能抛开服务器负载类型来讨论减少CXPACKET等待。

OLTP: 在纯OLTP系统上,它的事务较短,查询也不长,但是通常很快速。设置“Maximum degree of Parallelism”(MAXDOP)为1

这样做可以确保查询永远不必使用并行方式运行,并且不会导致更多的数据库引擎开销。

EXEC sys.sp_configure N'cost threshold for parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Data-warehousing / Reporting server: 因为查询执行时间一般较长,建议设置“Maximum degree of Parallelism”(MAXDOP)为0

这样大多数查询将会利用并行处理,执行时间较长的查询也会受益于多处理器而提高性能。

EXEC sys.sp_configure N'cost threshold for parallelism', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Mixed System (OLTP & OLAP):这样环境会是一个挑战,必须找到正确的平衡点。我采取了非常简单的方法。

我设置“Maximum degree of Parallelism”(MAXDOP)为2,这样意味着查询仍会使用并行操作但是仅利用2CPU

然而,我把“并行查询阀值”设置为较高的值,这样的话,不是所有的查询都有资格使用并行,除了那些查询成本较高的查询。

在一个即有OLTP查询又有报表服务器的系统上,我发现这样做运行得很好。

在这里我将会设置“‘Cost Threshold for Parallelism’”为25(如图)。你可以选择任何值。但你只能通过在系统上做实验来找到合适的值。

在下面的脚本中,我设置“Max Degree of Parallelism”为2,这样的话,那些具有较高成本的查询(这里是25),将会在2CPU上执行并行查询。

同时,不管服务器有多少颗CPU,查询只会选择两颗CPU来执行。

EXEC sys.sp_configure N'cost threshold for parallelism', N'25'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

SQL SERVER CXPACKET-Parallelism Wait Type 的惯用解决方案

--------------------------------------------

如蒙转载或引用,请保留以下内容:
Joe's Blog:http://www.cnblogs.com/Joe-T/


完整维护过程:

这里涉及两个值:

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)来限制