블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
  • total
  • today
  • yesterday
2018. 10. 27. 22:39 SQL SERVER


SELECT 

-- using statement_start_offset and

-- statement_end_offset we get the query text

-- from inside the entire batch
                                 DB_NAME(qt .dbid) dbname,
                                 OBJECT_NAME(qt .objectid) [object_name],
        SUBSTRING(qt .TEXT, ( qs.statement_start_offset / 2 ) + 1,

         ( ( CASE qs.statement_end_offset

               WHEN - 1 THEN DATALENGTH(qt .TEXT)

               ELSE qs .statement_end_offset

             END - qs. statement_start_offset ) / 2 ) + 1) AS [Text] ,

     qs.execution_count ,

     qs.total_logical_reads ,

     qs.last_logical_reads ,

     qs.total_logical_writes ,

     qs.last_logical_writes ,

     qs.total_worker_time ,

     qs.last_worker_time ,

-- converting microseconds to seconds

     qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,

     qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,

     qs.last_execution_time ,

     qp.query_plan

FROM sys .dm_exec_query_stats qs
-- Retrieve the query text
     CROSS APPLY sys. dm_exec_sql_text(qs .sql_handle) qt
        -- Retrieve the query plan
     CROSS APPLY sys. dm_exec_query_plan(qs .plan_handle) qp
ORDER BY qs. total_worker_time DESC -- CPU time



  

posted by bedbmsguru