The title of this post is what I can express here for this quick blog post. I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries in SQL Server Box.
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC -- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes -- ORDER BY qs.total_worker_time DESC -- CPU time
You can change the ORDER BY clause to order this table with different parameters. I invite my reader to share their scripts. Please note that this query is valid on any version of SQL Server from SQL Server 2005 or later version of SQL Server. I have been using this query on all of my performance tuning consultation and it works perfectly fine.
Reference: Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
One of the things to keep in mind with this query is that this gives statistics for cached query plans only. If you had a query which uses a lot of resource but is not cached, then this query won’t be able to show you this query.
The other thing to keep in mind for this is that on today’s larger servers, the plan cache can be many GB’s in size, so while you are only doing a TOP 10, this can still be a fairly expensive query to execute.
Hi, thanks for useful script, just wonder if result could be filtered for concrete database.
thanks for the script, do you know if there is a dmv which is equivalent to sp_who2.
Thank you
Here is what the scripts I have…this gives the original source of the query which helps in making any changes to query
–Queries taking longest elapsed time:
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_seconds DESC;
–Queries doing most I/O:
SELECT TOP 10
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_IO DESC;
Gaurav,
I get a syntax error on both of your queries:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ‘-‘.
just delete that ‘– ‘ and type that again.
No, it does not work. Please review before post. :(
Retyping the ‘-‘ worked for me.
How can I add the database name to this query?
Hi Rob,
Just add the script DB_NAME(qt.dbid) as DBName on the select statetment.
Regards,
Mai
Thank you for the script Pinal. Do you have a workaround for SQL 2008? It doesn’t appear to work with .sql_handle’.
Check the compatibility level of the database. If its in SQL 2000 mode then this query will not work.
I ran the script on a database but the result was empty, i ran again in another database and works why is that? the statistics are enabled
check your database compatibility
out of following commands which one is most costly:
1)Insert
2)Update
3)delete
4)truncate
If possible please provide the reason also.
update bcz it has to overwrite the data.If any index is on table then it has to modifi that and if that index is clusterd index then that index arrange itself in assinding order.
Any idea for DB running on Sql 2000? Please?
It seems it doesn’t work on 2005 :(
Rohit: It works on 2005. If you cant make it work, check the database compatibility. There are a lot of good query DMV Scripts (search for DMV Allstars)
Vick: Since 2000 doesnt have DMVs, go for the ol’ fashioned SQL Profiler or automated scripts that select and record the sysprocesses / DBCC NPUTBUFFER output.
Does running these DMVs in itself cause any performance overhead. My DBA is of the opinion that running these queries in itself cause an overhead. Can anyone please provide me concrete data/proof that these DMVs are not resource intensive
Hi Pinal,
Nice post, quite helpful while working for Query optimization.
Regards,
Girijesh
Sir, I would like to know why have you done (qs.statement_start_offset/2)+1
The second argument is the starting place. Using the statement start offset and statement end offset, we have to divide by two because the offset is in bytes and the text is Unicode. In this argument only, we must add 1 because the statement_start_offset and statement_end_offset are 0 based whereas the substring function is 1 based. This is correct in this function.
Hi all, Is it possible to get top 10 sql for our time frame?
Hi, thanks for a wonderful query. What is the way to find out which SPID’s are running these queries?. Thanks in advance
Dear Pinal,
can u suggest any way to find out ” if any body has access any particular storedprocedure” wheather by application or any other way
showing Error :Incorrect syntax near ‘.’.