Solved

How to find 50 most expensive queries in SQL 2008

Posted on 2011-02-14
2
822 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:hmra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Expert Comment

by:Jshaw
ID: 34890984
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
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34890992
Which queries are consuming the most CPU resources and what are the query plans...
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,
		qp.query_plan
FROM sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
	cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
ORDER BY 
        [Avg CPU Time] DESC

Open in new window

Which queries are consuming the most IO resources...
SELECT TOP 50
        (qs.total_logical_reads + qs.total_logical_writes) 
			/qs.execution_count as [Avg IO],
            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,
		qs.sql_handle,
		qs.plan_handle,
		qp.query_plan
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
ORDER BY 
       [Avg IO] DESC

Open in new window

0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question