Finding the Most Expensive Queries starts with choosing a resource measure. I use cached statistics as an investigation aid.
A performance-tuning client asked for the basic script I use to identify expensive work. The original sorted cached statements by total logical reads. That is a useful starting point, but it is not a list of queries running now.
SELECT TOP (10)
SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
(CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(t.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text,
qs.execution_count, qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.last_worker_time / 1000000.0 AS last_cpu_seconds,
qs.total_elapsed_time / 1000000.0 AS total_elapsed_seconds,
qs.last_elapsed_time / 1000000.0 AS last_elapsed_seconds,
qs.last_execution_time, p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
ORDER BY qs.total_logical_reads DESC, qs.last_execution_time DESC;The statement offsets extract the relevant statement from the batch. OUTER APPLY preserves the statistics row when a plan is unavailable. Total counters accumulate for that cached statement; last counters describe its last execution.
The query converts CPU and elapsed microseconds to fractional seconds. Logical reads and writes are page counts. Total reads can favor a frequently executed cheap statement; an average-cost question needs execution_count as well.
Keep the collection scope visible
Plan eviction removes these rows. Query Store offers retained history when configured. Permission requirements change with the server version; SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for this DMV.
Change the sort deliberately for CPU or writes. Don’t call the largest selected counter the universally worst query without reviewing the workload and plan.
Reference: Cached statement statistics.
Related reading
The largest cached counter is not a complete diagnosis, it is a place to start examining the workload.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
56 Comments. Leave new
create function findexpf
(
@eno int
)
returns @t int
as
begin
declare @t int
select @t=DATEDIFF(dd,hiredate,getdate())/365 from emp where EMPNO=@eno
return @t
end
* can u plz correct it
Please find the corrected one below….
create function findexpf
(
@eno int
)
returns int
as
begin
declare @t int
select @t=DATEDIFF(dd,hiredate,getdate())/365 from emp where EMPNO=@eno
return @t
end
====================
Thanks…
Gopal Reddy N R
create function findexpf
(
@eno int
)
returns int
as
begin
declare @t int
select @t=DATEDIFF(dd,hiredate,getdate())/365 from emp where EMPNO=@eno
return @t
end
Hi Pinal
this script realy helpful for finding most expensive query Thank you lot for helping us but here spid will not be displyed in any column so again its difficult in large environment which spid is eating more cpu memory can you please suggest me on this
hi, how can i clear these counters ?
Hi All,
Can we use SPID in the above query because witout spid we can not help application team.
Thanks in advance
Others think the habit is more likely connected to their checking the ground
for thhe scent of its enemies, since the dog has iits
nose to the ground during the turning around.
Go ahead. Doog ear are is imperative to making suire your ddog does not suffer needlessly.
How is this query different from the results generated by the Activity Monitor in SSMS?
Great Post Thanks…:)!
do we have any query to find out the most consuming memory with spid
This is great post thanks. My only question is how to include which object the expensive the queries belongs to?
Great Post. My only question is there a way to add object the expensive query belongs to? Without this information, I need to search through whole database to find the where that query coming from.
which permissions need to be grant to normal user, to run this query.
I have seen many queries to take out this information but is there a way to also add information on what user did the query?
We have a few different systems that uses the same databases and it would be nice to see depending on user or perhaps server name/ip what system is performing the expensive query.
Possible?
sys.dm_exec_query_stats doesn’t contain use details because its about the query execution. You need to use your own data collection mechanism to find server/IP/Login running those queries.
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,max(cp.usecounts) execution_count
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where DB_NAME(st.dbid) is not null and cp.objtype = ‘proc’
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc
I have run this got output, i sort the total_IO desc.. so i get some value around 692942307
milliseconds.
So how do i check how much of the time is this query taking to execute
Total total_physical_reads = 0 and total_logical_reads = 514358205, so is it required to fine tune these type of procedures
I am a student and struggling with how to input this syntax:
#i19 display only the price of the highest priced item,
# display should have column header “Max Price” and prefix price with $
# you must use an aggregate function
For me, the most interesting statistic is the average duration i.e. total_worker_time/executions. Also, if you store a snapshot of this data at regular intervals, you can the detect when that average jumps by a significant amount, which could alert you to a bad plan.
You wrote:
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
Is that right?
I tried:
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
and looks like return to me more correct results
Is it possible to make the script to show the most expencive queries for the last week? Or month?