High CPU utilization
TABLE OF CONTENTS
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 convinient 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 backend by checking the TEXT column in
the results of the same.
Procedure which reults the TOP50 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.
No comments:
Post a Comment