CPU瓶颈（七）--并行查询

并行查询

　　当为一个查询生成一个执行计划时，SQL Server优化器尝试为这个查询选择相应速度最快的计划。如果执行该查询的消耗超过了cost threshold for parallelism选项中的设置，并且并行执行并没有被禁用掉，优化器会尝试生成一个可以并行执行的计划。一个并行查询计划会尝试使用多个线程执行这个查询，它分布式调用CPU中可用的各个处理器并且在同一时间在各个处理器上同步执行。并行的最大深度是server-wide，可以通过max degree of parallelis设定，也可以通过OPTION(MAXDOP)查询提示去设置resource workload级别，或者一个per-query级别。

　　最终决定为这个并行查询使用哪种程度的并行（DOP）--也就是估计一下有多少线程会被用在这个指定的操作上--这个是被推迟到真正执行时才进行的。在执行查询之前，SQL Server会判断下有多少的调度器还没有被充分地使用，并且为这个查询选择一个DOP以完全利用存在着的调度器。选择完DOP后，查询在运行完成之前一直都会使用这个指定的并行查询深度。典型地，一个并行查询执行所用的CPU time数量与对应的串行执行所使用的CPU time数量近似，甚至比串行使用数量会稍微多一些，但实际上并行处理所使用的总的处理时间会较少。如果没有其它瓶颈因素的话，比如物理I/O等待，并行计划几乎会100%使用CPU上的所有处理器。

　　一个关键因素（系统如何安排闲置）导致在查询开始运行之后并行计划可以被修改。这个可以更改，然而，在查询已经开始运行之后。例如，如果一个查询在闲置时间出现，服务器可能会选择执行一个并行计划，指定一个DOP，并且在四个处理器上使用四个线程。这些线程开始运行之后，已经存在的连接会提交其它的查询同样使用很多的CPU。这时候，所有不同的线程需要去共享CPU可用的短暂时间切片，从而导致较高的查询持续时间。

　　运行并行计划并不是只有这些不足，还应该提供最快的查询相应时间。然而，给定查询的响应时间需要权衡下整个系统上其它查询的吞吐量及响应效果。并行查询一般最适合于批量处理机决策支持工作任务，并且在事物处理环境中不是太适用。

　　SQL Server 2008提升了分区表查询对可用硬件资源进行完全使用的扩展性。因此，SQLServer2008可能会比老的版本使用更多数量的CPU。如果这不是你想要的，你需要限制或者禁用掉并行功能。

诊断

　　并行查询问题可以使用下面的方法进行诊断。

　　Performance Monitor

　　更多关于SQL Server:SQL Statistics - Batch Requests/sec计数器的信息，可以参阅：http://msdn.microsoft.com/en-us/library/ms190911.aspx.

　　因为一个查询在被考虑作为一个并行查询之前，必须有一个预估消耗超过了并行设定的阈值（默认是5），服务器每秒钟有越多的批处理，这个批处理运行并行计划的可能性就越小。正在运行多个并行查询的服务器一般而言每秒钟也有较小的批请求（例如，值小于100）。

　　DMVs

　　在一个正在运行服务器上，你可以使用下面的查询去确定是否有一些活动的请求正在执行并行操作。

　　

select r.session_id,r.request_id,MAX(ISNULL(exec_context_id,0)) as number_of_workers,
r.sql_handle,r.statement_start_offset,r.statement_end_offset,r.plan_handle
 from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id=t.scheduler_id
join sys.dm_exec_sessions s on r.session_id=s.security_id
where s.is_user_process = 0x1
group by r.session_id,r.request_id,r.sql_handle,r.plan_handle,r.statement_start_offset,r.statement_end_offset
having MAX(ISNULL(exec_context_id,0))>0

　　有了这些信息，你可以很容易地获取对应的查询通过sys.dm_exec_sql_text,并且你可以获取对应计划通过sys.dm_exec_cached_plan.

　　你也可以查找那些适合进行并行运行的计划。要这样去做的话，查找缓存的计划去查看是否有一个关联的操作，这个操作的并行属性有一个非零值。这些计划可能不是在并行运行，但你也可以在系统不太忙时并行执行它们。

--
--Find query plan that can run in parallel
--
select p.*,
q.*,
cp.plan_handle
from sys.dm_exec_cached_plans cp 
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype='Compiled Plan' and p.query_plan.value('declare namespace p="http://schemas.micorosoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)','float')>0

　　一般而言，一个查询的持续时间会比CPU time的数量要长，因为其中一部分时间花费在了等待资源上，比如锁或者物理I/O。查询使用的CPU时间比持续时间更长的唯一场景是，多个线程并发地使用CPU。注意：并不是所有的的并行查询都可以证明这个现象（也就是CPU time比持续时间要长）。

　　

select * from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time > qs.total_elapsed_time

　　SQL Trace

　　看一下下面的这些并行查询，这些查询的CPU time会比延迟时间多。

　　

select EventClass,TextData from ::fn_trace_gettable('C:\temp\high_cpu_trace.trc',default)
where EventClass in (10,12)   --PRC:Completed, SQL:BatchCompleted
and CPU>Duration/1000      --CPU is in millseconds


select EventClass,TextData from ::fn_trace_gettable('C:\temp\high_cpu_trace.trc',default)
where TextData like '%Parallelism%'  

解决方案

　　--任意执行并行计划的查询都是因为优化器识别出了这个查询的消耗高于了并行的阈值，这个阈值默认情况下是5（大致来说就是执行时间5秒）.任何被上面提到的方法识别出来的查询，都是可以进行更深层次优化的对象。

　　--使用Database Engine Tuning Advisor去查看是都存在索引的更改，发生在索引视图上的更改，或者分区的更改会减少查询的消耗。

　　--检查一下那些没有使用查询架构的查询，这些查询不能被优化器进行精确评估，比如多语句的表值函数或者CLR函数，表变量，或者跟表变量进行比对（跟参数进行比对是没有问题的）。

　　--评估一下现有的查询语句是否可以使用其他结构的高效查询语句替代。