SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Services From a Data warehouse - Bottleneck On Every Month


Reporting Services From a Data warehouse - Bottleneck On Every Month

Author
Message
subramaniam.chandrasekar
subramaniam.chandrasekar
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 240
Hi All,

I am a Software Engineer working in an MNC on MSBI Platform.

I have been working in Reporting services project which runs on MSBI platform.We use SSRS,SSIS mostly in this project.

We have a data warehouse in one db server , two report servers .

We create reports by using SSRS & we will run those reports on every month end .

Now we have 2000+ SSRS reports for many clients and we scheduled those reports as subscriptions in those report servers.

On every month end , those SSRS subscriptions will run and query our data warehouse in db server from report servers.

The problem is at the moment, we are not able to run those 2000+ reports on every monthend(30 or 31).

The performance had been very poor.

May i know why?

Can anyone help please ???
doug.brown
doug.brown
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3452 Visits: 18042
Do you mean you've set up the subscriptions and they aren't running, or they've been running just fine but now they're taking much longer than usual? If the latter, I'd start by looking for stale statistics or bad parameter sniffing. Perhaps see if there are a few reports in particular taking much longer and focus on those. Alternatively, was a backup or some other robust update/insert job moved and now runs at the same time as the reports?
subramaniam.chandrasekar
subramaniam.chandrasekar
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 240
Hi,
Many thanks for the reply.

Yes.. There are several SQL jobs running at the same time where the subscriptions were scheduled.

Also many users will also query the tables in data warehouse where the subscriptions are also querying the same tables.

for example, if i am scheduling 110 + ssrs subscriptions at 02:00 AM UK time, those subscriptions were delivered around 08:00 AM UK time...may i know why ?...

I have been facing the issues at regular times....not for 02:00AM..this issue persist for all timings.....

Can you please help me to run my ssrs subscriptions quickly ??

At the moment we can run the reports... But performance had been poor.

Can u help me??
doug.brown
doug.brown
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3452 Visits: 18042
One thing to confirm is whether the reports themselves are taking a long time, or if the emails are being delayed. I've seen problems where mail clients flag reports as potential spam and hold onto them for a while, particularly when a whole bunch of subscriptions are being sent at once. When the emails arrive at 8:00, do they say they were sent at 8:00, or sent at 2:00? If the latter, the issue may be in the email chain rather than with the reports.

This query (run on the server where SSRS lives) will also show you if the reports are taking a long time to run:
--avg runtimes for reports in reporting services in Seconds
SELECT name
, MIN(DATEDIFF(SECOND, TimeStart, TimeEnd)) AS MinRun
, AVG(DATEDIFF(SECOND, TimeStart, TimeEnd)) AS AvgRun
, MAX(DATEDIFF(SECOND, TimeStart, TimeEnd)) AS MaxRun
,COUNT(*) AS times
FROM
ReportServer.dbo.ExecutionLog ex
JOIN ReportServer.dbo.Catalog ct ON ex.ReportID = ct.ItemID
WHERE
TimeStart >= '2016-11-05'
AND Status NOT IN ('rsProcessingAborted','rsInternalError')
--AND Name = 'YGOutofStockRDC' --to see a specific report
GROUP BY name
--ORDER BY name
ORDER BY AvgRun DESC;


subramaniam.chandrasekar
subramaniam.chandrasekar
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 240
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 ???
doug.brown
doug.brown
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3452 Visits: 18042
Your query looks good for finding problem reports. Adding in the byte count was a good touch.

Can you please explain me detail in PAGEIO waitypes ??


I'm not an expert on wait types, but I think you've got the gist of it. My understanding is PAGEIOLATCH_EX waits are involved in pulling data from the disk into memory. High values often mean either contention (which it sounds like is the case for you), or disk bottlenecks.

To deal with contention, one possible option might be to stagger the subscriptions, so instead of all firing off at 2:00, some go at 2:05, some at 2:10. etc. That way some of the the IO load is spread out over time. Another is to identify the worst performing queries and tune them so that they aren't in contention for as long as they are now. Are any pulling up more data than they need and then filtering it down, or are there missing indexes that could speed up reading data off the disks? Those are some of the options I'd be looking at.
subramaniam.chandrasekar
subramaniam.chandrasekar
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 240
hi ,

Thanks for you reply.

As per my earlier investigation,I have done the below,

1)done finding missing indexes
2)tuning of SSRS subscriptions query
3)Optimizing the SQL queries of all stored procedures so that they will take less time to complete

But I shall have to look on IO subsystem from now on......

why my SQL queries are waiting in my data warehouse, irrespective of select,update,delete SQL statements..

My Current Concern:[u][/u]

What is the best possible way to execute any SQL statements within less time span....??

What kind of change that i should need to do to my IO subsystem??

Should i need to look into my hardware config??
doug.brown
doug.brown
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3452 Visits: 18042
Now we're out of my area of expertise. On the best way to get more SQL running in less time, your list of three action items are the best way I know. But for improving IO and hardware, I don't know such stuff. Making the queries more efficient is the only way I know to improve IO; I don't know what options there might be on the DBA side of things. Anyone else have any thoughts?
subramaniam.chandrasekar
subramaniam.chandrasekar
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 240
Hi,

One thing i want to discuss...I had stopped my both Report servers services on today.

My Data warehouse in SQL server might not have been queried by Report Servers.

What did i do ??

Now i have queried my data warehouse for a complex SQL query.

Super..Super..i have got my results within seconds...

usually it will take more mins to execute..now it is just seconds..

Also i have queried my data warehouse for concurrent queries..there are nothing...

So i have found my real problem is.....,

More SSRS subscriptions have been scheduled at same time(multiple requests)

So when i have concurrent SQL queries,SSRS subs queries,Jobs query running against my data warehouse..it cant handle those requests..

Thatswhy those blocking and late delivery of ssrs subscriptions .

Now i need to find a way to execute concurrent SQL queries against my data warehouse...

Can u help please ?
doug.brown
doug.brown
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3452 Visits: 18042
The only suggestion I can offer at this point is trying to stagger the SSRS subscriptions to minimize the concurrency. Maybe if just five of them fire off each minute, that will give them a chance to finish before the next batch runs a minute or two later. There may also be hardware/DB configuration options open to you, but I don't know that side of the SQL world. Hopefully a more informed member of the community will have more info.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search