현재실행중인(부하를 많이 잡아 먹는)쿼리문 보기

mssql2000이하라면 sp_lock, sp_who, sp_who2 등을 통한 sid를 읽어 dbcc inputbuffer를 이용하시면 됩니다.

그렇지만 지금 보시려는 상황에 대한 사항까지는 충족시켜주지는 못할 듯합니다.

 

 

2005 이상이라면 Dynamic View, Dynamic Function을 이용하는 방법도 있습니다.

아래는 그 쿼리입니다. 참고하십시오.

 

 

--IO

SELECT TOP 10

        [Average IO]       = (total_logical_reads + total_logical_writes) / qs.execution_count

       ,[Total IO]                = (total_logical_reads + total_logical_writes)

       ,[Execution count]  = qs.execution_count

       ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

                                                            (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)

       ,[Parent Query] = qt.text

       ,[DatabaseName] = DB_NAME(qt.dbid)

       ,[Query PLAN] = ql.query_plan

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 ql

ORDER BY [Average IO] DESC;

 

 

--cpu

SELECT TOP 10

        [Average CPU used]        = total_worker_time / qs.execution_count

       ,[Total CPU used]          = total_worker_time

       ,[Execution count]         = qs.execution_count

       ,[Individual Query]        = SUBSTRING (qt.text,qs.statement_start_offset/2,

                                                            (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) ,[Parent Query] = qt.text

       ,[DatabaseName]                  = DB_NAME(qt.dbid)

       ,[Query PLAN]              = ql.query_plan

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 ql

ORDER BY [Average CPU used] DESC;