How to do periodic Sql Server performance monitoring and troubleshooting?

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

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.

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.

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.

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.

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?