It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

More:

Sql 2005 Dynamic Memory Managment with AWE enabled
http://blogs.msdn.com/chadboyd/archive/2007/03/08/sql-2005-dynamic-memory-managment-with-awe-enabled.aspx

Reporting Services Trace Logs
http://msdn2.microsoft.com/en-us/library/ms156500.aspx

Report and Snapshot Size Limits
http://msdn2.microsoft.com/en-US/library/ms156002.aspx

Monitoring Report Execution Performance with Execution Logs
http://msdn2.microsoft.com/en-us/library/aa964131.aspx

How to diagnose issues when running reports in the report server?
http://blogs.msdn.com/lukaszp/archive/2007/01/31/how-to-diagnose-issues-when-running-reports-in-the-report-server.aspx