SQL Server 性能调优（cpu）

什么没有

　　

研究cpu压力工具... 2
perfom.. 2
SQL跟踪... 2
性能视图... 2
cpu相关的wait event. 2
Signal wait time. 3
SOS_SCHEDULER_YIELD等待... 3
CXPACKET等待... 3
CMEMTHREAD等待... 3
调度队列... 4
cpu密集型查询... 4
高CPU使用率的创建几种状况... 5
miss index. 5
统计数据丢失... 6
非SARG谓词... 6
隐式类型转化... 6
参数探测器... 7
ad hoc 非参数化查询... 10
修改源代码... 11
强制性参数化... 12
不合适的并发查询... 12
cost threshold for parallelism.. 12
max degree of parallelism.. 13
超线程和并发查询... 13
诊断不合适的并发查询... 14
解决并发问题... 14
TokenAndPermUserStore. 14
总结... 15
参考资料：... 15

cpu在sql server 中扮演了很重要的角色，虽然cpu绑定的服务器排除cpu问题相对比较简单，但并不意味着总是简单。如果你的1个或多个cpu满负荷运行，那么就要小心了。sql server 对cpu的使用无处不在，所以如果cpu满负荷运行，那么问题很严重。

cpu性能出现问题，一般很慢盘查为啥，因为会照成cpu性能问题的很多，如内存不足，数据换进换出，cpu一路飙高。写操作性能很烂，索引建的不合适，sql server 配置等问题都会引起cpu过高的问题。所以cpu性能盘查需要很小心和仔细。

不管是什么问题引发的，对cpu的性能分析就是把问题隔离到一个特定资源，我们可以使用perfmon，性能视图，还有sql跟踪来收集资源。

一旦发生问题，我们就要把问题锁定在一个或多个查询上，对其进行调整如调整cpu密集型的查询，添加合适的索引，使用存储过程替换ad hoc查询等等。

研究cpu压力工具
perfom

对于cpu压力的研究我们一般使用一下工具：perfmon，SQL跟踪，动态性能视图

perfmon我们可以跟踪如下性能指标：

Processor/ %Privileged Time                          --内核级别的cpu使用率

Processor/ %User Time                                   --用户几倍的cpu使用率

Process (sqlservr.exe)/ %Processor Time    --某个进程的cpu使用率

上面3个性能指标是全局范围的，SQL Satatistics 计数器虽然不能直接说明cpu的使用率但是可以间接的说明cpu的使用情况。

· SQLServer:SQL Statistics/Auto-Param Attempts/sec

· SQLServer:SQL Statistics/Failed Auto-params/sec

· SQLServer:SQL Statistics/Batch Requests/sec

· SQLServer:SQL Statistics/SQL Compilations/sec

· SQLServer:SQL Statistics/SQL Re-Compilations/sec

· SQLServer:Plan Cache/Cache hit Ratio

这些计数器没有额定的阀值，需要和性能基线做对比

SQL跟踪

SQL跟踪的具体用法就不多讲，很多人都已经会用了，SQL跟踪在某个时间点上的捕获远远不如动态性能视图，而且捕获的时候要注意设置过滤不然会捕获大量无用的sql。

性能视图

性能视图是分析的利器：

验证cpu压力的wait event 可以使用 sys.dm_os_wait_stats.

通过sys.dm_os_wait_stats和 sys.dm_os_schedulers，通过wait event 类型诊断。

可以用sys.dm_exec_query_stats和sys.dm_exec_sql_text说明使用大量cpu的执行计划

可以使用sys.dm_os_waiting_task查看cpu相关的等待类型

通过sys.dm_exec_requests查看当前正在的查询的资源使用情况

cpu相关的wait event

sql server 所有的等待信息，都会被记录。可以使用sys.dm_os_wait_stats中查看。这个视图可以用来确定cpu压力，查看cpu绑定系统中大多数的wait event。

Signal wait time

根据特定的等待类型(wait type)，有一些等待时间：

wait_time_ms该等待类型所有等待时间。

signal_wait_time_ms从发出信号到开始运行的时间差，时间花费在等待运行队列中，是单纯的cpu等待。

signal_wait_time_ms是所有等待时间的一个重要部分，说明了等待一个可用资源的等待时间。可以表示sql server 中是否正在运行cpu密集型查询。

下面代码量化的像是signal_wait_time_ms占的比重

SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

这个dmv记录了统计信息，系统重启之后会被情况，所以如果查看某一时间点情况不是很好用，只能用临近的统计相减，也可以用 dbcc sqlperf清空统计信息。

关于session级和语句级的wait event 可以查看文章：http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/30/an-xevent-a-day-30-of-31-tracking-session-and-statement-level-waits.aspx

我们可以使用sys.dm_os_wait_stats查看那个资源等待时间最长。top 10 用等待时间排序，但是这样就容易忽略一开始的等待也就是signal wait time，因此要减去signal_wait_time，作为等待调度器的时间。

下面讨论三个wait type 这三个和cpu压力息息相关。

SOS_SCHEDULER_YIELD等待

sql server 调度器是非抢占式调度，也就是说是依靠查询自动放弃cpu，但是windows是抢占式，也就是说一定时间之后，windows 会直接从cpu上删除任务。

当查询自动放弃cpu，并且等待恢复执行，这个等待就叫做SOS_SCHEDULER_YIELD,如果这个值很小那么就说明，花费在等待cpu上，而不是等待其他资源上。

如果sys.dm_exec_requests或者 sys.dm_os_waiting_tasks 的SOS_SCHEDULER_YIELD的等待值偏高，那么说明有cpu密集型查询，需要优化sql或者增加cpu。

CXPACKET等待

当同步查询进程，worker之间交换迭代器的时候发生CXPACKET等待，特别是发生并发查询的时候。如果是在dw,或者是报表数据库，那么发生sql比较少，并且有大量的并发查询可以减少执行时间。对dw来说是正常的，但是在oltp中大多数是小的sql和事务，如果发生大量的并发，会导致性能下降。

CMEMTHREAD等待

CMEMTHREAD等待就是等待被同步的内存对象。有一些对象支持查询同时访问，有些不支持。当一个查询访问一个对象时，其他查询就必须等待，这就是CMEMTHREAD等待。

通常CMEMTHREAD等待不会很长时间。但是当内存出现问题后，cpu利用率和CMEMTHREAD等待都会变高，这是性能比较差的查询引起的。

调度队列

关于调度队列最主要的视图就是sys.dm_os_schedulers，视图主要的二个指标之一是每个调度器有几个task，和可运行队列的长度。可运行队列内都是等待cpu时钟的task，其他的task在current_tasks_count内，都处于sleep或者在等待其他资源。

SELECT scheduler_id ,

current_tasks_count ,

runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

这些值没有固定的阀值，只能通过性能基线来对比。当然这些值都是越低越好。如果可运行队列越长那么，signal time 的时间也就越长，就意味着可能cpu不足。

上面的sql过滤掉了一些 scheduler 因为其他的是backup，dac等调度器。

cpu密集型查询

关于cpu密集型查询，有2个性能视图，sys.dm_exec_query_stats和sys.dm_exec_sql_text。sys.dm_exec_query_stats统计了每个查询计划的各类信息。如*_worker_time：cpu花费的时间。*_elapsed_time:总共运行的时间。

下面的sql统计了前10个最费时间的查询：

SELECT TOP ( 10 )

SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,

( ( CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE QS.statement_end_offset

END - QS.statement_start_offset ) / 2 ) + 1)

AS statement_text ,

execution_count ,

total_worker_time / 1000 AS total_worker_time_ms ,

( total_worker_time / 1000 ) / execution_count

AS avg_worker_time_ms ,

total_logical_reads ,

total_logical_reads / execution_count AS avg_logical_reads ,

total_elapsed_time / 1000 AS total_elapsed_time_ms ,

( total_elapsed_time / 1000 ) / execution_count

AS avg_elapsed_time_ms ,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY total_worker_time DESC

这个查询并不会显示所有的query，执行计划是被保存在cache中的，当cache被淘汰，因为dbcc命令没清理，数据库状态发生变化，数据库配置发生变化等等，都会引起cache丢失的情况。有一些查询使用了recompile标示或者提示那就永远不会被保留在cache中。

如果你要全局的分析执行计划，请使用sql跟踪，而不要事情清空缓存，特别是在生产库中，缓存一旦被清空在一点时间内，讲严重影响性能。

高CPU使用率的创建几种状况

不管在服务器硬件配置和技术上面花了多大的成本，总有怎么一些查询会导致服务器的资源满负荷运行。每个sql被执行的时候，sql server优化器终会找一个尽量高效的方式来获取数据。如果当一个查询miss index或者忽略了合适的索引，那么优化器就无法生存一个真正高效的执行计划。如果优化器相关的信息是不准确的，那么优化器生存的执行计划也是不准备的，因为关于成本的计算也是不准确的。另外一种状况就是优化器生存的结果对一个查询是优化的，但是对其他查询并不优化。因为不合适的参数探测导致了这个问题。

miss index

miss index 是照成大量cpu和io使用的状况之一，也是最常发生的状况。当前的索引并不能满足查询的时候，优化器会试图是用表扫描来完成，这样就照成了大量的非必须的数据参与到预算中，会照成cpu和io的极大浪费。那么我们就以 adventureworks2008 数据库作为例子

SELECT per.FirstName ,

per.LastName ,

p.Name ,

p.ProductNumber ,

OrderDate ,

LineTotal ,

soh.TotalDue

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail sod

ON soh.SalesOrderID = sod.SalesOrderID

INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID

INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID

INNER JOIN Person.Person AS per

ON c.PersonID = per.BusinessEntityID

WHERE LineTotal > 25000

这个查询在salesorderdetail使用了表扫描，因为并没有关于linetotal列的索引

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 452 ms, elapsed time = 458 ms.

虽然返回24行只用了半秒的时间但是还是不够优化。那么我们就在linetotal建一个索引

CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_LineTotal

ON Sales.SalesOrderDetail (LineTotal)

那么我们继续运行上面的sql

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 8 ms.

结果有很大的不通，通过这个简单的例子说明cpu的压力有可能且很大的可能都是miss index 照成的。

统计数据丢失

优化器会通过统计信息估计每个查询操作的基数。通过估计行数，操作的花费。操作的花费决定了整个计划的花费。如果统计信息不准确，那么优化器的成本计算也就不准确，这样就会导致优化器误判，估计的花费是低的但是并不一定实际的花费也是低的。通常统计值不准确是比实际值要小，一旦小，那么优化器就会选择比较适合较小数量的操作符如nest loop，key lookup，但是实际的数据量很大，这样就会对查询照成严重的影响。有一个方法查看统计值是否丢失，就是在ssms中运行实际的查询计划，并且对比估计值和实际值的差距，如果差距很大那么就是统计数据丢失了，需要及时更新统计值。当然可以通过 update statistics 更新统计值，详细的用法可以参见联机文档。

如果是统计值过期的问题照成的那么有一下3个方法：

1.把数据库设置为自动更新统计值。

2.如果自动更新统计信息无效，那么有可能是索引建立的时候有不计算统计值的标记。

3.创建一个脚本定时更新统计值。

非SARG谓词

就是不要再表的字段上使用函数或者计算，因为你一用，就没办法使用索引了。一不能使用索引，显而易见cpu飙高了，io堵塞了。

隐式类型转化

很多人都认为隐式转化没什么关系，并不会给性能带来多大的冲击。一个过滤如果类型不同那么sql server 是无法比较的，这时候就要隐式转化了，隐式转化的时候都是从低的优先级转化到高的优先级，比如如果一个是varchar一个是nvarchar那么就会把varchar隐式转化成nvarchar。问题就来了如果一个表列是varchar但是过滤的条件是nvarchar，那么就会隐式转化把varchar转化成为nvarchar那么就会发生非SARG谓词，无法使用索引查找了。下面有个例子：

SELECT p.FirstName ,

p.LastName ,

c.AccountNumber

FROM Sales.Customer AS c

INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID

WHERE AccountNumber = N'AW00029594'

当然 accountnumber 上是有索引的

就变成索引扫描了，我使用2008r2测试，结果不是索引扫描。但是当我把accountnumber 禁用掉之后，尽然和书上发的执行计划是一样的了，让我深深的怀疑，是不是作者在写书的时候，把accountnumber 禁用了而没发现呢？我在网上查了写资料，发现了在sql server 2000下的测试语句ok，在2000 下面是会照成索引扫描。所以大家如果用2008r2的就不需要太担心这个问题。如果在其他版本真的遇到这个问题那么如何解决呢？那么就把类型转化放在常量这一端。或者直接修改表的数据类型。

我把2000的测试语句发出来：

DECLARE @CustID NCHAR(5)

SET @CustID = N'FOLKO'

SELECT CompanyName FROM NorthWind.dbo.Customers WHERE CustomerID = @CustID

这里要注意因为 customers 表的结构是 nchar的所以我们在测试的时候先要修改掉这个数据类型，改为char。northwind里面有外键要统统删掉，主键需要重建。

说到这里，我就和书的作者联系了，根据他给的结论，和测试结果

-- Windows Collation will get a Seek
CREATE TABLE #T (col1 varchar(10) COLLATELatin1_General_CI_AS PRIMARY KEY);
SELECT *
FROM #T
WHERE col1 = N'q'

-- SQL Collation will get a Scan
CREATE TABLE #T2 (col1 varchar(10) COLLATESQL_Latin1_General_CP1_CI_AI PRIMARY KEY);
SELECT *
FROM #T2
WHERE col1 = N'q'

-- Your Collation will get a Seek
CREATE TABLE #T3 (col1 varchar(10) COLLATE Chinese_PRC_CI_AS PRIMARY KEY);
SELECT *
FROM #T3
WHERE col1 = N'q'

DROP TABLE #T
DROP TABLE #T2
DROP TABLE #T3

当你用SQL Server 的排序规则那么就是扫描如果用windows 的排序规则那么就是查询。

上面就是他发过来的sample

参数探测器

当sql server为存储过程，函数或者参数化查询创建执行计划的时候，会探测参数，并结合统计数据计算花费选择较好的执行计划。参数探测器只会在编译或者重编译的时候发生，那么这里就有个问题如果当创建执行计划的时候该参数的值是非典型的，那么就很可能并不适用于以后传过来的参数。初始化编译的时候，只有输入的参数会被探测，本地变量是不会被探测的。如果一个语句在一个batch 中被重编译那么参数和变量都会被探测。

下面是一个运行在Adventureworks数据库的例子最大日期是2011-7-8 最小日期是2004-8-7.

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

AS

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

GO

会对shipdate进行过滤那么就在shipdate设置一个索引

CREATE NONCLUSTEREDINDEX IDX_ShipDate_ASC

ON Sales.SalesOrderHeader (ShipDate)

GO

接下来会运行2次这个存储过程第一次夸多年的，第二次就夸几天。并查看实际的执行计划

DBCC FREEPROCCACHE

EXEC user_GetCustomerShipDates '2001/07/08', '2004/01/01'

EXEC user_GetCustomerShipDates '2001/07/10', '2001/07/20'

查询结果2个都用了扫描

     |--Filter(WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate]>=[@ShipDateStart]AND [AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate]= [@ShipDateStart]AND [AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate]