High CPU usage caused by SQL Server

All processes that are currently running on the instance-


SELECT *
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
and session_id <> @@spid


If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output)

• RPC: Completed (Under stored procedures)

• SQL: BatchCompleted (Under TSQL)

Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value.


1.Find disk delays for a particular database-

select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
where DB_NAME(database_id) = 'your database name'
order by avg_io_stall_ms desc


2. Find IO pending for an Instance-

select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle



3. Find memory pressure-


select * from sys.dm_os_performance_counters
where counter_name like 'page life%'


4. Find queries utilization-


SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC


5. Find CPU usage-


select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255


6. Find System info-
Select * from sys.dm_os_sys_info



7. There are some useful Dynamic Management Views (DMVs) to check CPU bottlenecks.

select plan_handle,
sum(total_worker_time) as total_worker_time,
sum(execution_count) as total_execution_count,
count(*) as number_of_statements
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc

SQL Server 2008 computes the hash value of every query during compilation. You can find this value in the query_hash column.

This value is shown as the QueryHash attribute in Showplan/Statistics XML too.

The plan_generation_num column shows how many times the query has been recompiled.

The SQL Server optimizer tries to choose an execution plan for the query that provides the fastest response time but this does not always mean minimal CPU utilization. Inefficient query plans that cause increased CPU consumption can also be detected using the sys.dm_exec_query_stats.

8.