使用(Dynamic Management View)找出執行最久的查詢Part 2

這是之前所撰寫dmv script的補強，我加上了IO與記憶體相關的資訊，能讓dba能掌握更詳盡的資訊。

適用版本為：sql 2005以上。

SELECT

 es.session_id

,er.blocking_session_id

,es.program_name [程式名稱]

,(es.reads + es.writes) * 8.0 / 1024 [IO]

,es.memory_usage * 8.0/1024 [使用計憶體(MB)]

,es.host_name [主機名稱]

,es.login_name [登入帳號]

,user_name(user_id) [使用者名稱]

,er.status [執行狀態]

,DB_NAME(database_id) [資料庫]

,er.start_time  [開始時間]

,er.total_elapsed_time * 1.0 / 1000 [作業花費時間(秒)]

,es.cpu_time*1.0/1000  [cpu時間]

,substring(qt.text, (er.statement_start_offset / 2) + 1,

                     ((CASE WHEN er.statement_end_offset = -1 THEN      LEN(convert(NVARCHAR(MAX), qt.text)) * 2

                                                                                                                                 ELSE   er.statement_end_offset

                                                                                                                                 END - er.statement_start_offset) / 2) + 1)  [SQL指令]

,qp.query_plan  [執行計劃]                      

            FROM

                      sys.dm_exec_requests AS er

                      INNER JOIN sys.dm_exec_sessions AS es

                                 ON es.session_id = er.session_id

                      CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

                      CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp

            WHERE

                      es.is_user_process = 1 AND es.session_Id <> @@SPID

      ORDER BY er.total_elapsed_time DESC

參考網址

使用DMV(Dynamic Management View)找出目前正在執行的查詢

執行以下程式碼可以找出的資訊：

1.      是誰在執行，參照[主機名稱]與[登入名稱]。

2.      執行SQL指令，參照[SQL指令]。

3.      執行SQL指令的應用程式名稱，參照[執行程式名稱]。

4.      SQL指令目前執行多久，參照[執行時間]。

5.      BLOCK與Transaction的資訊，參照[目前執行SQL的transaction數目]與[等待類別]等。

SELECT

 b.session_id

,b.host_name  [主機名稱]

,b.login_name [登入名稱]

,a.status  [執行狀態]

,DB_NAME(database_id) AS [資料庫名稱]

,c.text AS [SQL指令]

,b.program_name [執行程式名稱]

,a.start_time   [SQL開始執行時間]

,a.wait_type    [等待類別]

,a.total_elapsed_time [執行時間]

,a.cpu_time           [CPU時間]

,a.logical_reads      [邏輯讀取]

,a.open_transaction_count [目前執行SQL的transaction數目]

,a.last_wait_type       [上次等待類別]

FROM sys.dm_exec_requests AS a

INNER JOIN sys.dm_exec_sessions AS b ON b.session_id = a.session_id

CROSS APPLY sys.dm_exec_sql_text( a.sql_handle) AS c

WHERE b.is_user_process=1 AND b.session_Id <> (@@SPID)

ORDER BY b.session_id

執行結果：

使用(Dynamic Management View)找出執行最久的查詢

使用sys.dm_exec_query_stats與sys.dm_exec_sql_text找出被Block最久的SQL指令，如果有些SQL指令Block太久，會早成其他SQL指令要等待它釋放資源才能完成工作，這樣會大大影響資料庫的效能，甚至引起死結，要快速找出Block最久的SQL指令使用DMV是一個快速的方法，以下的程式碼就是使用DMV找出執行最久的SQL指令。

SELECT TOP 10 [執行時間(秒)]=CAST((a.total_elapsed_time - a.total_worker_time) /1000000.0 AS DECIMAL(16,2))

,[執行次數]= a.execution_count

,[SQL指令]= b.text

FROM sys.dm_exec_query_stats A

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

WHERE a.total_elapsed_time > 0 AND b.text  NOT LIKE '%SCHEMA%'

ORDER BY 1 DESC

執行結果：

使用(Dynamic Management View)找出執行時間最久的查詢

找出執行時間最久的SQL指令有助於我們減少資料庫的負擔，因為執行時間過長的SQL指令占用資料庫的資源也很長，所出這些SQL指令後加以修改可以不但可以縮短執行時間也可以增加資料庫的效能。

程式碼如下：

SELECT TOP 10

  [總執行時間(秒)]               =CAST(a.total_elapsed_time / 1000000.0 AS DECIMAL(16, 2)) 

, [執行次數]                            =a.execution_count

, [平均執行時間(秒)]     =CAST(a.total_elapsed_time / 1000000.0 / a.execution_count AS DECIMAL(16, 2))

, [SQL指令]                            =SUBSTRING (b.text,(a.statement_start_offset/2) + 1,500)

FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

WHERE a.total_elapsed_time > 0 AND B.[text] NOT LIKE '%SCHEMA_NAME%'--去除一些系統的SQL指令

ORDER BY [平均執行時間(秒)] DESC

執行結果：