(6)  当执行计划发现并不需要用并行执行时,强制sql 使用OPTION ( MAXDOP x)
也不会采用并行执行。

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

步骤六:查看TASK

图片 1

   1.1 
 查询 CXPACKET的等待

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

【关系】

最大工作线程数可以通过下面的查询得到。SQL
SERVER并不是一开始就把这些所有的工作线程都创建,而是依据需要而创建。

     下面是一个分组查询,在执行计划中看到,以采用了并行处理

 

结果如下:

一个客户端connection可能包含一个或多个BATCH,一般SQL
Server引擎会为一个BATCH视为一个TASK,但使用并行化查询的BATCH会被分解成多个TASK。具体BATCH怎么分解成TASK,以及分解成多少个,则是由SQL
Server内部决定的。但是在这里我们依然可以使用相关DMV探寻一下大致分配情况:

一.概述

 CXPACKET 解释:

然后执行下面的程序。下面的程序会开启256个连接到SQL
Server, 这256个连接由于前面的transaction未闭合,都处于BLOCKING状态。

步骤一:

   CXPACKET是指:线程正在等待彼此完成并行处理。什么意思呢? 当sql
server发现一条指令复杂时,会决定用多个线程并行来执行,由于某些并行线程已完成工作,在等待其它并行线程来同步,这种等待就叫CXPACKET。

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

步骤七:查看WORKER

如上图所示,左边是很多连接,每个连接有一个相应的SPID,只要用户没有登出,或者没有timeout,这个始终是存在的。标准设置下,对于用户连接数目,是没有限制的。

 或采用sys.sysprocesses查看结果。下面一个举例中
会话session是SPID 56。 这里我们明显看到,SQL Server使用了5个线程kpid
来执行这个query。

  翻译整理:Joe.TJ

SQL
Server的任务调度使得SQL
SERVER能够以最快方式处理用户发过来的请求。了解SQL
SERVER的任务调度过程,对于我们调整系统性能是非常有帮助的。如适当增加MAX
WORKER
THREAD,调整MAXDOP,去除BLOCKING等等,了解这些概念,会使得我们的调整更有目的性。

步骤二:

图片 2

 

using System;
using System.Diagnostics;
namespace WORKER
{
    class Program
    {
        static void Main(string[] args)
        {
            for(int i=0; i<256; i++)
            {
                OpenConnection();
            }
        }
        static void OpenConnection()
        {
            ProcessStartInfo startInfo = new ProcessStartInfo();
            startInfo.FileName = "sqlcmd.exe";
            startInfo.Arguments = " -E -S SERVERNAME -d TEST -q \" SELECT * FROM TEST \"";
            Process.Start(startInfo);
        }
    }
}

对于很大的SPID编号,通常表明,我们的WORKER数是很高的。这种情况比较危险,如果一个新的连接进来,可能没有空闲WORKER来处理这个连接。在CLUSTER环境下,ISALIVE检查会失败,会导致SQL
SERVER做FAILOVER。

  下面是通过sys.dm_os_waiting_tasks 来查看该语句的task任务。

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

CREATE DATABASE TEST
go
use TEST
go
CREATE TABLE TEST(ID int,name nvarchar(50))
INSERT INTO TEST VALUES (1, 'aaa')
SELECT * FROM TEST

1.5  CXPACKET资源等待总结

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

图片 1

图片 4

 图片 5

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

【跟踪】

步骤四:查看连接

  设置sql语句级的MAXDOP。如果MAXDOP=1的话,使得一个BATCH只对应一个TASK。如果没有设置MAXDOP,一个BATCH可能会产生多个TASKS,那么TASK之间的协调,等待等等,将是很大的开销。把MAXDOP设小,能同时减少WORKER的使用量。所以,如果我们看到等待类型为CXPACKET的话,那么我们可以设置MAXDOP,减少并行度。

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

select cpu_count,scheduler_count,scheduler_total_count from sys.dm_os_sys_info

SQL
Server上,每一个CPU通常会对应一个Scheduler,有几个额外的系统的Scheduler,只是用来执行一些系统任务。对用户来讲,我们只需要关心User
Scheduler就可以了。如果有4个CPU的话,那么通常就会有4个User
Scheduler。

 (5)  避免或减少白天执行频繁复杂sql,优化sql 建好索引。

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

select * from sys.dm_os_tasks where session_id=63 order by 7

WORKER(又称为WORKER
THREAD), 则是工作线程。在一台服务器上,我们可以有多个工作线程。因为每一个工作线程要耗费资源,所以,SQL
Server有一个最大工作线程数。

   图片 6

  联机丛书:

这是因为WORKER用完的缘故。新的连接无法获得一个WORKER来做login
process。所以导致连接失败。在群集环境下,如果连接不上SQL Server,
ISALIVE检查会失败,会引起SQL Server
FAILOVER。所有的连接都会被强迫中止,并且SQL
Server会在新结点上重新启动。针对这种情况,我们可以修改提高MAX WORKER
THREAD,但是并不能最终解决问题,由于BLOCKING缘故,新的连接会迅速积累,一直把MAX
WORKER
THREAD用完,所以这时候,我们应该检查BLOCKING。使得task能及时完成,释放WORKER。

图片 7

  为什么会有并行线程呢?  因为在sql server
里有个任务调度SCHEDULER是跟操作系统CPU个数 默认是一 一匹配的, 
我们也可能通过sp_configure来设置最大并行度,也就是Max Degree of Parallelism
(MAXDOP)。 关于调度可参考” sql server
任务调度与CPU”

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

图片 7

【总结】 

  由于并行的原因而从出现了Expacket
的等待。是否并行的执行,通过执行计划可以查看到,下面是查询大表中的数据,sql
server自动加启了并行执行。

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

查询SELECT * FROM sys.dm_os_tasks这时候我们发现有278个TASK,而查询sys.dm_os_schedulers 我们发现有两个CPU, 因此有两个用户SCHEDULER, 每个SCHEDULER上,有128个workers. 加起来有256个WORKERS。针对两个CPU的架构,我们缺省最大的WORKER数是256。所以已经到了极限了。

步骤六:查看TASK

最后考虑调整并行度的开销阈值或降低并行度。

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

这时候,我们新开启一个连接,会发现SQL Server连不上,并报如下错误:

步骤五:查看batch

  并行处理的劣势:1是并行线程要等待同步。2是由于这10个线程全力以赴,就有10个对应的cpu,这样别的用户发过来的指令就会受到影响,甚至拿不到cpu来执行。所以对于并发度要求高的需要及时响应的,一般会建议手动设置每个指令的并行线程数。反之可以不设置Max
Degree of Parallelism由系统默认去并行或者设少一点并行度。

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

我们了解了SQL
SERVER任务调度的机制,那么有些问题,就会更加清楚。

原文网址如下:

 (4)  找出程序中感觉复杂的sql语句,查看执行计划。

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

我们使用spid为63的窗口执行一个复杂的查询,此查询使用默认并行度运行(由于有8个CPU因此默认MAXDOP=8)。

begin tran
update TEST set name='bbb' where [ID] = 1

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注