Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

6 Answers

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
add comment

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
add comment

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
add comment

SQL Server Profiler's good.

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

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-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
add comment

We've been using an open source tool called zabbix. http://www.zabbix.com - 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
add comment

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
add comment

Your Answer

 
discard

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.