Slow SSRS Report in production

Question

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?

Answer 1

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 <> '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.

Answer 2

On the topic of blocking processes, below is a quick and dirty way ofrecording blocking -

--CREATE A TABLE TO HOLD PROCESSES WHICH BLOCKED

CREATE TABLE [monitor].[log_blocking] (
  [database_name] [varchar] (30) COLLATE Latin1_General_CI_AS NULL,
  [waittype] [binary] (2) NULL,
  [waitresource] [nchar] (256) COLLATE Latin1_General_CI_AS NULL,
  [date_captured] [datetime] NULL,
  [lastwaittype] [nchar] (32) COLLATE Latin1_General_CI_AS NULL,
  [program_name] [nchar] (128) COLLATE Latin1_General_CI_AS NULL,
  [cmd] [nchar] (16) COLLATE Latin1_General_CI_AS NULL,
  [loginame] [nchar] (128) COLLATE Latin1_General_CI_AS NULL,
  [text] [nvarchar] (max) COLLATE Latin1_General_CI_AS NULL ) 

--CREATE STORED PROCEDURE TO INSERT A NEW ROW WHEN BLOCKING HAPPENS

CREATE PROCEDURE [dbo].[mon_sp_record_blocking] 
WITH EXECUTE AS CALLER AS set nocount on

    insert into monitor.log_blocking 
    select db_name(dbid), 
      waittype, waitresource, 
      GETDATE(), 
      lastwaittype, 
      program_name, 
      cmd,
      loginame, 
      (select text from sys.dm_exec_sql_text(sp.sql_handle)) 
    from sys.sysprocesses as sp 
    where dbid = DB_ID('database_name') and blocked <> 0

Add the stored procedure to a job, run every 5 minutes or however long you want t check. Not perfect gives you a quick overview.