SSRS Tuning Query-Part III November 14th, 2013

Vinod Kumar

This is in continuation to SSRS tuning Query–Part II series that I have been writing. This particular query is to parse the AdditionalInfo XML data for the TOP 10 longest duration reports from our Reporting Server environments.

SELECT TOP 10
COALESCE(C.path, ‘Unknown’)
AS ReportPath,
Datediff(s, timestart, timeend)
AS ReportExecutionDuration_Seconds,
els.[rowcount],
CASE els.source
 
WHEN 1 THEN ‘Live’
 
WHEN 2 THEN ‘Cache’
 
WHEN 3 THEN ‘Snapshot’
 
WHEN 4 THEN ‘History’
 
WHEN 5 THEN ‘AdHoc’
 
WHEN 6 THEN ‘Session’
 
WHEN 7 THEN ‘Rdce’
 
ELSE ‘Unknown’
END
AS Source,
CASE( reportaction )
 
WHEN 1 THEN ‘Render’
 
WHEN 2 THEN ‘BookmarkNavigation’
 
WHEN 3 THEN ‘DocumentMapNavigation’
 
WHEN 4 THEN ‘DrillThrough’
 
WHEN 5 THEN ‘FindString’
 
WHEN 6 THEN ‘GetDocumentMap’
 
WHEN 7 THEN ‘Toggle’
 
WHEN 8 THEN ‘Sort’
 
ELSE ‘Unknown’
END
AS ReportAction,
CASE
els.additionalinfo.value(‘(/AdditionalInfo/ProcessingEngine)[1]’, ‘integer’
)
               
WHEN 1 THEN ‘SQL 2005’
               
WHEN 2 THEN ‘On-Demand’
               
ELSE ‘Unknown’
             
END
             
AS ProcessingEngine,
             
els.timerendering,
             
els.timeprocessing,
els.additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Pagination)[1]’,
‘integer’)
AS
PaginationScalabilityTime,
els.additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Rendering)[1]’,
‘integer’)
AS
RenderingScalabilityTime,
els.additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Processing)[1]’,
‘integer’)
AS
ProcessingScalabilityTime,
els.additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]’, ‘integer’) AS PaginationMemoryKB,
els.additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]’, ‘integer’)  AS RenderingKB,
els.additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]’, ‘integer’) AS ProcessingKB
FROM   executionlogstorage els
INNER JOIN dbo.catalog c
ON c.itemid = els.reportid
ORDER  BY reportexecutionduration_seconds DESC

This search for newer and more interesting queries will continue and I will keep sharing some of these interesting queries with you from time to time as I work with multiple customer environments. Hope you are finding it interesting.

Tags: , , , , , , ,

This entry was posted on Thursday, November 14th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply