使用(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

參考網址