Troubleshooting SQL ServerCPU使用过高.docx
- 文档编号:7529044
- 上传时间:2023-01-24
- 格式:DOCX
- 页数:17
- 大小:98.14KB
Troubleshooting SQL ServerCPU使用过高.docx
《Troubleshooting SQL ServerCPU使用过高.docx》由会员分享,可在线阅读,更多相关《Troubleshooting SQL ServerCPU使用过高.docx(17页珍藏版)》请在冰豆网上搜索。
TroubleshootingSQLServerCPU使用过高
《TroubleshootingSQLServer》读书笔记-CPU使用率过高
CPU使用率过高问题很容易被发现,但是诊断却不是很容易。
CPU使用过高很多时候会成为其它问题的替罪羊,所以在确认和故障诊断时要抽丝剥茧。
调查CPU压力
三个主要的工具:
性能监视器,SQLTrace,DMV.
性能监视器:
首先用它来确认是SQLServer还是其它进程使用了过多的CPU。
主要计数器有:
Processor/%PrivilegedTime:
在特权模式下进程线程执行代码所花时间的百分比。
基本可以认为是Windows核心使用的CPU
Processor/%UserTime:
处理器处于用户模式的时间百分比。
应用程序的使用的CPU。
Process(sqlservr.exe)/%ProcessorTime:
SQLServer.exe线程使用处理器执行指令所花的时间百分比。
还有一些与SQLServer相关CPU消耗的计数器:
SQLServer:
SQLStatistics/Auto-ParamAttempts/sec
SQLServer:
SQLStatistics/FailedAuto-params/sec
SQLServer:
SQLStatistics/BatchRequests/sec
SQLServer:
SQLStatistics/SQLCompilations/sec
SQLServer:
SQLStatistics/SQLRe-Compilations/sec
SQLServer:
PlanCache/CachehitRatio
SQLTrace:
通过Profiler生成SQLTrace脚本,进行服务器端跟踪,来获得高CPU使用时详细信息。
DMV:
a.使用sys.dm_os_wait_stats来得到signalwait,确认CPU压力的程度.
b.使用sys.dm_os_wait_stats和sys.dm_os_schedulers观察等待类型
c.使用sys.dm_exec_query_stats和sys.dm_exec_sql_text找出高CPU使用的执行计划和对应的查询
d.使用sys.dm_os_waiting_tasks观察当前与CPU使用相关等待类型
e.使用sys.dm_exec_requests正在执行的查询的资源使用状况
调查CPU相关的等待统计:
请求执行前,包含请求的会话必需等待,SQLServer会记录等待原因和时间。
通过sys.dm_os_wait_stats查询这些信息。
信号等待时间(Signalwaittime):
sys.dm_os_wait_stats的wait_time_ms表示等待类型的总共等待时间,signal_wait_time_ms表示线程收到段义和到重新执行间的等待时间,
这些时间主要花在runnable队列里,是纯CPU等待。
通过以下查询得到信号等待的时间比率:
SELECTSUM(signal_wait_time_ms)ASTotalSignalWaitTime,
(SUM(CAST(signal_wait_time_msASNUMERIC(20,2)))
/SUM(CAST(wait_time_msASNUMERIC(20,2)))*100)
ASPercentageSignalWaitsOfTotalTime
FROMsys.dm_os_wait_stats
也可以查询各类资源等待的比率,下面是等待top10:
SELECTTOP(10)
wait_type,
waiting_tasks_count,
(wait_time_ms-signal_wait_time_ms)ASresource_wait_time,
max_wait_time_ms,
CASEwaiting_tasks_count
WHEN0THEN0
ELSEwait_time_ms/waiting_tasks_count
ENDASavg_wait_time
FROMsys.dm_os_wait_stats
WHEREwait_typeNOTLIKE'%SLEEP%'--removeeg.SLEEP_TASKand
--LAZYWRITER_SLEEPwaits
ANDwait_typeNOTLIKE'XE%'
ANDwait_typeNOTIN--removesystemwaits
('KSOURCE_WAKEUP','BROKER_TASK_STOP','FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT','BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS','BROKER_TRANSMITTER','CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE','SQLTRACE_BUFFER_FLUSH','CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR','PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS','BROKER_TO_FLUSH')
ORDERBYwait_time_msDESC
与CPU相关的等待类型主要有SOS_SCHEDULER_YIELD,CXPACKET和CMEMTHREAD
SOS_SCHEDULER_YIELD:
SQLServer计划程序是协同的多任务计划程序。
查询占用一小段时间的CPU后自发地让出CPU给后面的查询,
并且回到可运行队列等待重新被运行,这种等待就是SOS_SCHEDULER_YIELD。
如果此等待时间在sys.dm_exec_requests或者sys.dm_os_waiting_tasks过多,则表示有高CPU使用的查询需要优化或者需要增加CPU。
CXPACKET:
多处理器运行并行查询时,当同步多个线程间的查询处理器交换迭代器时出现。
CMEMTHREAD:
等待同步内存对象。
有些内存对象是不请允许并发访问的,当多个线程试图访问此内存对象时,就会等待。
调查计划程序队列(schedulerqueues):
scheduler_id<255的是隐藏的系统计划程序,如DAC,备份等。
SELECTscheduler_id,
current_tasks_count,
runnable_tasks_count
FROMsys.dm_os_schedulers
WHEREscheduler_id<255
current_task_count表示每个计划程序上的任务数,runnable_task_count表示runnable队列中等待CPU的任务。
找出高CPU消耗的查询
主要使用sys.dm_exec_query_stats和sys.dm_exec_sql_text。
下面是占用CPU时间的TOP10查询:
SELECTTOP(10)
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASEstatement_end_offset
WHEN-1THENDATALENGTH(st.text)
ELSEQS.statement_end_offset
END-QS.statement_start_offset)/2)+1)
ASstatement_text,
execution_count,
total_worker_time/1000AStotal_worker_time_ms,
(total_worker_time/1000)/execution_count
ASavg_worker_time_ms,
total_logical_reads,
total_logical_reads/execution_countASavg_logical_reads,
total_elapsed_time/1000AStotal_elapsed_time_ms,
(total_elapsed_time/1000)/execution_count
ASavg_elapsed_time_ms,
qp.query_plan
FROMsys.dm_exec_query_statsqs
CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)st
CROSSAPPLYsys.dm_exec_query_plan(qs.plan_handle)qp
ORDERBYtotal_worker_timeDESC
值得注意的是有些情况下缓存计划是会被清除的,如内存压力,数据库状态改变等。
使用了withrecompile的SP和option(recompile)提示的语句不会缓存执行计划。
当查询因为某些原因被重编译(统计信息改变,架构改变等),如果经常发生,则会让执行时间统计变得不准确。
所以最好是每隔一段时间抓取缓存计划信息,然后汇总对比。
CPU使用率过高的常见原因
查询优化器会尽量从CPU,IO和内存资源成本最小的角度,找到最高效的数据访问方式。
如果没有正确的索引,或者写的语句本身就会忽略索引,
又或者不准确的统计信息等情况下,查询计划可能不是最优的。
有些查询计划可能对只对某种条件下的查询是高效,而不是所有条件下都是。
缺失索引
索引的缺失,会导致查询处理的行数大大超出必要的行数,从而加重CPU和IO的负载。
简单的例子:
SELECTper.FirstName,
per.LastName,
p.Name,
p.ProductNumber,
OrderDate,
LineTotal,
soh.TotalDue
FROMSales.SalesOrderHeaderASsoh
INNERJOINSales.SalesOrderDetailsod
ONsoh.SalesOrderID=sod.SalesOrderID
INNERJOINProduction.ProductASpONsod.ProductID=p.ProductID
INNERJOINSales.CustomerAScONsoh.CustomerID=c.CustomerID
INNERJOINPerson.PersonASper
ONc.PersonID=per.BusinessEntityID
WHERELineTotal>25000
SQLServerparseandcompiletime:
CPUtime=0ms,elapsedtime=0ms.
SQLServerExecutionTimes:
CPUtime=452ms,elapsedtime=458ms.
上面的查询使用AdventureWorks2008数据库,字段LineTotal上没有索引,会导致SalesOrderDetail全表扫描。
然后创建如下索引后,改善很明显:
CREATENONCLUSTEREDINDEX
idx_SalesOrde
ON
Sales.SalesOrderDetail(LineTotal)
SQLServerparseandcompiletime:
CPUtime=0ms,elapsedtime=0ms.
SQLServerExecutionTimes:
CPUtime=0ms,elapsedtime=8ms.
过期的统计信息
查询优化器使用统计信息计算各种查询操作的基数(开销)。
查询操作的成本(cost)又决定了查询计划的成本。
过期的统计信息会导致生成非最优的查询计划,
如预估成本很低,但实际成本很高的计划。
最常见就是预估行数很少,并选择了那些适合少量数据的操作(如嵌套循环,LookUp),但当实际执行时要处理的行数却很多,查询效率就变得很低。
可以通过SSMS或者setstatisticsprofileon为索引查找和扫描操作,返回实际行数与预估行数做比较。
如果两者差异较大,就很有可能统计信息过期了。
过期时,可以使用updatestatisticstableName更新表上所有的统计信息,updatestatisticstableNamestatisticsName更新指定统计信息。
为了防止统计信息过期的问题,有如下三种方法:
a.开启数据库的Auto_Update_Statistics选项或者用定时作业更新全库的统计信息。
b.如果某些索引的自动更新统计信息被禁用,则需要指定STATISTICS_NORECOMPUTE=OFF重建索引开启。
c.对于某些经常因为统计信息过期而导致性能问题的统计信息,可以创建定时作业频繁地更新它们。
非SAGR谓词
SAGR=SearchAgrument.简单说就是能够使用索引查找的谓词。
列应该直接与表达式进行比较则符合SAGR,如WHERE SomeFunction(Column)=@Value就符合,
WHEREColumn=SomeOtherFunction(@Value)则符合。
注意LIKE和BETWEEN也是SAGR谓词。
非SAGR会导致表或者索引扫描,它的影响跟缺失索引类似。
使得CPU处理大量非必需的数据行。
下面查询会导致索引扫描:
SELECTsoh.SalesOrderID,
OrderDate,
DueDate,
ShipDate,
Status,
SubTotal,
TaxAmt,
Freight,
TotalDue
FROMSales.SalesOrderheaderASsoh
INNERJOINSales.SalesOrderDetailASsod
ONsoh.SalesOrderID=sod.SalesOrderID
WHERECONVERT(VARCHAR(10),sod.ModifiedDate,101)='01/01/2010'
改写成如下则会使用索引查找:
SELECTsoh.SalesOrderID,
OrderDate,
DueDate,
ShipDate,
Status,
SubTotal,
TaxAmt,
Freight,
TotalDue
FROMSales.SalesOrderheaderASsoh
INNERJOINSales.SalesOrderDetailASsodONsoh.SalesOrderID=sod.SalesOrderID
WHEREsod.ModifiedDate>='2010/01/01'
ANDsod.ModifiedDate<'2010/01/02'
UPPER,LOWER,LTRIM,RTRIM,ISNULL这些经常会被滥用,甚至用于WHERE和JOIN条件中。
在不区分大小写排序规则中,大小写被视为相等的,像UPPER,LOWER这种拖累性能的函数就不必要用了。
SQL中字符串比较会忽略末尾空格,所以RTRIM也没必要用。
下面两个过滤条件,前者,字段NULL值转换成0从而被排除;后者中,其实NULL值与任何值比较操作都不会返回TURE,而被排除。
NULL值只在ISNULL或者ISNOTNULL检查时才可能返回TRUE。
所以是等效的,但后者才能使用索引查找。
WHERE ISNULL(SomeCol,0)>0
WHERE SomeCol>0
隐式转换
隐式转换发生在比较两个不同数据类型时。
SQL不能对不同类型数据进行比较,所以查询优化器会在比较操作前把低优先级的数据类型转换成高优先级的数据类型再比较。
这跟非SARG谓词一样,将不能使用IndexSeek,从而处理很多不必要的数据行,增加CPU开销。
最常见例子是使用NVARCHAR类型的参数与VARCHAR类型的列进行比较。
如:
SELECTp.FirstName,p.LastName,c.AccountNumber
FROM
Sales.CustomerASc
INNERJOINPerson.PersonASp
ONc.PersonID=p.BusinessEntityID
WHEREAccountNumber=N'AW00029594'
上面的查询导致一个非聚集索引扫描,在Filter操作中会看有一个COVERT_IMPLICIT。
为了避免隐式转换:
1.JOIN的列,数据类型尽量相同
2.与列比较时,任何参数,变量和常量的类型要和列的类型相同
3.当参数,变量或常量的类型与要比较的列不同时,斟酌地使用类型转换函数,使其与列类型相同
4.有些数据访问组件和开发框架会把字符串类型默认地设置为NVARCHAR
参数探测(ParameterSniffing)
参数探测是SQLServer为存储过程,函数和参数化查询创建查询计划时用到的处理方式。
当首次编译查询计划时,SQLServer会检测或者探测输入参数的值并结合统计信
息,预估受影响的行数,
并以之估算查询计划成本。
当根据传入的参数值创建查询计划,得到的受影响行数不是典型的情况时,就产生问题了。
参数探测只出现在编译和重编译时,之后的存储过程,函数和
参数化查询,
会重用此查询计划。
最初编译时只有输入参数的值会被探测到,本地变量是没有值的。
如果批处理中的语句被重编译,则参数和变量将会被赋值并探测到。
示例如下:
CREATEPROCEDUREuser_GetCustomerShipDates
(@ShipDateStartDATETIME
@ShipDateEndDATETIME
)
AS
SELECTCustomerID,SalesOrderNumber
FROMSales.SalesOrderHeader
WHEREShipDateBETWEEN@ShipDateStartAND@ShipDateEnd
GO
Sales.SalesOrderHeader表的ShipDate字段范围是2004-08-07~2011-08-07,并创建非聚集索引:
CREATENONCLUSTEREDINDEXIDX_ShipDate_ASC
ONSales.SalesOrderHeader(ShipDate)
GO
首先我们执行两次SP,并用DBCCFREEPROCCACHE在运行前清空计划缓存:
DBCCFREEPROCCACHE
EXECuser_GetCustomerShipDates'2001/07/08','2004/01/01'
EXECuser_GetCustomerShipDates'2001/07/10','2001/07/20'
查询计划如图:
查询并没有使用ShipDate列非聚集索引,因为它不是一个覆盖索引,并且被执行时,查询优化器根据参数值结合统计信息预估的行数很多,使用IndexSeek和LookUp的组合成本
太高。
再观察STATISTICSIO&TIME:
==FIRSTEXECUTION(LARGEDATERANGE)===
(Table'SalesOrderHeader'.Scancount1,logicalreads686,physicalreads0.
SQLServerExecutionTimes:
CPUtime=16ms,elapsedtime=197ms.
SQLServerExecutionTimes:
CPUtime=16ms,elapsedtime=197ms.
==SECONDEXECUTION(SMALLDATERANGE)===
Table'SalesOrderHeader'.Scancount1,logicalreads686,physicalreads0.
SQLServerExecutionTimes:
CPUtime=15ms,elapsedtime=5ms.
SQLServerExecutionTimes:
CPUtime=15ms,elapsedtime=5ms.
两者的纯CPU时间和IO是基本一样,因为前者需要处理的数据量多很多,所以CPU消耗时间长一些。
接下来,调换两个执行SP的顺序,再执行:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Troubleshooting SQL ServerCPU使用过高 ServerCPU 使用 过高