Biegral Blog

DBA案例分析：如何解决CPU占用100%的问题

http://database.51cto.com/art/200805/74978.htm 

SQL Server 2005 CPU 100%

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/935e910a-fcd4-4e29-825e-a0ab456b4422/

首先执行了如下检查

 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处理能力下降。

SELECT TOP(25) qt.[text] AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count AS [Execution Count], ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()), 0) AS [Calls/Second], ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS [AvgElapsedTime], qs.max_logical_reads, qs.max_logical_writes, DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt WHERE qt.[dbid] = DB_ID() -- Filter by current database ORDER BY qs.total_worker_time DESC;