■ CPU 점유율 높은 SQL문을 조회하는 방법을 보여준다.
▶ 예제 코드 (SQL)
|
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 32 33 34 35 36 37 |
SELECT TOP 10 REPLACE(CONVERT(VARCHAR(20), CONVERT(MONEY, A.total_worker_time / A.execution_count/1000), 1), '.00', '') AS [Avg CPU Time(ms)] ,A.execution_count ,SUBSTRING ( B.text, A.statement_start_offset / 2 + 1, ( CASE WHEN A.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), B.text)) * 2 ELSE A.statement_end_offset END - A.statement_start_offset ) / 2 ) AS query_text ,B.dbid ,D.name ,B.objectid ,B.number ,B.encrypted ,B.text ,E.session_id ,E.command ,E.status ,E.last_wait_type ,E.wait_resource ,C.query_plan ,F.text FROM sys.dm_exec_query_stats AS A CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS C LEFT OUTER JOIN sys.databases AS D ON D.database_id = B.dbid LEFT OUTER JOIN sys.dm_exec_requests AS E ON E.sql_handle = A.sql_handle OUTER APPLY sys.fn_get_sql(E.sql_handle) AS F ORDER BY A.total_worker_time / A.execution_count DESC GO |