Archive for November 14th, 2013

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.

Continue reading...