Warm up Reporting Services for quicker reporting

You might already know AX2012 R2 CU7 brought a new helper class for warming up Reporting Services so it doesn't "fall asleep" during long periods of inactivity. The new class has already been blogged about, but in this post I will show to set up the batch and also give you some hints on how to find potential reports you can target if you want to extend the class.

Setting up the batch

Now, I'm going to set this up for every 10 minutes, and not every minute as some might suggest.

The class is dependent on a SSRS report that needs to be deployed, so if you haven't done so already, find the report and deploy it.

After making sure the report is available, you need to locate the class in the AOT. Open the class to run it. Choose batch and define recurrence and alerts.

When the report runs, it will save the result as a PDF to the temporary folder on the AOS. You may open the report and view it, if you're really interested. The point is not the report as much as keeping the service "warm" and also provide a pattern for warming up other potentially slow running reports.

You may want to make sure you don't get a log entry for each successful batch run. I prefer to keep Errors Only for these types of frequent jobs.

Finding potential reports for extending the warmup

Reporting Services logs the execution of the reports and there are a lot of good statistics you can use to investigate potential performance issues. For this example I will run a SQL Query that gives me frequently run reports and load some metrics. I'm looking for reports run the last month with more than 25 runs and where there are more zero rows. These reports could be reports I would want to keep warm.

SELECT 
  COUNT(REPORTPATH) AS RUNS, 
  REPORTPATH, 
  MAX(TIMEDATARETRIEVAL) AS MAX_DATA ,
  MAX(TIMEPROCESSING) AS MAX_PROCESSING,
  MAX(TIMERENDERING) AS MAX_RENDER,
  MIN(TIMEDATARETRIEVAL) AS MIN_DATA ,
  MIN(TIMEPROCESSING) AS MIN_PROCESSING,
  MIN(TIMERENDERING) AS MIN_RENDER
FROM EXECUTIONLOG2 
  WHERE 
     STATUS IN ('RSSUCCESS') 
  AND REPORTPATH NOT IN ('UNKNOWN')
  AND TIMESTART > DATEADD(m,-1,GETDATE())
  AND BYTECOUNT > 0 AND [ROWCOUNT] > 0
GROUP BY REPORTPATH
HAVING COUNT(REPORTPATH) > 25
ORDER BY COUNT(REPORTPATH) DESC

Here are a snippet of the results. You can clearly tell even the warmup report has different metrics for MAX and MIN. These are milliseconds, but if you see reports spending several thousands of milliseconds processing or rendering just a few rows, you may want to investigate why.

Coming from pretty quick and performant MorphX reports to SSRS might be painful both for us AX consultants and AX end users, but then we also see some reports perform stunningly if they were run just minutes ago. You would think they should run just as slow each time. They don't, so maybe we should figure out why that is. Keeping Reporting Services warm is part of the solution.