Looking for SQL Server Performance Monitor Tools [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

Questions asking us to recommend or find a tool, library or favorite off开发者_如何学Go-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it.

Closed 9 years ago.

Improve this 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?

---

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.

---

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.