Tracing High CPU usage Queries

Tracing High CPU usage Queries

This document describes how to trace the long running queries and the high CPU utilized queries. Tracing the queries and tuning / optimizing them will resolve the issue of CPU usage and improves the better performance.

Process of tracing the long running queries.

Option 1 :   The below procedure is to store the data of the High CPU Utilized queries at a point of time. It stores data of the queries into the table which is created by the procedure.

a.       

/* First we need to create a procedure as - HighCpuSessions_in_Production on the server */

CREATE procedure HighCpuSessions_in_Production   

as   

INSERT INTO dbo.HighCpuSessions SELECT  GETDATE(),r.session_id,status ,SUBSTRING(qt.text,   

r.statement_start_offset/2,       

(CASE       

WHEN r.statement_end_offset = -1        

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2         

ELSE r.statement_end_offset       

END - r.statement_start_offset)/2)      

AS query_text   -- this is the statement executing right now     

,qt.dbid     

,qt.objectid     

,r.cpu_time     

,r.total_elapsed_time     

,r.reads     

,r.writes     

,r.logical_reads     

,r.scheduler_id    

FROM sys.dm_exec_requests r    

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt   

WHERE r.session_id >50 

order by CPU_time desc

b.      

Create a SQL JOB , and the step will be as

exec HighCpuSessions_in_Production

Schedule this job for every 2 – 5 mins , according to our criteria. This job executes the procedure and inserts data into the table (dbo.HighCpuSessions).

For every  2 – 5 mins we can find high cpu utilized queries by select statement as :

select * from HighCpuSessions order by CPU_TIME desc

c.       

Create another job for cleaning the table “HighCpuSessions” every morning in order to avoid the huge data or records increase (deleting the data of before day’s) .

delete from HighCpuSessions

Note : Better option is of deleting the data every morning for our convenient time. Before deleting the data from the table its better to ‘EXCEL’ the result which shows the earlier day’s result for High CPU Session queries

.

Option 2 : If the CPU Utilization is running to 100% - Its nothing but the long running queries and the poor performing queries. Here is the another option to find the Long Running queries.

Create a procedure on MASTER db where it results the data with the statement_text (This is only the part of the exact query where and which its utilizing duration and CPU usage) which is utilizing more time. We can also check the actual query running back-end by checking the TEXT column in the results of the same.

Procedure which results the TOP 50 queries executed.

create procedure TOP50QueriesExecuted_EXECUTIONCount 

as 

select top 50 

qs.total_worker_time / execution_count as avg_worker_time, 

substring(st.text, (qs.statement_start_offset/2)+1, 

((case qs.statement_end_offset 

when -1 then datalength(st.text) 

else qs.statement_end_offset 

end - qs.statement_start_offset)/2) + 1) as statement_text, 

* 

from 

sys.dm_exec_query_stats as qs 

cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 

order by 

avg_worker_TIME desc 

By executing the above procedure we can find the total number of queries running / utilizing the high duration. Tuning these queries will result the better performance.

Option 3:  We have another procedure which results - logical reads / writes and the count.

create procedure TOP50ExecutedQueries 

as 

select top 50 

(total_logical_reads + total_logical_writes) as total_logical_io, 

(total_logical_reads/execution_count) as avg_logical_reads, 

(total_logical_writes/execution_count) as avg_logical_writes, 

(total_physical_reads/execution_count) as avg_phys_reads, 

substring(st.text, (qs.statement_start_offset/2)+1, 

((case qs.statement_end_offset 

when -1 then datalength(st.text) 

else qs.statement_end_offset 

end - qs.statement_start_offset)/2) + 1) as statement_text, 

* 

from 

sys.dm_exec_query_stats as qs 

cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 

order by 

total_logical_io desc

The above procedure provides the execution count and the total logical reads etc.,

We need to tune these queries as well to improve server performance to better.