Identify SSRS reports that have been running over 5 minutes

Question

I would like to identify which SSRS reports have been running over 5 minutes but are still running.

The SSRS executionlog only shows an entry AFTER the report has loaded and the below query does not show me the source of long running queries so I can't tie it to a report.

I would like to use this to kill spids of long running reports (in SQL Server 2012).

 SELECT  st.text,
            qp.query_plan,
            qs.*
    FROM    (
        SELECT  TOP 50 *
        FROM    sys.dm_exec_query_stats
        ORDER BY total_worker_time DESC
    ) AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    WHERE qs.max_worker_time > 300
          OR qs.max_elapsed_time > 300

what is the report running can you see the SQL behind the report? Do you you SQL analyze tool to see what is it doing while waiting? — BobNoobGuy, Jul 28 '17 at 17:49
I can see the Query running with the above query, but how do I tie that to a report? Some reports have multiple queries and some reports share the same queries. — BelgoCanadian, Jul 28 '17 at 18:06
if you cannot see the SQL behind the RDL file then, some db admin may need to chime in. But I often want to know the SQL behind the RDL file. and run it raw in SSMS. if it slow in SSMS then the next action can be taken to optimize the query. — BobNoobGuy, Jul 28 '17 at 19:34

score 0 · Answer 1 · answered Jul 28 '17 at 18:10

0

With Reporting Services installed there is a runningjobs table that you can query. Caveat: What version of SQL Server do you have?

USE ReportServer;

DECLARE @cutoff DATETIME;
SET @cutoff = DATEADD(minute, -5, GETDATE()); --Five minutes ago

SELECT JobID, JobType, JobStatus, RequestName, StartDate 
FROM RunningJobs
WHERE StartDate < @cutoff --prunes records that haven't run for five minutes yet
ORDER BY StartDate --oldest first

answered Jul 28 '17 at 18:10

quest4truth

1,111
9
14

Oddly enough, I cannot get this to show any information. I made a hefty report on purpose but it does not show with this query. I'm running SQL Server 2012 – BelgoCanadian Jul 28 '17 at 18:13
It's been going more than five minutes? – quest4truth Jul 28 '17 at 18:16
Yeah, even if I remove your where clause, it still doesn't show anything. – BelgoCanadian Jul 28 '17 at 18:24

score 0 · Answer 2 · edited Jun 07 '18 at 13:40

here is some read that might help in troubleshooting Slow SSRS report https://technet.microsoft.com/en-us/library/bb522806(v=sql.105).aspx

https://www.mssqltips.com/sqlservertip/2328/sql-server-reporting-services-reports-performance-debugging-and-analysis/

Fast query runs slow in SSRS

SELECT TOP 1000 * FROM[ReportServer].[dbo].[ExecutionLog2]

or

SELECT TOP 1000 * FROM[ReportServer].[dbo].[ExecutionLog3]

or you can take advantage of a 14 days free trial of SQL Monitor from RedGate http://www.red-gate.com/dynamic/products/dba/sql-monitor/download

score 0 · Answer 3 · answered Jul 31 '17 at 07:19

I assume that your are running those reports through SQL server agent jobs. And, this is what I'm using for this,

 SELECT
   *
FROM
    msdb.dbo.sysjobs_view job 
        INNER JOIN msdb.dbo.sysjobactivity activity
        ON (job.job_id = activity.job_id)
WHERE

       run_Requested_date>dateadd(MINUTE,-5,getdate())
    AND stop_execution_date is null)

Identify SSRS reports that have been running over 5 minutes

3 Answers3