SSRS Record & Play testing

In my previous article I wrote about SSRS performance measurement. This time we will take a look at how we can make use of this data for our performance testing itself.

To test we would need the report, report path and the parameters; all of these we can get from the report server itself

Select catalog.name, [Catalog].Path,
ExecutionLog.Parameters
from ExecutionLog with (nolock)
inner join
 Catalog on Catalog.ItemID= ExecutionLog.ReportID

This is like our manual tests being recorded, lets see how we can use this for replaying this rest.

As you know, we can access SSRS reports directly from  the server using reportServer web service URL. Using this URL we can pass query strings too!

<iframe height="100px" width=100%" src="http://172.16.150.29/ReportServer/Pages/ReportViewer.aspx?#ReportPath/ReportName#&amp;rs:Command=Render&amp;#Parameters#"/>

Use excel to import the results and use =CONCATENATE(A2,B2,C2,D2,"""/>")  in excel to generate this HTML code. Now copy these line and place it between HTML tags and your page which can open all reports in one go is ready.

Open this HTML page from multiple machines or tabs; so that it generates sufficient load. Use report server ExecutionLog view to measure the performance of your test.

How bad is your SSRS report performance?

In SSRS, the execution data is stored in a table called ExecutionLogStorage table in ReportServer database. You can view this data using the views provided by SSRS named ExecutionLog3, 2 and ExecutionLog. All these views read the data from same table, with only difference is that they interpret data and give you different views.

Following query will give you data on which report sucks.

use ReportServer
go
-- overall suckers
Select Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from  ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TotalTime DESC

-- Query sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeDataRetrieval DESC

-- SSRS Processing sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeProcessing DESC

-- SSRS Rendering sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeProcessing DESC