DBA案例分析：如何解决CPU占用100%的问题
标签：SQL SERVER 2005 CPU 性能优化
【51CTO独家特稿】本文来自于DBA实践第一线，具体情况，具体分析，全程回放，如何解决CPU占用率达100%的过程和全景。
背景说明:
某商业门户网站，系统后台架构为Windows 2003 Server + SQL Server 2005 SP2 +IIS 6.0,
网站采用asp.net 2.0 技术。服务器技术参数为CPU 系4核Xeon（TM） CPU 3.00GHZ,2G内存，100M网卡。
故障情况说明：
某日客服部来电反映，网站反映响应速度变慢。而在DBA上午的例行检查中也发现 ，通过任务管理器和性能监察器均显示CPU持续占用率高达100%。而正常情况下的网站CPU占用率仅在10%以下。
解决过程：
一旦异常出现，正如医生看病，首先要探明问题出现的原因，才能够对症下药，有的放矢，方能解决问题。DBA某种程度上正是数据库的医生。面对持续100%的CPU占用率这样的经典疑难杂症，笔者首先需要排查的该问题是否由操作系统平台引起，考虑到CPU占用率一直锁定在100%,并且没有丝毫的振荡区间，作为一名windows用户,多年使用经验告诉我，这很可能是木马或者病毒惹的祸，遂使用赛门铁克扫描服务器果然发现arp.exe感染了木马，该arp.exe本来是一个防止arp攻击的软件，现在服务器进行了双向绑定，果断将其关闭。关闭该软件后发现CPU占用率从峰值100%立即回落，然后一直在50%和100%间来回震荡。至此，我们完成了第一步，排除了木马病毒引起操作系统平台问题的解决，但很显然，问题还没有彻底解决，但我们看到问题的根源已经向应用层面转移了。为了进一步验证这种想法，笔者把访问该数据库的Web应用服务暂停，发现数据库服务机器CPU占用率立即回落到0。到此时，我们可以确信，问题的症结已经在应用系统的问题了。
应用系统引起的性能问题，笔者曾经作过总结，主要集中体现在重要表的索引建立和使用上，而执行效率低下的SQL写法导致查询或更新进程的阻塞，直至引发的死锁现象都是引发SQL Server响应异常缓慢，CPU占用率高居不下的主要原因。
首先执行了Sp_lock，返回结果发现X锁和IX锁并没有出现，故排除掉死锁现象。
首先执行了如下检查
SELECT TOP 5 total_worker_time, last_worker_time,
max_worker_time, min_worker_time,
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
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC
发现CPU占用时间大得惊人，而且都是针对了表maininfotab 和detialInfotab表（这两个表是WEB应用系统中使用频率最大同时也是记录最多的两个表）很显然，问题已近进一步定义了，很可能是常见表的索引建立和使用的问题而引起的阻塞导致CPU处理能力下降。
为了进一步把问题锁定到更精确地位置，笔者开启了SQL Server Profiler进行跟踪，如下图，由于是性能检测，所以选用了Tuning模板，如下图：
点击”运行” 后，系统进入检测页面，待SQL server profiler运行了一段时间后，将其检测结果保存为trc文件，这个时候，就可以就具体的性能问题着手进行定位分析了，通过trc文件我们发现，Duration出现值大于1000的多次出现，且objectType一般为P(即Stored Procedure类型),由于该web应用访问数据库主体的业务逻辑主体是通过Stored Procedure运行的，通过该文件已经能够实施”精确”方案了，很显然，下一步计划应该是调整表的索引和索引碎片了。
既然有了trc文件，何不让SQL Server 2005的数据库引擎优化顾问”表现”一下，很多人不屑于SQL Server 2005本身提供的工具，总喜欢第三方工具，笔者认为，工具的使用是殊途同归的，关键是能够有益于解决问题，笔者还是立即运行了该工具，并参考了它的分析报告，
如下图：
选择了要分析负荷trc文件后，点击”开始分析”，一段时间后它将给你优化报告，可以择其善者而从之。笔者通过一番比较和研究发现它给出的一些索引和信息重建确实比较合理，故在SQL server management studio 中执行了这些重建索引和静态信息的语句，执行之后，效果比较明显，SQL SERVER 2005的CPU占用率已经回落到平均50%^左右了。此时，夹在心里的一块石头算是落地了，但是革命尚未成功，同志仍须努力，紧接着笔者继续调整了很多重要大表的索引和静态信息计划的重建，并且对索引碎片也进行了重新组织，并且更改了备份计划，让每天备份完成后自动进行索引碎片重新组织的任务，如下图：
在持续调整优化表和索引，并且重新组织索引碎片后，SQL SERVER 2005的CPU再度从平均50%下降到10%左右了。
这个结果也表明，通过笔者持续努力，已经取得了解决CPU占用率100%的阶段性成绩，随着网站用户量的进一步提升，数据库并发性能和吞吐量将面临进一步的考验。
