關於 SQLServer.exe CPU 100% 相關處理

2011-02-22

12846
0
Server

摘要:關於 SQLServer.exe CPU 100% 相關處理

問題: 請問 SQL Server 2005 SP3 (9.00.4035) CPU 持續 100% 問題
http://social.msdn.microsoft.com/Forums/zh-TW/240/thread/327f64fe-a1e9-42f6-b112-3d1621f303ad/

描述: 運作中環境在非忙碌的時間，SQLserv.exe CPU 100%。

解決:

可先透過 Select * from sys.dm_exec_requests 查詢是否有 wait_type 是 LCK_M_X ，
或是可透過以下查詢，找出目前相關消耗CPU時間的查詢語法。

SELECT 
substring(text,qs.statement_start_offset/2
,(CASE 
WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), 
text)) * 2 
ELSE qs.statement_end_offset 
END - qs.statement_start_offset)/2) 
,qs.plan_generation_num as recompiles
,qs.execution_count as execution_count
,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
,qs.total_worker_time as cpu_time
,qs.total_logical_reads as reads
,qs.total_logical_writes as writes
,last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
LEFT JOIN sys.dm_exec_requests r 
ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC

sys.dm_exec_sql_text

傳回指定的 sql_handle 所指 SQL 批次的文字。
這個資料表值函數取代系統函數 fn_get_sql。

sys.dm_exec_requests
http://technet.microsoft.com/zh-tw/library/ms177648(SQL.90).aspx

sys.dm_exec_query_stats (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms189741.aspx

相關參考:

Optimizing SQL Server CPU Performance
http://technet.microsoft.com/en-us/magazine/2007.10.sqlcpu.aspx

Tracking database recovery progress using information from DMV
http://blogs.msdn.com/b/psssql/archive/2010/12/29/tracking-database-recovery-progress-using-information-from-dmv.aspx

Performance Tuning in SQL Server 2005
http://www.docstoc.com/docs/24580659/Performance-Tuning-in-SQL-Server-2005
WhatsUp Professional - sqlservr.exe 100% CPU issue
http://support.ipswitch.com/kb/WP-20050405-DM01.htm

sqlservr.exe 95% cpu time
http://sql-server-performance.com/Community/forums/p/14093/81413.aspx
SQLservr.exe 100% CPU, why?
http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/793de024-ba0d-4642-b562-977daa6e3698

回首頁