Hi folks
The activity monitor in sql2k8 allows us to see the most expensive queries. Ok, that's kewl, but is there a way I can log this info or get this info via query analyser? I don't really want to have the Sql Management console open and me looking at the activity monitor dashboard.
I want to figure out which queries are poorly written/schema is poorly designed, etc.
Thanks heaps for any help!
Would the SQL Server Profiler do what you need? I haven't used 2008 yet so I don't know if the tool is still in there but if it is I believe you can set up a trace to log queries that meet specific criteria (such as those that execute and drive CPU up above a certain threshold).
We've used this on our project and it did a pretty good job of helping us troubleshoot poorly executing queries (though don't leave it on full time, rely on the general Windows Performance Counters for performance health tracking).
I had never heard of this tool before, but Microsoft provides a set of reports that do a fantastic job of giving you exactly this - including slowest queries. Check out their Performance Dashboard Reports.
Not sure if they're SQL 2008-compatible, but worth checking out.