How to get the names of the most expensive SPs in SQL Server 2005/2008

Question

Hi,

With the following quiry i get the list of top 10 most expensive queries with their SQL statements.

select top 10 * from sys.dm_exec_query_stats 
cross apply sys.dm_exec_sql_text(sql_handle)
order by max_logical_reads desc

But how can I extract their object names ?

Answer 1

It's in the last column "text" for me on SQL Server 2005 SP3, like this

CREATE PROCEDURE dbo.myProc @parameter 1 int...

You could try OBJECT_NAME(objectid) but Metadata Visibility may prevent this (like it does me on my production boxes)

Answer 2

If you are dealing with persisent objects (i.e. stored procedures) then you can join on sysobjects like this:

select top 10 o.name from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) b 
inner join sys.sysobjects o on b.objectid = o.id
order by max_logical_reads desc

Answer 3

automaticaly you can't. you'll have to parse them out yourself from the [Text] column

Answer 4

In this site, if you download the Zip file and create the uspWorstTSQL stored procedure and run it, this should give you a good list of what your after.

Answer 5

select top 10 OBJECT_NAME(objectid), * from sys.dm_exec_query_stats 
cross apply sys.dm_exec_sql_text(sql_handle)
order by max_logical_reads desc