Slow Datasets

sp_BlitzRS® Result: Slow Datasets

Good news! You get to blame something other than the Report Server for this!

(Well, not quite.)

Slow Datasets are identified by having an average TimeDataRetrieval value of 5 seconds or longer. This means once the datasets are requested from the data source, it takes 5+ seconds before those results come back to the Report Server. Because the report engine will wait patiently for all datasets to come back before continuing to process the report, it only takes one slow dataset to hold up the entire process.

How to Fix the Problem

The troubleshooting process depends on what version you’re running, and whether or not you’re using shared datasets.

IF YOU HAVE SHARED DATASETS

You can track the performance of the dataset through the ExecutionLog2/3 view. Run this query against the ExecutionLog view using the report path listed in the sp_BlitzRS Details column:

SELECT eld.InstanceName, eld.ItemPath, eld.UserName, eld.ExecutionId ,
 eld.Parameters, eld.ItemAction, eld.TimeStart, eld.TimeDataRetrieval
FROM dbo.ExecutionLog3 AS elr
JOIN dbo.ExecutionLog3 AS eld ON eld.ExecutionId = elr.ExecutionId
WHERE elr.ItemPath = '/AdventureWorks Sample Reports/Employee_Sales_Summary'
AND elr.TimeStart BETWEEN '2014-08-29' AND '2014-08-30' /* Narrow the results down using TimeStart */
ORDER BY eld.TimeStart DESC

SELECT eld.InstanceName, eld.ItemPath, eld.UserName, eld.ExecutionId ,

eld.Parameters, eld.ItemAction, eld.TimeStart, eld.TimeDataRetrieval

FROM dbo.ExecutionLog3 AS elr

JOIN dbo.ExecutionLog3 AS eld ON eld.ExecutionId = elr.ExecutionId

WHERE elr.ItemPath = '/AdventureWorks Sample Reports/Employee_Sales_Summary'

AND elr.TimeStart BETWEEN '2014-08-29' AND '2014-08-30' /* Narrow the results down using TimeStart */

ORDER BY eld.TimeStart DESC

The report and its shared datasets will be grouped together using the same ExecutionId, and should appear consecutively in the results. Find the dataset(s) with a high TimeDataRetrieval and tune the queries behind them. The Parameters column will give you exactly that: the parameters you’ll want to run the dataset’s query with in order to get a relevant execution plan. If all the listed datasets are fast — say, 30ms — but the report’s TDR value is high — like 500ms — then there may be an embedded dataset. Check the report definition for other datasets and investigate from there.

If you don’t have shared datasets…

…and if you’re on Reporting Services 2008 or 2008 R2:

This will be a more tedious process. You’ll have to capture the individual dataset execution times through either Profiler or Extended Events. In order to confirm you’re hitting the right dataset, check the report definition and see what datasets are involved. Using the parameters supplied by the ExecutionLog query above, run the T-SQL/stored procedures contained in the report and see how long they take.

…and if you’re on Reporting Services 2012 or later:

You are rewarded for using modern technology! You’ll like this better than the pre-2012 method, I promise.

Start by finding the report in the ExecutionLog3 view where the TimeDataRetrieval column exceeds 5000 (the numbers in this column are in milliseconds). Scroll to the right and find the AdditionalInfo column. Click on the XML in the AdditionalInfo column. The embedded dataset info will be listed under the <DataSets> node. For each embedded dataset, look at the <QueryPrepareAndExecutionTime> element for the total milliseconds. That should give you enough information to identify and tune the slowest datasets.