SSRS – Chart concurrent subscription processes

Our Microsoft SQL Server Reporting Services (SSRS) server was experiencing high load during certain times of the day. Here are the SQL queries that I used to chart concurrent subscription processes for analysis.

I wanted to plot a full 24 hours worth of report processing, and I wanted a row for each time an hourly subscription was run, so I created a table in memory to join on that had a row for each of the 24 hours.

I also wanted a metric that represented how much load each subscription put on the server, so I summed up each report’s data retrieval time, processing time, and rendering time for the entire history of data.

I also wanted the report start and average end time, so I used an average of the difference between the start and end times of the report for the entire history of data to get an average runtime.

DECLARE @myCounter int
SET @myCounter = 0

DECLARE @myTable TABLE (myType int, myHour int)
DECLARE @myTable2 TABLE (starttime varchar(8), endtime varchar(8))
DECLARE @results TABLE (myTime varchar(8), myCount int)

WHILE (@myCounter < 24)
BEGIN
  INSERT INTO @myTable
  SELECT 2, @myCounter
  SET @myCounter = @myCounter + 1
END
INSERT INTO @myTable
SELECT 4, 0

SELECT a.Name
, a.Path
, f.weight
, CONVERT(varchar, DATEADD(hour, e.myHour, d.StartDate), 8) AS starttime
, CONVERT(varchar, DATEADD(minute, f.avgruntime + 1, DATEADD(hour, 
  e.myHour, d.StartDate)), 8) AS endtime
FROM ReportServer.dbo.Catalog a
JOIN ReportServer.dbo.Subscriptions b
ON(a.ItemID=b.Report_OID)
JOIN ReportServer.dbo.ReportSchedule c
ON(a.ItemID=c.ReportID AND b.SubscriptionID=c.SubscriptionID)
JOIN ReportServer.dbo.Schedule d
ON(c.ScheduleID=d.ScheduleID)
JOIN @myTable e
ON(d.RecurrenceType=e.myType)
JOIN
(SELECT ReportID
, AVG(DATEDIFF(minute, TimeStart, TimeEnd)) AS avgruntime
, SUM(CAST(TimeDataRetrieval AS bigint) + CAST(TimeProcessing AS bigint) + CAST(TimeRendering AS bigint)) AS weight
FROM ReportServer.dbo.ExecutionLogStorage
GROUP BY ReportID
) f
ON(a.ItemID=f.ReportID)
WHERE d.RecurrenceType IN (2,4)
ORDER BY f.weight DESC, starttime, endtime;

This gave me a good list of reports that I should look into.

Name	Path	weight	starttime	endtime
Report 1	/RSX/Report 1	1855511629	06:00:00	06:10:00
Report 1	/RSX/Report 1	1855511629	08:00:00	08:10:00
Report 2	/RSX/Report 2	128975417	03:00:00	03:14:00
Report 2	/RSX/Report 2	128975417	07:00:00	07:14:00
Report 3	/Travel/Report 1	92511205	07:00:00	07:02:00
...

Next, I really wanted a timeline chart that showed the start and end times of reports so that I could see how the runtimes overlapped, so that we could see if performance could be improved by pacing the subscriptions better.

To do this, I needed to create another in memory table in order to build a map of every minute for the 24 hour period of time. Once I had that table, it required just one more minor addition to the query to generate the chart data.

DECLARE @myCounter int
SET @myCounter = 0

DECLARE @myTable TABLE (myType int, myHour int)
DECLARE @myTable2 TABLE (starttime varchar(8), endtime varchar(8))
DECLARE @results TABLE (myTime varchar(8), myCount int)

WHILE (@myCounter < 24)
BEGIN
INSERT INTO @myTable
SELECT 2, @myCounter
SET @myCounter = @myCounter + 1
END
INSERT INTO @myTable
SELECT 4, 0

INSERT INTO @myTable2
SELECT CONVERT(varchar, DATEADD(hour, e.myHour, d.StartDate), 8) AS starttime
, CONVERT(varchar, DATEADD(minute, f.avgruntime + 1, DATEADD(hour,
e.myHour, d.StartDate)), 8) AS endtime
FROM ReportServer.dbo.Catalog a
JOIN ReportServer.dbo.Subscriptions b
ON(a.ItemID=b.Report_OID)
JOIN ReportServer.dbo.ReportSchedule c
ON(a.ItemID=c.ReportID AND b.SubscriptionID=c.SubscriptionID)
JOIN ReportServer.dbo.Schedule d
ON(c.ScheduleID=d.ScheduleID)
JOIN @myTable e
ON(d.RecurrenceType=e.myType)
JOIN
(SELECT ReportID
, AVG(DATEDIFF(minute, TimeStart, TimeEnd)) AS avgruntime
, SUM(TimeDataRetrieval + TimeProcessing + TimeRendering) AS weight
FROM ReportServer.dbo.ExecutionLogStorage
GROUP BY ReportID
) f
ON(a.ItemID=f.ReportID)
WHERE d.RecurrenceType IN (2,4)

DECLARE @myHour int
DECLARE @myMin int
SET @myHour = 0
SET @myMin = 0

WHILE (@myHour < 24)
BEGIN
WHILE (@myMin < 60)
BEGIN
INSERT INTO @results
SELECT RIGHT('0' + CAST(@myHour AS varchar(2)),2) + ':' +
RIGHT('0' + CAST(@myMin AS varchar(2)),2) + ':00' AS myTime
, COUNT(*)
FROM @myTable2
WHERE starttime <= RIGHT('0' + CAST(@myHour AS varchar(2)),2) + ':' +
RIGHT('0' + CAST(@myMin AS varchar(2)),2) + ':00'
AND endtime >= RIGHT('0' + CAST(@myHour AS varchar(2)),2) + ':' +
RIGHT('0' + CAST(@myMin AS varchar(2)),2) + ':00'
SET @myMin = @myMin + 1
END
SET @myHour = @myHour + 1
SET @myMin = 0
END

SELECT * FROM @results;

This is the chart that I created in Excel using this data, although one could easily turn this into a daily SSRS report.

This chart allowed me to see where the problem areas were and pace out the report subscriptions better.