Slow SSRS Report in production I have an SSRS report which gets its data by firing a series of stored procedures.

Now the report is timing out big time when run in production, yet when I pull down the prod database and restore to development the report runs fine.

I was thinking to set up a sql server profiler trace in production and hopefully that will tell me something... eg high Disk I/O at the time it's being run.

What else should I be doing? Something with perfmon?
You've either got a slow running query or blocking/deadlocking going on.  To see blocking, you could run this query:

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      * 
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName &lt;&gt; 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC

DROP TABLE #sp_who2


Look for anything in the BlkBy column and you can see what session is blocking your queries.

You didn't mention a deadlock error, which you should see in SSRS logs.  I'd take a close look at those to see the exact error and proceed with that knowledge.  It's down in the log dir of the SSRS install.

SSRS will have timeouts set for it, and you may be hitting those times, but your SSRS log should tell you if it's a application timeout or a deadlock.