SQL Server Reporting Services Report Performance Comparison
In my career I have had to upgrade the SQL Server Reporting Services (SSRS) platform multiple times in various environments. Often, I need to compare report performance from one instance of SSRS to another, to identify reports that perform worse on a newer platform. Often, Microsoft will introduce features or change how reports are executed, and these changes may adversely affect how reports perform on a newer version of SSRS.
Our goal is to execute every distinct report run in Production in our test environment (usually QA). Often, organizations will have a Production, Quality Assurance (QA) and Development environment at a minimum. For the purposes of our code, we will look at the last run report parameter combination. For SSRS, reports can be run multiple times with different parameters, and it is important to account for those changes in parameters as they can have a large impact on performance.
GetAllReports
Our first step is to gather reports from Production. This query will select the report path and parameter combination, filtering the results to return only one unique entry. The end result is our list of every unique report run in Production. For simplicity sake, I filter out results that are more than ten thousand characters long.
The ExecutionLog2 view is a view provided by the ReportServer database that displays historical statistics of report run time information. It is very useful and we will be using it extensively for our analysis.
Note that the view name can change or be upgraded by Microsoft as they upgrade the version of SSRS, so make sure the view is present in both of the systems you are comparing.
WITH cteRowNumber
AS ( SELECT [ReportPath] ,
[Parameters] ,
[TimeEnd] ,
ROW_NUMBER() OVER ( PARTITION BY [ReportPath] ORDER BY [TimeEnd] DESC ) AS RowNum
FROM [ReportServer].[dbo].[ExecutionLog2]
)
SELECT [ReportPath] ,
[Parameters]
FROM cteRowNumber
WHERE RowNum = 1
AND DATALENGTH([cteRowNumber].[Parameters]) < 10000
ORDER BY [ReportPath]
Next we need to move to Excel. Copy the results of the previous query into an Excel file and create a new column, replacing blanks in the report path column. This is needed so the string will be read by the web browser. The following code will do the trick.
=SUBSTITUTE(A1,” “,”%20”)
Next we need to combine the report path string and the parameters. The following formula will concatenate the two strings as well as add the necessary text to make the string render in a browser. Replace with your target report server.
=CONCATENATE(“:/ReportServer?”,B1,”&rs:Command=Render&”,C1)
RunAllReports
RunAllReports.ps1 is a simple Powershell file that will read each line of a text file, open a new Internet Explorer process and render the result from the URL in the text file. Take this file and put it on the C drive on your SSRS application server.
$textfile = "C:Reports.txt"
ForEach ($url in Get-Content $textfile) {
$IE=new-object -com internetexplorer.application
$IE.navigate2($url)
$IE.visible=$true
Start-Sleep -s 400
$IE.quit()
}
The file needed is called Reports.txt. You can create a new one for the C drive location. Copy and past your Excel data into the text file as a one column list.
Running the Reports
Running the reports is very straightforward. Log into the report server using Internet Explorer and keep a tab open so your credentials are cached, then simply execute RunAllReports.ps1. You’ll need to leave this running for quite a while, depending on how many entries you have in the text file. The duration between entries is currently four hundred milliseconds in the Powershell file. You can change this if needed.
If you are comparing between two different instances other than Production, you may need to repeat this process for the other server. Otherwise you can move on to parsing the results.
Parsing the Results
Once the run is complete, you can work on getting your results. First, copy the ExecutionLog2 view to a working table. You can do a SELECT * to complete this. Next, you may have to change collation and data types for some columns in the table. You may also have to copy it locally to get of the XML data type.
GetReportResults
GetReportResults.sql will select the last run of a report by a combination of report path and parameters. Run this against your data set.
WITH cteRowNumber
AS ( SELECT [InstanceName] ,
[ReportPath] ,
[RequestType] ,
[Parameters] ,
[TimeStart] ,
[TimeEnd] ,
[TimeDataRetrieval] ,
[TimeProcessing] ,
[TimeRendering] ,
[Status] ,
[ByteCount] ,
[RowCount] ,
ROW_NUMBER() OVER ( PARTITION BY [ReportPath],
[Parameters] ORDER BY [TimeEnd] DESC ) AS RowNum
FROM [tempdb].[dbo].[tmpReport]
)
SELECT [InstanceName] ,
[ReportPath] ,
[RequestType] ,
[Parameters] ,
[TimeStart] ,
[TimeEnd] ,
[TimeDataRetrieval] ,
[TimeProcessing] ,
[TimeRendering] ,
[Status] ,
[ByteCount] ,
[RowCount]
INTO [tempdb].[dbo].[tmpFinalReport]
FROM cteRowNumber
WHERE RowNum = 1
CompareReports
After getting two temporary tables representing your two different instances, you are ready to compare the results. CompareReports.sql will join the two tables on the report path and the parameter and generate a side by side view of execution statistics for each instance, showing you retrieval time, processing time and rendering time as well as row counts and total bytes processed.
SELECT b.[ReportPath] AS [ReportPath] ,
b.[TimeDataRetrieval] AS [b TimeDataRetrieval] ,
b.[TimeProcessing] AS [b TimeProcessing] ,
b.[TimeRendering] AS [b TimeRendering] ,
b.[ByteCount] AS [b ByteCount] ,
a.[TimeDataRetrieval] AS [a TimeDataRetrieval] ,
a.[TimeProcessing] AS [a TimeProcessing] ,
a.[TimeRendering] AS [a TimeRendering] ,
a.[ByteCount] AS [a ByteCount] ,
b.[RowCount] AS [b RowCount] ,
( b.TimeDataRetrieval + b.TimeProcessing + b.TimeRendering ) AS [b Total] ,
a.[RowCount] AS [a RowCount] ,
( a.TimeDataRetrieval + a.TimeProcessing + a.TimeRendering ) AS [a Total]
FROM [tmpReportFinal_Production] a
INNER JOIN [tmpReportFinal_Test] b ON a.ReportPath = b.ReportPath
AND a.[Parameters] = b.[Parameters]
You can copy the results to Excel to fine tune your compares and present information. What you’ll end up with is a comparison of environments for the matching report path and parameter based on the initial list of unique reports run in Production from step 1.
DiscoveryOfSuspectReports
If needed, you can run this query which will allow you to discover suspect reports.
SELECT b.[ReportPath] AS [ReportPath] ,
b.[Parameters] AS [Parameters] ,
( b.TimeDataRetrieval - a.TimeDataRetrieval ) AS TimeDataRetrieval_DIFF ,
( b.TimeProcessing - a.TimeProcessing ) AS TimeProcessing_DIFF ,
( b.TimeRendering - a.TimeRendering ) AS TimeRendering_DIFF ,
( b.ByteCount - a.ByteCount ) AS ByteCount_DIFF ,
( b.[RowCount] - a.[RowCount] ) AS RowCount_DIFF ,
( ( b.TimeDataRetrieval + b.TimeProcessing
+ b.TimeRendering ) - ( a.TimeDataRetrieval
+ a.TimeProcessing
+ a.TimeRendering ) ) AS [Total_DIFF]
FROM [tmpReportFinal_Production] a
INNER JOIN [tmpReportFinal_Test] b ON a.ReportPath = b.ReportPath
AND a.[Parameters] = b.[Parameters]
GetThe10WorstReports
This query, while not needed here, allows you to see the top ten worst performing reports for a given instance.
USE ReportServer
GO
SELECT TOP 10
COUNT(Name) AS ExecutionCount ,
Name ,
SUM(TimeDataRetrieval) AS TimeDataRetrievalSum ,
SUM(TimeProcessing) AS TimeProcessingSum ,
SUM(TimeRendering) AS TimeRenderingSum ,
SUM(ByteCount) AS ByteCountSum ,
SUM([RowCount]) AS RowCountSum
FROM ( SELECT TimeStart ,
Catalog.Type ,
Catalog.Name ,
TimeDataRetrieval ,
TimeProcessing ,
TimeRendering ,
ByteCount ,
[RowCount]
FROM Catalog
INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
WHERE Type = 2
) AS RE
GROUP BY Name
ORDER BY COUNT(Name) DESC ,
Name
The code for the T-SQL and Powershell scripts is here