Link to home
Start Free TrialLog in
Avatar of AXISHK
AXISHK

asked on

CPU Consumption on SQL Server

I have used the following query to get the 10 worst CPU consuming queries on the SQL Server. what fact can cause a high CPU consumption ? Will it be possible that the CPU are doing table scan ? Tks


SELECT TOP 10
    sql.text as sql
   , qp.query_plan
   , creation_time
   , last_execution_time
   , execution_count
   , (total_worker_time / execution_count) as avg_cpu
   , total_worker_time as total_cpu
   , last_worker_time as last_cpu
   , min_worker_time as min_cpu
   , max_worker_time as max_cpu
   , (total_physical_reads + total_logical_reads) as total_reads
   , (max_physical_reads + max_logical_reads) as max_reads
   , (total_physical_reads + total_logical_reads) / execution_count as avg_reads
   , max_elapsed_time as max_duration
   , total_elapsed_time as total_duration
   , ((total_elapsed_time / execution_count)) / 1000000 as avg_duration_sec
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp
ORDER BY qs. total_worker_time DESC
SOLUTION
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial