3초기억력

MS-SQL CPU 점유율 높은것 쿼리 본문

쿼리_MSSQL

MS-SQL CPU 점유율 높은것 쿼리

잠수콩 2014. 6. 25. 10:28
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.



제목 : MS-SQL CPU 점유율 높은것 쿼리




USE master


 



SELECT TOP 10 

       REPLACE(CONVERT(VARCHAR(20), CONVERT(MONEY,qs.total_worker_time / qs.execution_count/1000),1),'.00','') as [Avg CPU Time(ms)]

      ,qs.execution_count

      ,substring (qt.text,

                  qs.statement_start_offset/2+1,

                  (case when qs.statement_end_offset = -1 then len (convert (nvarchar(max), qt.text)) * 2

                        else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text

      ,qt.dbid

      ,sd.name

      ,qt.objectid

      ,qt.number

      ,qt.encrypted

      ,qt.text

      ,sr.session_id

      ,sr.command

      ,sr.status

      ,sr.last_wait_type

      ,sr.wait_resource

      ,sq.query_plan

      ,fs.text

  FROM sys.dm_exec_query_stats qs

       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 

       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as sq

       LEFT OUTER JOIN sys.databases as sd on qt.dbid = sd.database_id

       LEFT OUTER JOIN sys.dm_exec_requests as sr on qs.sql_handle = sr.sql_handle

       OUTER APPLY sys.fn_get_sql(sr.sql_handle) AS fs       

 ORDER BY qs.total_worker_time / qs.execution_count DESC

GO


내용 :




 출처 :  


Comments