Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
It could very well be a blocking issue -- I'm guessing production has many more users than just yourself accessing the database. If that's the issue, consider using snapshot isolation for the report queries. –  Jon Seigel Apr 4 '13 at 13:30
3  
Before you jump into blaming concurrency, first find out what the report's query is waiting on. Can you execute the query manually? If so, do a few things while this is happening: Grab the execution plan and what it is waiting on (via sys.dm_os_waiting_tasks). Post that information if you can and we can further troubleshoot. –  Thomas Stringer Apr 4 '13 at 13:38
    
Have you check if the Indexes are defragmented and statistics are up-to-date? Also, is the report running any SP as you might want to recompile the SP if that is the case. –  Kin Apr 4 '13 at 20:57
    
@Kin, Yes the report is running an SP, which calls 5 SP's in order. –  Andrew Duffy Apr 4 '13 at 21:34
    
Any changes happened on the server recently .. like upgrade, etc? You can look into sp_recomile 'proc_name' to recompile the SP's. –  Kin Apr 4 '13 at 21:54

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.