What tools are available to test SQL statement performance?

Question

In the never-ending search for performance (and my own bludgeoning experience), I've learnt a few things that could drag down the performance of a SQL statement.

Obsessive Compulsive Subqueries Disorder
Doing crazy type conversions (and nest those into oblivion)
Group By on aggregate functions of said crazy type conversions
Where fldID in (select EVERYTHING from my 5mil record table)

I typically work with MSSQL. What tools are available to test the performance of a SQL statement? Are these tools built in and specific to each type of DB server? Or are there general tools available?

Answer 1

SQL Profiler (built-in): Monitoring with SQL Profiler

SQL Benchmark Pro (Commercial)

SQL Server 2008 has the new Data Collector

SQL Server 2005 (onwards) has a missing indexes Dynamic Management View (DMV) which can be quite useful (but only for query plans currently in the plan cache): About the Missing Indexes Feature.

There is also the SQL Server Database Engine Tuning Advisor which does a reasonable job (just don't implement everything it suggests!)

Answer 2

I mostly just use Profiler and the execution plan viewer