What tools are people using to measure SQL Server database performance?

Question

I've experimented with a number of techniques for monitoring the health of our SQL Servers, ranging from using the Management Data Warehouse functionality built into SQL Server 2008, through other commercial products such as Confio Ignite 8 and also of course rolling my own solution using perfmon, performance counters and collecting of various information from the dynamic management views and functions.

What I am finding is that whilst each of these approaches has its own associated strengths, they all have associated weaknesses too. I feel that to actually get people within the organisation to take the monitoring of SQL Server performance seriously whatever solution we roll out has to be very simple and quick to use, must provide some form of a dashboard, and the act of monitoring must have minimal impact on the production databases (and perhaps even more importantly, it must be possible to prove that this is the case).

So I'm interested to hear what others are using for this task? Any recommendations?

Answer 1

RedGate's SQL Response is definitely a great tool for the job.

EDIT #1

There is also SQL IO, which tests the I/O of SQL Server. You will have some further information following the link provided.

There are other performance testing tools such as DBMonster which are open source (you need to scroll down).

I can't remember the name of the absolute testing tool I already referenced here on SO. I shall write it here when I found out.