Archive for October 22nd, 2013

SSRS tuning Query–Part II October 22nd, 2013

Vinod Kumar

This is in continuation to SSRS tuning Query–Part I blog post. In this blog post I will bring couple more queries based on ExecutionLog2 View inside Reporting services Database. The first query is to find out the highest average RowCount per report execution. This is a report execution statistics.

SELECT TOP 10 instancename,
reportpath,
Avg(Datediff(s, timestart, timeend)) AS AvgReportDuration_seconds,
Avg(bytecount)                       AS AvgByteCount,
Avg([rowcount])                      AS AvgRowCount
FROM   executionlog2
WHERE  source = ‘Live’
GROUP  BY instancename,
                    
reportpath
ORDER  BY avgrowcount DESC 

In production environments, we want to find out which of the reports failed in their execution? The following query gives us the number of times a report execution failed.

SELECT instancename,
      
reportpath,
      
Count(*) AS NumberOfFailures
FROM   executionlog2
WHERE  [status] <> ‘rsSuccess’
GROUP  BY instancename,
                    
reportpath 

The next query is identifying the most resource consuming report from our Reporting Services environment. This is one the most used query when looking at resource crunched environments.

SELECT
instancename,
reportpath,
Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]’,  ‘integer’), 0) + Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]’,  ‘integer’), 0) + Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]’,  ‘integer’), 0) AS EstimatedMemoryUsageKB,
Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Pagination)[1]’,
‘integer’), 0) +  Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Rendering)[1]’,
‘integer’), 0) +  Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Processing)[1]’,
‘integer’), 0) AS ScalabilityTime_ms
FROM   executionlog2
WHERE  reportaction = ‘Render’
      
AND format = ‘RPL’
ORDER  BY estimatedmemoryusagekb DESC,
         
scalabilitytime_ms DESC 

We will keep sharing many more of such queries that I use in day-today at my work in future too. Do let me know if you would like these kind of blog posts for future too.

Continue reading...