一般我們可以使用sql server自帶的性能分析追蹤工具sql profiler分析數據庫設計所產生問題的來源，進行有針對性的處理。但我們也可以通過自己寫SQL語句來有針對性的進行性能方面的查詢。通常會用到如下三個系統視圖：sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests --一、查看當前的數據庫用戶連接有多少 USE master GO SELECT* FROMsys.[sysprocesses] WHERE [spid] > 50 --AND DB_NAME([dbid])='gposdb' SELECTCOUNT(*) FROM[sys].[dm_exec_sessions] WHERE [session_id] > 50 --二、選取前10個最耗CPU時間的會話 SELECT TOP 10 [session_id] , [request_id] , [start_time] AS '開始時間' , [status] AS '狀態' , [command] AS '命令' , dest.[text] AS 'sql語句' , DB_NAME([database_id]) AS '數據庫名' , [blocking_session_id] AS '正在阻塞其他會話的會話ID' , [wait_type] AS '等待資源類型' , [wait_time] AS '等待時間' , [wait_resource] AS '等待的資源' , [reads] AS '物理讀次數' , [writes] AS '寫次數' , [logical_reads] AS '邏輯讀次數' , [row_count] AS '返回結果行數' FROMsys.[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 --三、查詢前10個最耗CPU時間的SQL語句 SELECT TOP 10 dest.[text] AS 'sql語句' FROMsys.[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 --四、查詢會話中有多少個worker在等待 SELECT TOP 10 [session_id] , [request_id] , [start_time] AS '開始時間' , [status] AS '狀態' , [command] AS '命令' , dest.[text] AS 'sql語句' , DB_NAME([database_id]) AS '數據庫名' , [blocking_session_id] AS '正在阻塞其他會話的會話ID' , der.[wait_type] AS '等待資源類型' , [wait_time] AS '等待時間' , [wait_resource] AS '等待的資源' , [dows].[waiting_tasks_count] AS '當前正在進行等待的任務數' , [reads] AS '物理讀次數' , [writes] AS '寫次數' , [logical_reads] AS '邏輯讀次數' , [row_count] AS '返回結果行數' FROMsys.[dm_exec_requests] AS der INNER JOIN [sys].[dm_os_wait_stats] AS dows ON der.[wait_type] = [dows].[wait_type] CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest WHERE [session_id] > 50 ORDER BY [cpu_time] DESC --五、查詢CPU占用高的語句 SELECT TOP 10 total_worker_time / execution_count AS avg_cpu_cost , plan_handle , execution_count , ( SELECTSUBSTRING(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) FROMsys.dm_exec_sql_text(sql_handle) ) AS query_text FROMsys.dm_exec_query_stats ORDER BY [avg_cpu_cost] DESC