Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recent Expensive Queries Expand / Collapse
Author
Message
Posted Wednesday, February 17, 2010 3:09 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 17, 2014 5:41 AM
Points: 93, Visits: 425
Hi guys,

as you know SQL Server 2008 has an activity monitor where you can watch processes, Resource Waits, I/O and so on.
The last chapter is "Recent Expensive Queries"

If you e.g. click to Processes and then on the column Wait Type you get shown that it is read out of a stored Procedure.
I was wondering if there is something similar for the "Recent Expensive Queries".

The reason is simple: I would like to analysis from time to time those queries to see if I can make some improvements to the Database/Indexes.

Cheers,
Mitch
Post #866841
Posted Wednesday, February 17, 2010 7:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:43 AM
Points: 3,309, Visits: 6,700
There are couple of ways in achieving this. You can use the Standard reports that ships with SSMS2008 to achieve this. There you can choose Top Queries by CPU or IO.
Or you can use the DMVs like sys.dm_exec_query_stats along with sys.dm_exec_sql_text to get ad hoc.
Please keep in mind that the DMVs get cleared as soon as you restart SQL Server. If you want to keep historical data of query stats you can save these values into a table with a job and then run queries from your table.


-Roy
Post #867082
Posted Thursday, February 18, 2010 2:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:31 AM
Points: 363, Visits: 1,317
See for example
http://blogs.msdn.com/sqltips/archive/2005/10/05/Top-N-costly-query-plans.aspx

You can also trace those queries with the profiler - for example the "duration" template may be a starting point.
Post #867821
Posted Thursday, February 18, 2010 2:18 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 17, 2014 5:41 AM
Points: 93, Visits: 425
Thanks guys. That will help

Cheers,
Mitch

Post #867829
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse