SQL Server Profiler

Here is my preferred configuration for a SQL Server Profiler template when checking query performance from traces. Select the following events:

• RPC:Completed
• SP:StmtCompleted
• SQL:BatchCompleted
• SQL:BatchStarting
• Showplan XML

With these events you get the duration, CPU and reads of every batch and stored procedure statement, plus the execution plan of each query.

A good article on the subject: How to Identify Slow Running Queries with SQL Profiler (Simple Talk).