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!
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.
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
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?
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. :(