SQL Server Reporting Services performance monitoring


I’ve been doing a little bit of SSRS performance tuning lately and it has been somewhat of a challenge since I’m not all too familiar with Reporting Services. I was however thrilled when I found out about the ExecutionLog2-view in the ReportServer database and the fact that it has timings for the various parts of rendering each and every report that is run. The format of the view is somewhat self-explanatory but there is an xml column with some extra info that needs to be panned out as well, so I created this simple query to get it all out in a nice tabular format. (Detailed descriptions of the columns can be found on Robert Bruckners blog: http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx)

USE ReportServer
GO
SELECT TOP 100
  Report = REVERSE(LEFT(REVERSE(ReportPath),CHARINDEX('/', REVERSE(ReportPath),1)-1)),
  Username,
  Parameters,
  TimeStart,
  TotalDurationMS = DATEDIFF(ms, TimeStart, TimeEnd),
  DurationDataRetrievalMS = TimedataRetrieval,
  DurationProcessingMS = TimeProcessing,
  DurationRendering = TimeRendering,
  [RowCount],
  ReportSizeKB = CAST(ByteCount/1024.0 AS decimal(18, 2)),
  ProcessingEngine = CAST(AdditionalInfo.query('data(/AdditionalInfo/ProcessingEngine)') AS varchar(200)),
  ScalabilityPagination = CAST(AdditionalInfo.query('data(/AdditionalInfo/ScalabilityTime/Pagination)') AS varchar(200)),
  ScalabilityProcessing = CAST(AdditionalInfo.query('data(/AdditionalInfo/ScalabilityTime/Processing)') AS varchar(200)),
  EstMemPaginationKB = CAST(AdditionalInfo.query('data(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)') AS varchar(200)),
  EstMemProcessingKB = CAST(AdditionalInfo.query('data(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)') AS varchar(200)),
  DBQueryTimes = CAST(AdditionalInfo.query('data(/AdditionalInfo/DataExtension/SQL)') AS varchar(200))  
FROM ExecutionLog2 WITH (NOLOCK)
WHERE ReportPath <> 'Unknown'
ORDER BY TimeStart DESC
Advertisements

One thought on “SQL Server Reporting Services performance monitoring

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s