WikiDBA

Hi Guys, Here I am starting How To series, hope it will be helpful for SQL’s lovers.

It’s a daily routine we see system reports where all things like Server health, DB Status, Jobs Status, Disk Spaces … etc. are as per defined standard or not. Hope you guys also worked/managed SSRS – report server also. Hope you have also got queries / complaints from your internal or external clients stating reports are slow / reports are not being generated / reports server is not working / reports are being generated from long time …… etc.
Here is a query, it will show where are problems – is it related to data Retrieval or related to data processing or where it is related to rendering. Below query will show all details

Use ReportServer — Use configured DB Name
GO

SELECT EL.ReportID ‘Report ID’,
CT.name ‘Report Name’,
CT.Path ‘Report Path’,
CASE
WHEN EL.RequestType = 0 THEN ‘Interactive’
WHEN EL.RequestType = 1 THEN ‘Subscription’
WHEN EL.RequestType = 2 THEN ‘Refresh Cache’ END AS ‘Request Type’,
EL.Format ‘Report Format’,
EL.TimeStart,
EL.TimeEnd,
DATEDIFF(ss,EL.TimeStart,EL.TimeEnd) AS ‘TotalDuration(Sec)’,
(EL.TimeDataRetrieval/1000.00) AS ‘Data Retrieval Time (Sec)’,
(EL.Timeprocessing/1000.00) AS ‘Processing Time(Sec)’,
(EL.TimeRendering/1000.00) AS ‘Rendering Time(Sec)’,
CASE
WHEN EL.Source=1 THEN ‘LIVE’
WHEN EL.Source=2 THEN ‘Cache’
WHEN EL.Source=3 THEN ‘Snapshot’
WHEN EL.Source=4 THEN ‘History’
WHEN EL.Source=5 THEN ‘Ad hoc(Report Builder)’
WHEN EL.Source=6 THEN ‘Session’
WHEN EL.Source=7 THEN ‘Report Definition Customization Extension(RDCE)’
END AS ‘Source’,
EL.Status,
EL.ByteCount/1024.00 AS ‘Size(Kb)’,
EL.[RowCount] AS ‘Number of Records’
FROM ExecutionLog EL
INNER JOIN [Catalog] CT ON CT.itemid=EL.reportid
Order by EL.TimeStart Desc

Use it and please share the comments/views/your finding on same – Happy Reading !