[转]SQL Server 性能调优（cpu）

 

研究cpu压力工具

perfom

SQL跟踪

性能视图

cpu相关的wait event

Signal wait time

SOS_SCHEDULER_YIELD等待

CXPACKET等待

CMEMTHREAD等待

调度队列

cpu密集型查询

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

miss index

统计数据丢失

非SARG谓词

隐式类型转化

参数探测器

ad hoc 非参数化查询

修改源代码

强制性参数化

不合适的并发查询

cost threshold for parallelism

max degree of parallelism

超线程和并发查询

诊断不合适的并发查询

解决并发问题

TokenAndPermUserStore

总结

参考资料：

 

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) COLLATE Latin1_General_CI_AS PRIMARY KEY);
SELECT * 
FROM #T 
WHERE col1 = N'q'

-- SQL Collation will get a Scan
CREATE TABLE #T2 (col1 varchar(10) COLLATE SQL_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 NONCLUSTERED INDEX 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]<=[@ShipDateEnd]))
            |--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]))
                 |--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID],0),N'*** ERROR ***')))
                      |--Table Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader]))

这个是我的结果和书上的不一样。那么为什么为产生表扫描不是索引查找呢，因为第一个查询在编译的时候优化器任务用表扫描比较合适。但是到第二句的时候，虽然是不合适，但是已经有执行计划存储在了内存里面，sql server 就直接拿来用了，就照成了这个问题。开 SET STATISTICS IO on

表'SalesOrderHeader'。扫描计数1，逻辑读取700 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

那么我们把2个存储过程倒过来：

DBCC FREEPROCCACHE

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

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

情况就完全不一样了

       |--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1004]) WITH UNORDERED PREFETCH)
                 |--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[IDX_ShipDate_ASC]), SEEK:([AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate] >= [@ShipDateStart] AND [AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate] <= [@ShipDateEnd]) ORDERED FORWARD)
                 |--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID],0),N'*** ERROR ***')))
                      |--RID Lookup(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

果断使用了索引查找，但是对第二句来说索引查找不一定是好事情，因为他要扫描的行太多，如果假定现在树是3层，那么读一个页需要读3次你想想。

表'SalesOrderHeader'。扫描计数1，逻辑读取17155 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

比较一下夸多年的那个存储过程的逻辑读。

通常keylookup只适合较少的数据通常是整表的1%，当然不是绝对的。

跟踪标记4136

SQL Server 2008 引入了一个新的跟踪标记 4316，使用了这个跟踪标记之后sql server 会关掉参数探测功能，这个功能在sql server 2008 sp2 cu7 ,sql server 2--8 r2 cu2,sql server 2005 sp3 cu9 中才加入。先前讨论过了如果开了参数探测，一个存储过程如果第一次编译的时候估计值偏小，或者偏大，都会对接下来使用这个存储过程产生影响。当参数探测器被停用的时候 4316 跟踪是如何处理的呢，举个例子这里又一个列 X 有如下的值1,2,3,3,3,3,3,4,5,5，那么他的估计值就是2这个哪来的？就是所有数据的平均估计值。所有的计划都会被这个值优化。如果开了这个选项那么会给很多存储过程优化带来好处。

这边有篇关于4316的文章比较简单但是很到位：http://blogs.msdn.com/b/axperf/archive/2010/05/07/important-sql-server-change-parameter-sniffing-and-plan-caching.aspx

使用 OPTIMIZE FOR 提示

到了sql server 2005 以后你可以使用OPTIMIZE FOR 来优化查询

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

AS

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( OPTIMIZE FOR ( @ShipDateStart = '2001/07/08',

@ShipDateEnd = '2004/01/01' ) )

GO

使用了OPTIMIZE FOR 提示那么sql server 就会按提示的信息来编译，当然如果提示的值不理想那么也会产生问题。

在SQL Server 2008 中引入了一个新的提示 OPTIMIZE FOR UNKNOWN，那么sql server 就不会再用参数探测的功能，它的功效和4316相同，所以这个方法是比较可取的因为毕竟参数探测还是一个比较好的东西。

重编译选项

重编译也是解决参数探测的一个方法，但是问题就是执行计划不会被保存在内存中，但是就有一个问题存储过程的执行的花费就会变高。

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

WITH RECOMPILE

AS

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

GO

如果存储过程中只需要一部分重新编译，那么就可以使用OPTION(RECOMPILE)选项放到查询中即可，相比重编译整个存储过程，这样会好些。

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

AS

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( RECOMPILE )

GO

 

ad hoc 非参数化查询

Ad hoc查询语句发送到sql server 的时候优化器还是会从cache查找合适的执行计划。ad hoc 查询会让所有的语句都生产一遍执行计划，这样会照成资源浪费特别是CPU。

SELECT soh.SalesOrderNumber ,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = 'SO43662'

SELECT soh.SalesOrderNumber ,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = 'SO58928'

SELECT soh.SalesOrderNumber ,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = 'SO70907'

很不幸，这三个语句本来是应该可以用同一个执行计划的。现在因为ad hoc 用不了了。如果是简单的查询那么sql server 会使用简单参数化来重用执行计划。但是上面的例子太复杂了所以没办法。那就会有2个问题

1.执行计划缓存充满了单用户的计划，不能被重用。浪费内存空间。

2.执行计划因为不可用所以总是要编译新的计划，导致cpu时钟浪费。

可以用perfmon来监视编译重编译的量

• SQLServer: SQL Statistics: SQL Compilations/Sec

• SQLServer: SQL Statistics: Auto-Param Attempts/Sec

• SQLServer: SQL Statistics: Failed Auto-Param/Sec

如果真的是非参数化照成的问题，那么又很多方法去调整，最好的方式是修改源代码。如果不行那么只能设置sql server 来调整

修改源代码

关于修改源代码就不讨论了，直接给demo自己看。

cmd.CommandType = CommandType.Text;

cmd.CommandText = @"SELECT soh.SalesOrderNumber,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = '" + txtSalesOrderNo.Text + "'";

dtrSalesOrders = cmd.ExecuteReader();

dtrSalesOrders.Close();

cmd.CommandType = CommandType.Text;

cmd.CommandText = @"SELECT soh.SalesOrderNumber,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = @SalesOrderNo";

cmd.Parameters.Add("@SalesOrderNo", SqlDbType.NVarChar, 50);

cmd.Parameters["@SalesOrderNo"].Value = txtSalesOrderNo.Text;

dtrSalesOrders = cmd.ExecuteReader();

强制性参数化

关于强制参数化，可以设置数据库选项

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

如果使用强制参数化那么上面我们提过的3个sql的执行计划就变成一个了。可以使用如下sql查询

SELECT b.text,c.* FROM sys.dm_exec_query_stats   a

      CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

      CROSS APPLY  sys.dm_exec_query_plan(a.plan_handle) c  

使用强制参数化很不好，就会使得所有的sql都使用同一个查询计划，不管好坏，有点和参数探测器的问题类似了。

Optimize for ad hoc workloads

这是一个数据库服务配置项，配置了之后当ad hoc第一次运行的时候sql server 会产生一个子查询计划不能用，当第二次执行的时候产生一个执行计划。可以有效的减少内存压力。

EXEC sp_configure 'show advanced options',1

RECONFIGURE

EXEC sp_configure 'optimize for ad hoc workloads',1

RECONFIGURE

 

不合适的并发查询

当查询在不同的线程，每个线程在不同的调度器下运行，就可以理解为并发查询。

当一个查询被提交到sql server 优化器，优化器开始估算花费，如果花费比cost threshold for parallelism 要大，那么优化器会考虑使用并发。max degree of parallelism 用来限制查询的最大并发数如果查询中使用了maxdop提示的话那么最大并发数则为提示的值。并发查询通过把数据水平分区到各个不同的逻辑cpu，通过多个处理器内核执行相同的操作来减少查询的时间。这个对于dw或者报表数据库是很有用的因为数据量很大，而且并发请求比较少。所以能够充分的利用硬件资源，并且减少执行的时间。对于并发的负载还是又一些要素，并不是指余下的设备资源能否应付并发负载带来的大内存分配和磁盘io的问题。并发查询使用的好会给服务器的整体性能带来很大的提升，但是并发负载对oltp系统来说是非常不利的，oltp是又很多小的事务组成，并发量比较大，如果oltp上有并发负载，占据了较长时间的cpu，那么其他事务就会等待并发的完成，导致查询假死在那边。

对于并发的配置参数有2个cost threshold for parallelism ，max degree of parallelism 第一个是启用并发查询的阀值，第二个是最大并发数。当发生不合适的并发的时候，建议的解决方法是调整max degree of parallelism，减少1/2，或者减少1/4或者直接设置为1。当然这个是不理想的解决方案，最理想的解决方案是设置2个配置参数，到一个比较合理的值。

cost threshold for parallelism 

cost threshold for parallelism 是一个启用并发的阀值，查过了就启用并发，没超过就不启用。cost threshold for parallelism 的默认值是5秒，但是对于大数据库5秒是一个比较小的值，因此设置cost threshold for parallelism 阀值很重要

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

WITH XMLNAMESPACES

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT query_plan AS CompleteQueryPlan ,

n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText ,

n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)')

AS StatementOptimizationLevel ,

n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')

AS StatementSubTreeCost ,

n.query('.') AS ParallelSubTreeXML ,

ecp.usecounts ,

ecp.size_in_bytes

FROM sys.dm_exec_cached_plans AS ecp

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp

CROSS APPLY query_plan.nodes

('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')

AS qn ( n )

WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

所以通过以上查询，分析相似的查询。以最小化cpu，io竞争为目标设置cost threshold for parallelism。

max degree of parallelism

sql server 并发查询的并发度有以下3点：

1.可用的处理器数量

2.max degree of parallelism

3.MAXDOP查询提示

如果你的服务器现在出现了并发问题那么修改阀值和最大并发度是解决这个问的最快速的方法。

网上有种说法就是直接把max degree of parallelism设为1，对于oltp系统的特性是可能性的，但是还是觉得你这样设置之后就不能使用并发了，感觉会减少性能。

分析CXPACKETwait event，CXPACKET只是一种症状，并没有真正的发生问题。查看sys.dm_os_waiting_tasks中其他的wait event可以更好的得出合适的 max degree of parallelism。如果相关的等待事件是 PAGEIOLATCH_SH，并发正在等待io读取，减少max degree of parallelism 并不能解决根本问题，它只会减小被使用的工作任务，减少CXPACKET累计等待时间。但是也可能会减少额外的io，给你提示io性能的空间。

并发查询也需要考虑到内存的结构体系，在NUMA结构下，最大并发度设置在一个NUMA节点的可用经常。这样node之间就不会产生交互，因为node间的共享内存操作代价很高。在SMP结构中，多个处理器内核都在单个芯片上共享二级缓存，这样很容易照成内存命中率下降，但是好处是在并发查询下高并发的性能表现很好，当然max degree of parallelism 也要根据硬件设备的能力做适当的调节。在sql server 2008 以上的版本还可以使用资源管理器来限制。

超线程和并发查询

超线程是Intel一个技术，为了提高并发操作，就设计了2个逻辑内核对于1个物理内核。就是说不想以前一个调度器一个物理内核，现在2个内核，并且可以“同时”使用。当然我们关心的是性能，那么sql server 有没有使用超线程，会给sql server 带来什么影响。

对于olap和dss系统并发查询是又很大好处的，但是当开了超线程的时候性能就变差了。但是超线程对oltp没什么影响，对于oltp来说超线程在增加并发度是又好处的。对于早期的超线程因为会带来很多问题所以dba都是在bios中关闭超线程的。近几年sql server 2008 发布了建议关闭超线程特别是olap/dw/dss系统。超线程最大的问题是超线程会共享内置的cache，照成命中率下降。现在很多问题都解决了，windows 2003 就能认识物理内核和逻辑内核，并且给予不同的工作量。现在的处理器缓存变大不容易发生。事实上对于当前的处理器结构，特别是intel nehalem，开超线程是有好处的，除非是有明确的理由。所以在决定是否使用超线程的时候最好先做一下测试。

诊断不合适的并发查询

最好诊断的方法是查看wait统计信息和latch统计信息，当执行并发的时候出现瓶颈，CXPACKET等待就会变的很高。当并发查询等待交换迭代器到另外一个工作任务的时候就会发生等待。通常这里也会有一些相关的其他等待，来协助工作，因为大量的并发查询，CXPACKET的等待会比根本原因盖过去。最好的方法是分隔在troubleshooting各个相关的等待时间。因为并发查询会影响全局的性能问题。CXPACKET很有可能只是一个症状很多问题都会引起CXPACKET偏高。当io不能维持并发查询的需求，关键的等待可能是IO_COMPLETION,ASYNC_IO_COMPLETION,PAGEIOLATCH_*,不能扩展io性能。但是减小并发度，任然会发生io性能瓶颈的状况，那么就要提从全局的系统性能。如果CXPACKE相关的等待是LATCH_*,SOS_SCHEDULER_YIELD,那么很有可能是并发的问题，深入latch验证是并发的问题。sys.dm_os_latch_stats包含一些特殊的latch等待，如ACCESS_METHODS_DATASET_PARENT,LATCH_*,SOS_SCHEDULER_YIELD等待都比较高，那么减少并发度就可能解决问题。

解决并发问题

先前已经讨论过，对于大的，长运行时间的查询使用并发很有好处。不合适的并发主要问题是负载类型是混合的。很多库本质上是oltp的但是因为sql比较复杂超过了cost threshold for parallelism。所以试图提升一下cpu性能。如果诊断到了并发存在问题，如果没有被调整过，那么很有可能因为索引丢失或者不合适的索引造成问题，如果调整完之后还是这样那么就用先前提到的2个系统配置参数，来全局的管理数据库并发。

TokenAndPermUserStore

TokenAndPermUserStore在2005的时候被引进来优化关于权限验证，怎么TokenAndPermUserStore是怎么工作的呢？这里有一个简单的例子说明TokenAndPermUserStore的工作情况。例子当你执行的时候select * from t1 join t2 join t3，那么sql sever 就会对权限进行验证，验证后会缓存在TokenAndPermUserStore以免以后重复验证。但是这个会引起性能问题，特别是较早版本的sql server 2005，因为这个cache的内存限制过高性能问题的表现为cpu使用率比较高，cmemthread等待比较严重。微软已经给出了一个解决方案http://support.microsoft.com/kb/927396/通常问题发生在非awe内存分配的sql server 上（特别是64b的服务器），很多动态的或者 adhoc查询，数据库用户过多。你可以使用如下sql查询TokenAndPermUserStore使用量：

SELECT SUM(single_pages_kb + multi_pages_kb) / 1024.0 AS CacheSizeMB

FROM sys.dm_os_memory_clerks

WHERE [name] = 'TokenAndPermUserStore'

如果cache一直增长，并且伴随着cmemthread等待，那么很有可能导致高cpu使用率，如果使用sql server2005低于sp2补丁，那么第一时间就是打上补丁。嫌少动态sql和adhoc来减少发生问题的概率。

短期修复

使用sysadmin角色，因为sysadmin是sql server 最大的权限，不需要做权限检查。那么也就不会产生cache

定期清理cache：DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

在sql 2005 sp2 以上版本使用 trace flage 4618,4610来限制cache中的条目数量，当4618开启，cache中只能有1024个cache，当2个trace flag 都开启那么又8192个条目。这个限制会影响其他cache，因此只能临时使用。sql server 2005 sp3以后有个新的trace flag 4612，可以设置客户端的配额详细看：(http://support.microsoft.com/kb/959823)

sql2008的配置项

在sql server 2008 对于TokenAndPermUserStore有2个配置项，access check cache quota，access check cache bucket count,如果问题很明显的发生，那么就减少这2个值的大小，其实并不建议修改默认值，除非又微软客服支持。

 

总结

troubleshooting是一个分析问题的过程，我上一篇文章也说了，是一个根据统计的信息，分析问题的过程。因此需要了解数据库内核，内部运行的结构才能更好的进行调优。调优第一步的信息往往都是来至于perfmon，和动态性能视图，最后才是sqltrace，为啥，因为sqltrace最浪费时间，会有滞后性，所以已经滞后了还不如放到最后运行。

 

参考资料：

 Implicit data conversations
•http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/findingimplicit-column-conversions-in-the-plan-cache.aspx
 Query tuning
• http://www.straightpathsql.com/presentations/ucandoit/
• http://www.simple-talk.com/sql/performance/simple-query-tuning-with-statistics-io-and-execution-plans/

•http://www.simple-talk.com/sql/t-sql-programming/13-things-youshould-know-about-statistics-and-the-query-optimizer/
• http://www.simple-talk.com/author/gail-shaw/
 Estimated vs. actual row counts
• http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/
 Cost threshold for parallelism
• http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/26/21172.aspx
• Max degree of parallelism
• http://msdn.microsoft.com/en-us/library/ms181007.aspx
 Query hints
• http://msdn.microsoft.com/en-us/library/ms181714.aspx
 Guidelines for modifying MAXDOP
• http://support.microsoft.com/kb/329204
 Limiting MAXDOP with the Resource Governor
•http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/controlling-maxdop-executing-queries-140163
 Parallelism/MAXDOP configuration
• http://msdn.microsoft.com/en-us/library/ms178065.aspx
• http://msdn.microsoft.com/en-us/library/ms188611.aspx
• http://blogs.msdn.com/b/joesack/archive/2009/03/18/should-you-worryabout-sos-scheduler-yield.aspx

 SQLOS architecture
• http://blogs.msdn.com/b/sqlosteam/archive/2010/06/23/sqlos-resources.aspx
•http://sqlblogcasts.com/blogs/sqlworkshops/archive/2007/11/25/findingoptimal-number-of-cpus-for-a-given-long-running-cpu-intensive-dss-olaplike-queries-workload.aspx
 System Monitor CPU counters
• http://msdn.microsoft.com/en-us/library/ms178072.aspx
 DMV usage for CPU usage from ring buffers
•http://troubleshootingsql.com/2009/12/30/how-to-find-out-the-cpuusage-information-for-the-sql-server-process-using-ring-buffers/
• http://msdn.microsoft.com/en-us/library/ms175048(SQL.90).aspx
• http://technet.microsoft.com/en-us/library/cc966540.aspx
 Forced parameterization
• http://technet.microsoft.com/en-us/library/ms175037(SQL.90).aspx
 Fixing TokenAndPermUserStore problems Identification and overview
• http://support.microsoft.com/kb/927396
 Access check result cache
• http://support.microsoft.com/kb/955644
• http://msdn.microsoft.com/en-us/library/cc645588.aspx
• Purging the cache whenever it reaches a certain size
• http://blogs.msdn.com/chrissk/archive/2008/06/19/script-to-purgetokenandpermuserstore.aspx

 SQL Server 2008 sp_configure options
• http://support.microsoft.com/kb/955644/en-us
• Hot-fixes associated with this problem
• http://support.microsoft.com/kb/959823