Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What are the best tools and processes for periodically monitoring and troubleshooting sql server performance issues?

share|improve this question
You should accept an answer. – Jeremy Roberts Oct 21 '09 at 13:56

Something that we use is SQLH2 (SQL Server Health and History). It's created as an add on pack from Microsoft and has a good set of "get you going" reports for SQL Servers, and it's free!

SQL Server Health and History MSDN

If you are only looking for periodic heath checks, then you can set these reports up on a schedule to email them to you weekly.

If you start to notice a problem, either from the reports or from user reports then you start to look into Perfmon and SQL Trace for clues and more specific diagnostics.

share|improve this answer
I saw this from another post you made and set it up in a few minutes. Very good stuff. – Sam May 19 '09 at 16:22

I got roped into being the 'DBA' for my company. I was tasked with this same thing. I came across a blog by Brent Ozar about it. Perfmon is a great way to get started with performance testing. More in depth approach is using SQL Server Profiler. I just downloaded a free ebook from redgate.

share|improve this answer
Moderating you +1 for using my blog. ;-) – Brent Ozar May 19 '09 at 16:58

One of my favorites on Sql Server 2005 is Object Execution Statistics. All of my data access is via stored procedures, so this report shows stats for all procs with currently cached plans.

Right-click a database -> Reports -> Standard Reports -> Object Execution Statistics

Anything that is running slowly or too often will stick out like a sore thumb.

share|improve this answer

SQL Server Profiler's good.

I wrote an article for Simple Talk that included using profiler to get slow-running queries,-part-1/

What I tend to do is run that kind of trace weekly and keep historical results in a table in a monitoring database (somewhere other than the production server). That way it's pretty easy to see trends from one week to the next.

share|improve this answer

We've been using an open source tool called zabbix. - runs on mysql/php.

It does perfmon collection and graphing. Also monitoring/alerting. You can pull any perfmon counter.

Some of the statistical analysis is a bit weak, so we are dumping hourly stats into sql server for custom reports in reporting services to answer questions such as - when will my server be running at an average of 65% CPU if usage continues in a linear fashion?

share|improve this answer

I have been using a great product, Veritas I3, that keeps track of the load of the SQL server and may give you recommendations on missing indexes etc.

I Veritas I3 you can see how much CPU, IO, memory, execution plan etc that every single SQL Statement are using.

But I have to dissapoint you, the product is not for free. :(

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.