Finding
Top & Worst performaing queries
Everything is mentioned in the code .But be
careful while executing it. This is doing lots of sorts to get the ranking and
it is on different columns. Thus it will do lots of sorts and if your system is
alredy under pressure this query might add to your perf issue. I would suggest
that you start using one of the counter and comment others and then proceed to
make sure that it doesnt put the pressure on the system.
Also, this query will work on SQL server
2008 and compability 100 and above.
/*
You might have
seen a number of queries which provides you with top 10 or top 5 worst
performing queries either by Logical IO’s or by CPU time or by Elapsed Time.. I
have similar query but it does many more thing.
I personally do
not query either by total logical reads or total cpu time or total elapsed time
individually. I usually use this query to get the queries which are top n on a
number of counters like say top 5 by ‘Total Logical Reads’ and top 5 by ‘Total
CPU Time’ and top 5 by ‘Total Elapsed Time’ etc. Thus I will be able to find
the queries which are performing bad not just on one counter but on a number of
counters. Thus I will be able to find “true” worst perforaming query. I used
word “true” because sometime a query using more CPU time as it is a parallel
query whereas it is not taking too much time to actuall execute query (or the
total elapsed time rank is not say even in top 20). However, there is something
which is still missing. There are queries which are using the memory grant for
sort and hash joins. This value is not included here.However, for such queries
CPU time is more and thus these kind of queries might come up in top queries by
CPU time.
*/
with
PerformanceMetrics
as
(
select
--dest.text,
--statement_start_offset,
--statement_end_offset,
--LEN(dest.text)
ln,
substring
(
dest.text,
statement_start_offset/2,
case when statement_end_offset =
-1 then LEN(dest.text)
else
statement_end_offset
end /2
) as 'Text of the SQL' ,
deqs.plan_generation_num
as 'Number of times the
plan was generated for this SQL',
execution_count as 'Total Number of Times the
SQL was executed',
DENSE_RANK() over(order by
execution_count desc)
as 'Rank of the SQL by
Total number of Executions',
total_elapsed_time/1000 as 'Total Elapsed Time in ms consumed by this SQL',
DENSE_RANK() over(order by
total_elapsed_time desc)
as 'Rank of the SQL by
Total Elapsed Time',
Max_elapsed_time/1000 as 'Maximum Elapsed Time in ms consumed by this SQL',
min_elapsed_time/1000 as 'Minimum Elapsed Time in ms consumed by this SQL',
total_elapsed_time/1000*nullif(execution_count,0) as 'Average Elapsed Time in
ms consumed by this SQL',
DENSE_RANK() over(order by
total_elapsed_time/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Elapsed Time',
total_worker_time as 'Total CPU Time in ms
consumed by this SQL',
DENSE_RANK() over(order by
total_worker_time desc)
as 'Rank of the SQL by
Total CPU Time',
Max_worker_time as 'Maximum CPU Time in ms
consumed by this SQL',
min_worker_time as 'Minimum CPU Time in ms
consumed by this SQL',
total_worker_time/nullif(execution_count,0) as 'Average CPU Time in ms consumed by this SQL',
DENSE_RANK() over(order by
total_worker_time/nullif(execution_count,0) desc) as 'Rank of the SQL by Average CPU Time',
total_logical_reads as 'Total Logical Reads
Clocked by this SQL',
DENSE_RANK() over(order by
total_logical_reads desc) as 'Rank
of the SQL by Total Logical reads',
Max_logical_reads as 'Maximum Logical Reads
Clocked by this SQL',
min_logical_reads as 'Minimum Logical Reads
Clocked by this SQL',
total_logical_reads/nullif(execution_count,0) as 'Average Logical Reads Clocked by this SQL',
DENSE_RANK() over(order by
total_logical_reads/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Logical reads',
total_physical_reads as 'Total Physical Reads
Clocked by this SQL',
DENSE_RANK() over(order by
total_physical_reads desc) as 'Rank of the SQL by Total Physical Reads',
Max_physical_reads as 'Maximum Physical Reads
Clocked by this SQL',
min_physical_reads as 'Minimum Physical Reads
Clocked by this SQL',
total_physical_reads/nullif(execution_count,0) as 'Average Physical Reads Clocked by this SQL',
DENSE_RANK() over(order by
total_physical_reads/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Physical Reads',
total_logical_writes as 'Total Logical Writes
Clocked by this SQL',
DENSE_RANK() over(order by
total_logical_writes desc) as 'Rank of the SQL by Total Logical Writes',
Max_logical_writes as 'Maximum Logical Writes
Clocked by this SQL',
min_logical_writes as 'Minimum Logical Writes
Clocked by this SQL',
total_logical_writes/nullif(execution_count,0) as 'Average Logical Writes Clocked by this SQL',
DENSE_RANK() over(order by
total_logical_writes/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Logical Writes',
deqp.query_plan
as 'Plan of Query'
--similarly you
can add the ranks for maximum values as well.That is quite useful in finding
some of the perf issues.
from
sys.dm_exec_query_stats deqs
/*F0C6560A-9AD1-448B-9521-05258EF7E3FA*/ --use a newid so that we could exclude this query from the
performanc emetrics output
outer apply sys.dm_exec_query_plan(deqs.plan_handle) deqp --sometimes the plan
might not be in the cache any longer.So using outer apply
outer apply sys.dm_exec_sql_text(deqs.sql_handle) dest --Sometimes the text is not returned by the dmv so use
outer apply.
where
dest.text not like '%F0C6560A-9AD1-448B-9521-05258EF7E3FA%'
)
select
*
from
PerformanceMetrics
where
1=1
--apply any of
these where clause in any combinations or one by one..
--and [Rank of
the SQL by Average CPU Time] <= 20 --Use this to find the top N queries by
avg CPU time.
--and [Rank of
the SQL by Average Elapsed Time] <= 20 --Use this to find the top N queries
by avg elspsed time.
--and [Rank of
the SQL by Average Logical reads] <= 20 --Use this to find the top N queries
by avg logical reads.
--and [Rank of
the SQL by Average Physical Reads] <= 20 --Use this to find the top N
queries by avg physical reads.
and [Rank of
the SQL by Total CPU Time] <= 20 --Use this to find the top N queries by total CPU time.
and [Rank of
the SQL by Total Elapsed Time] <= 20 --Use this to find the top N queries by total elapsed time.
and [Rank of
the SQL by Total Logical reads] <= 20 --Use this to find the top N queries by Total Logical
reads.
and [Rank of
the SQL by Total Physical Reads] <= 20 --Use this to find the top N queries by Total Physical
Reads.
and [Rank of
the SQL by Total number of Executions] <= 20
--Use this to find the top N queries by Total number
of Executions.
--and [Rank of
the SQL by Average Logical Writes] <= 20 --Use this to find the top N
queries by Average Logical Writes.
and [Rank of
the SQL by Total Logical Writes] <= 20 --Use this to find the top N queries by Total Logical
Writes.
--I usually do
the query by 6 rank types together Total logical reads,Total CPU time, Total
Elapsed Time , Total Execution count ,Total Physical Reads and Total Logical
Writes.Sometimes I exclude last two counters if i do not get any query in the
output.
--If some
queries are say in top 10 in all these 6 categories then these needs to tune
first...
--But sometime
you might not get any rows at all if u use these 6 categiories in that case
remove one of these categories or try one by one..
No comments:
Post a Comment