Link to home
Start Free TrialLog in
Avatar of hmra
hmra

asked on

How to find 50 most expensive queries in SQL 2008

Hello There,

I know there are several queries to  find  the 50 most expensive queries, but I would like to know if there is a new feature in SQL 2008 R2 that give you the correct output, if there is none please provide me with a query that you think is the best option to use.

Thank you in advance
Avatar of Jshaw
Jshaw

SELECT TOP 50
        qs.total_worker_time/qs.execution_count as [Avg CPU Time],
        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 query_text,
            qt.dbid, dbname=db_name(qt.dbid),
            qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
        [Avg CPU Time] DESC

cheers
Jeff
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial