觸發(fā)器造成死鎖、作業(yè)多且頻繁房交、中間表的大量使用彻舰、游標(biāo)的大量使用、索引的設(shè)計不合理候味、事務(wù)操作頻繁刃唤、SQL語句設(shè)計不合理，都會造成查詢效率低下白群、影響服務(wù)器性能的發(fā)揮尚胞。我們可以使用sql server自帶的性能分析追蹤工具sql profiler分析數(shù)據(jù)庫設(shè)計所產(chǎn)生問題的來源，進行有針對性的處理帜慢；下面小編收集了SQL Server中如何查詢CPU占用高的SQL語句笼裳，希望大大家能有所幫助唯卖。
注意：SQL Server中查詢CPU占用高的情況，經(jīng)常會用到sys.sysprocesses?,dm_exec_sessions?,dm_exec_requests
1侍咱、查看當(dāng)前的數(shù)據(jù)庫用戶連接數(shù)
USE?master
GO
SELECT?*?FROM?sys.[sysprocesses]?WHERE?[spid]>50?--AND?DB_NAME([dbid])='gposdb'
SELECT?COUNT(*)?FROM?[sys].[dm_exec_sessions]?WHERE?[session_id]>50
2耐床、選取前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])='gposdb'
ORDER BY [cpu_time] DESC
3、查詢前10個最耗CPU時間的SQL語句
--在SSMS里選擇以文本格式顯示結(jié)果
SELECT TOP 10
dest.[text] AS 'sql語句'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50
ORDER BY [cpu_time] DESC
4楔脯、查詢CPU占用高的語句
SELECT TOP 10 ? total_worker_time/execution_count AS avg_cpu_cost,
plan_handle, ? execution_count, ? (SELECT SUBSTRING(text,
statement_start_offset/2 + 1, ? ? ?(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
歡迎關(guān)注我的公眾號（同步更新文章）：DoNet技術(shù)分享平臺
