最近發(fā)現(xiàn)SQLserver進程占用CPU的很大，導(dǎo)致應(yīng)用程序部分程序打開緩慢，
把sqlserver服務(wù)重新啟動以后，情況好轉(zhuǎn)，但是過一段時間或者一會，cpu占用有大了。
最后排除發(fā)現(xiàn)是網(wǎng)絡(luò)內(nèi)某個機器訪問sql過大造成的，怎么找到這個具體的機器呢
利用下面語句：
USE master
GO
----如果要指定數(shù)據(jù)庫就把注釋去掉
SELECT * FROM sys.sysprocesses WHERE spid>50 AND DB_NAME([dbid])='JHLZB'
SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE session_id>50
看一下當(dāng)前的數(shù)據(jù)庫用戶連接有多少，都是什么機器連接的，哪個機器連接消耗cpu大，找到具體的機器處理。
然后使用下面語句看一下各項指標(biāo)是否正常，是否有阻塞，這個語句選取了前10個最耗CPU時間的會話
SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '開始時間',
[status] AS '狀態(tài)',
[command] AS '命令',
dest.[text] AS 'sql語句',
DB_NAME([database_id]) AS '數(shù)據(jù)庫名',
[blocking_session_id] AS '正在阻塞其他會話的會話ID',
[wait_type] AS '等待資源類型',
[wait_time] AS '等待時間',
[wait_resource] AS '等待的資源',
[reads] AS '物理讀次數(shù)',
[writes] AS '寫次數(shù)',
[logical_reads] AS '邏輯讀次數(shù)',
[row_count] AS '返回結(jié)果行數(shù)'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='JHLZB'?
ORDER BY [cpu_time] DESC
