某集团一台sqlserver服务器，据系统管理员反应，经常会出现CPU飙到100%而且高居不下的情况，然后应用层会出现各种等待超时。

服务器配置：windows

server 2008R2 x64 + 32GB内存 +6核CPU，业务量不算特别繁忙，按理CPU使用率不会很高。

首先确认CPU高是否确实是由SQLSERVER导致的，添加几个windows CPU计数器:Procesor:% Privileged

Time(kernel mode值)，Procesor: %user time (user mode值)，Procesor:

%processor time ,Process:Processor time(添加所有实例，查看sqlserver

cpu占用率)。结果%processor time 很高(正常平均应低于50%)，且sqlserver使用率占用绝大部分。

一般来说，sqlserver对

cpu的使用相对于内存、I/O等资源来说，应该算是较少的。CPU的使用主要是用于编译、重编译、sort、aggregation、表join(nested

loop,hash

match等)。可以通过profiler抓取一段trace，基本没有多少编译和重编译存在(profiler里cache

insert和cache miss基本没有)，那么很有可能是某些sql语句导致CPU消耗很高。再通过profiler抓取一些CPU

time比较高的sql语句，有时候CPU使用率到达90%时，使用profiler会使服务器负载更高，我们可以用下面sql抓取一段时间内cpu消耗比较高的语句：

SELECT TOP 10

execution_count as [Number Of Executions],

total_worker_time/execution_count as [Average CPU Time],

total_elapsed_time/execution_count as [Average Elapsed Time],

(

SELECT

SUBSTRING(text,statement_start_offset/2,

(CASE WHEN

statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max),text)) *2

ELSE statement_end_offset END -

statement_start_offset) /2)

FROM

sys.dm_exec_sql_text(sql_handle)

) as

query_text

FROM

sys.dm_exec_query_stats

ORDER BY [Average

CPU Time] DESC;

抓取到的语句结构基本一致，在业务非繁忙期把sql单独拉出来测一下，看一下cpu时间和io情况：

cpu时间很高，实际数据获取不到32s，cpu时间占用91s(触发了并行运算)，同时光某一张表的扫描达到400W次，其余的扫描我在此没截图；

再看看执行计划

cost最大的是在11行的clustered

scan，结合抓取到的CPU高的sql语句来看，发现cost最大的消耗全部都是基于某个筛选条件 ——summary like

'%某某某%'，问题原因找到了，那么为什么会出现以及怎么解决就是后面要做的了。

sql

server基于like的模糊查询很特别，像上述那种like'%%'，前后都有通配符的情况，不管该字段有没有索引，走的都是table

scan或clustered index

scan，效率非常低(该生产环境表数据量在1000W行左右，全表扫描性能消耗很大)！如果是只是后面通配符如 like

'abc%',且有合适索引是可以走index

seek的(实际生产环境会更复杂，有基于cost的算法有时也会做全表扫描)。解决办法只能从两个方面入手：1.创建合适的索引，更改模糊查询匹配为后匹配(经测试cpu时间会降低10-20倍)；2.创建全文索引，全文索引类似于搜索引擎功能，它不同于基于B+树或hash索引的普通索引，性能上优于模糊查询，不做详细介绍，下面是一个1万行小数据量利用模糊查询和全文索引的一个小测试，数据量越大性能差别越大：

模糊查询：

利用全文索引：

当然上述两种方式是比较理想的情况，比如说建立全文索引的表必须要有唯一键，否则全文索引也不可行；又比如应用层就是想用到前后置的模糊查询‘%***%’,那想做优化也无能为力。换个思路，从另一面也能达到同样的目的。

上述我们所说的几个昂贵的查询语句，都同时用到了并行查询，且在排查出这些情况的同时，发现系统等待事件上有大量的CXPACKET

CXPACKET等待产生机制：当为SQL查询创建一个并行操作时，会有多个线程去执行这个查询。每个查询处理不同的数据集或行集。因为某些原因，一个或多个线程滞后，而产生了CXPACKET等待状态。组织线程必须等待所有线程完成处理才能进行下一步。由于组织线程等待缓慢的线程完成处理所产生的等待，就叫CXPACKET等待。CXPACKET等待事件要辩证来看，并不是所有的CXPACKET等待类型都是不好的事情。

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

OLTP:

在纯OLTP系统上，它的事务较短，查询也不长，但是通常很快速。设置“Maximum degree of

Parallelism”(MAXDOP)为1，禁用并行查询，降低数据库引擎开销；

OLAP或DataWarehouse:在纯OLAP系统上，因为查询执行时间一般较长，建议设置“Maximum degree of

Parallelism”(MAXDOP)为0，由SQL server自动配置最大并行度，

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

OLAP&OLTP混合系统，这样的环境比较复杂，就像上面讲的那个案例，也是基于这种系统，我们必须找到“并行查询阀值”和“最大并行度”之间的平衡点。测试下来发现把并行查询阀值提升到18，且最大并行度设置为3，系统运行很好，既不影响运行速度又不会使CPU过高。基于这种设置，那么比较简单的查询不会启用并行查询，降低CPU开销，当预估cost大于18的比较复杂报表查询,那么会启用并行查询，提升查询速度，同时这意味着只会使用cpu的3个核(6核)来做并行运算，不会使cpu使用过高。这么一修改下来，cpu终于降下来了，没报表在跑的话基本在10%左右，有报表在跑也不会很高，在40%~50%左右徘徊。

EXEC sys.sp_configure N'show advanced options', N'1'

GO

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'cost threshold for parallelism', N'18'

GO

EXEC sys.sp_configure N'max degree of parallelism', N'3'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0'

GO

RECONFIGURE WITH OVERRIDE