Hi .,
Thank you for your Reply...
The query what you have shared was very useful.
Can you please review my query ?
Select b.Name,b.Path,a.timestart,a.TimeEnd,DATEDIFF(MI,a.TimeStart,a.TimeEnd) As diff,
a.ByteCount,CAST(a.ByteCount / 1048576.0 AS DECIMAL(10, 2)) as ReportMB,a.Status
from Reportserver.dbo.ExecutionLog a
JOIN
Reportserver.dbo.catalog b
ON
a.ReportID=b.ItemID
where
a.TimeStart>='2016-11-08 00:00:00.000'
AND
a.TimeStart<='2016-11-08 23:59:59.999'
AND
a.RequestType='1'
order by
--CAST(a.ByteCount / 1048576.0 AS DECIMAL(10, 2)) desc
DATEDIFF(MI,a.TimeStart,a.TimeEnd) DESC
This query will show the Reports,timestart,timeend,size,duration etc.,,
Earlier My Understanding :
Problem Statement :
The SSRS reports what we have been scheduled were taking more time to deliver to users..Lets say at 02:00 AM a report is scheduled it is delivered at 01:00PM(10 hours delay)....
My Understanding earlier :
The problem of delivering reports deleyed because,
1)The reports are long running
2)The reports are scheduled at same time( For example at 04:00AM.,300+ Reports have been schdeuled)
3)SMTP server problems
Now My Understanding :
I have found that the Reports delivery issue was persisting because of,
When i query my data warehouse while these SSRS subscriptions were querying my Datawarehouse .,i have found that there were 30+ current running queries available.
when i checked in detail., most of queries are my subscirptions (ssrs).,
Each & every subscirptions' SQL query have been in idle state in my data warehouse.
All my subscriptions query have PAGEIO_LATCH_SH/EX waittypes..
Later i understood that all my ssrs subs queries are waiting for Pagefiles to bring the data from the datadrives or hard drives ...
Can you please explain me detail in PAGEIO waitypes ??
If i found any solution to this .,then i can solve the issue easily...
Can you help please ???