February 17, 2010 at 3:09 am
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
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
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
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