Post reply

Recent Expensive Queries

  • February 17, 2010 at 3:09 am

    #219217

    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

  • February 17, 2010 at 7:42 am

    #1120090

    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

  • February 18, 2010 at 2:08 am

    #1120729

    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.

  • February 18, 2010 at 2:18 am

    #1120736

    Thanks guys. That will help

    Cheers,

    Mitch

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply