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) 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

 

相关文章