Looking for SQL Server Performance Monitor Tools

Question

I may be approaching this problem from the wrong angle but what I'm thinking of is some kind of performance monitor tool for SQl server that works in a similar way to code performance tools, e.g. I;d like to see an output of how many times each stored procedure was called, average executuion time and possibly various resource usage stats such as cache/index utilisation, resultign disk access and table scans, etc.

As far as I can tell the performance monitor that comes with SQL Server just logs the various calls but doesn't report he variosu stats I'm looking for. Potentially I just need a tool to analyze the log output?

Answer 1

A lot of this stuff is accessible through DMVs at least as long as the execution plan is in the cache. In SQL Server 2005 Management studio if you right click the server Then choose Reports -> Standard Reports is this what you need?

NB: Just remembered I think the reports were introduced in a SP. Can't remember exactly which one though!

Answer 2

The SQL Server Profiler does give you what you want, although perhaps not in a very intuitive manner.

To monitor stored procedures, you can trace the SP:Completed event and watch CPU usage, I/O reads/writes, and duration to get what you want. For cache monitoring, you need to monitor the SP:CacheHit, SP:CacheInsert, SP:CacheMiss and SP:CacheRemove events.

If you want another tool, I can highly recommend Quest's Performance Analysis for SQL Server, which gives you what you want and a lot more in an easy-to-use package.

Answer 3

Turns out the SQL Server Profiler does report duration of stored proc calls against the stored proc name, it's just a matetr of knowing which events to listen to. The SP_Counts template seems to be the best starting point as it reports stored proc names. If you switch to use the 'completed' instead fo 'started' events then you can choose to report the duration of each call. From there you can save a recorded trace to an XML file and write XPath/XQuery to summarise the total and average tiem spent in each stored proc. I ended up writing a little C# application to sumamrise the data and output it to CSV file so I can view it in an excel spreadsheet and sort by the various columns - e.g. slowest stored proc on average, stored proc with most total time, and SP with highest invocation count.