July 30, 2014 at 11:16 am
Hello
Our SSRS report times is getting worse. We process about 5000 report subscriptions on a daily basis. They start at 2 AM and run about until 5 AM. For the past few weeks, the end time has climbed to 7 am. The number of reports and data has stayed almost the same. We don't see any slowness on the server itself. cpu and mem is good. The only wait types i see is OLEDB and MSQL_DQ, the avg wait is about 9 ms.
Our reports are written to a file server and I am not sure how to figure out if file server is the bottleneck.
we have a 5 node SSRS farm serving reports using 3 data sources. The execution time for the reports look normal (time for data retrieval, rendering and processing). But the end time is getting worse.
Please help me find the cause for the slowness. I ran out of ideas.
thanks in advance
July 30, 2014 at 11:30 am
If your data retrieval and rendering haven't changed, I would suspect the network or destination server to be at fault. How is the space and fragmentation on the file server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2014 at 12:06 pm
we have more than 30 % free space on the drive. and for fragmentation: we are vmware shop with EMC SAN(VMAX thin pools) and EMC doesn't recommend running defrag from windows, even if windows report fragmentation.
I have noticed from windows that write latency go upto 35 ms during peak activity, but from VMAX it looks normal. May be we are hitting the roof on disk queue or file limits.
is there a way to check from SSRS, the delay after the report has been processed and rendered to the time taken for the file being delivered?
thanks
July 30, 2014 at 12:13 pm
I have heard EMC say that about defrag and I have shown them the difference.
You may have a file queue issue.
Sum the data retrieval and data render time and subtract that from the total execution time. That difference is your time outside of SSRS (network and fileserver).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 9:05 am
we are going to split the data across multiple drives and move it to a unshared datastore.
I will let you know if it made a difference or if i find anything else
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply