November 2, 2009 at 9:47 am
Ok I have a report on SSRS 2005 64 bit SP3 and SQL 2005 standard SP2 64 bit on a separate machines.
That report crashes after 30 minutes of "work".
When I monitor all the machines involved the cpu is completely idle, so is the ram and the network.
The query ran from any machine returns all the data in less than 10 seconds. Usually 5 secs.
I've checked the sql and windows log on all servers and local machines and nothing is logged.
When I check the execution history for the rare times where the report actually loads, the TimeDataRetrieval shows almost 30 minutes while the rendering takes only 40 secs.
The query returns around 12k rows with 5-6 group bys in the rendering (category, sub category, year, month, day).
I'm thinking this is a network issue but since I can download the data in 10 seconds at worst from 4-5 different machines on the network and that every other reports works normally, I can't help thinking this is something else.
Anything else I need to check?
November 2, 2009 at 9:48 am
oh ya, and I added 5 GB of space to rstemdb "just in case"... even if the current size was only 350 mb with 200 mb free space.
I'm out of ideas, can't wait to see what you guys can come up with.
November 2, 2009 at 10:05 am
Had a similar issue once with SSRS. I never had the report time out, but it took quite a while to run and compile as I was pulling many thousands of records in my query that got cross-tabbed and such in SSRS.
I ended up speeding it up tremendously by doing all of the aggregation at the db instead of in SSRS. It went from running in 10-15 minutes to like 10-15 seconds.
Since you're pulling that much data across the wire I'd have to look at that as the culprit. I understand the hesitation as in QA or SSMS it probably returns fine, but I think SSRS doesn't handle it quite as well. No hard data or references to back that up, just what I've noticed the few times I've tried to do large data loads in SSRS...
-Luke.
November 2, 2009 at 10:07 am
November 2, 2009 at 10:14 am
Did a bit more googling than I had when this originally came up in my office...
Here's the MS reasoning why you might be seeing the timeouts...
http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
Basically, the session times out because the report is running too long and the suggestion is to make it complete faster (ummm... gee thanks MS).
-Luke.
November 2, 2009 at 10:20 am
That's also what stumps me. When I run other reports with as much or even more data (we're talking about less than 500 KB of data here). They run in seconds as usual. I have to start 5-10 small reports at once just to see a real "long" spike in cpu / ram on the ssrs server. Yet when THAT report "runs", there's absolutely nothing happening.
I've seen the cpu go to 100% for 35 seconds when it's rendered, but even that doesn't happen now.
November 2, 2009 at 10:23 am
Luke L (11/2/2009)
Did a bit more googling than I had when this originally came up in my office...Here's the MS reasoning why you might be seeing the timeouts...
http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
Basically, the session times out because the report is running too long and the suggestion is to make it complete faster (ummm... gee thanks MS).
-Luke.
The report is already as optimized as I can make it on the server... without doing the full pivot or CUBE on the OLTP server. The sums are already done by day & group. The only thing left to do in SSRS is then group by month / year and mega group (made here or imported).
November 2, 2009 at 10:25 am
... and the timeout is at 30 MINUTES. It's already way too long so I can't see how increasing it would really help.
November 2, 2009 at 11:01 am
Can you guys run this query on your SSRS database engine. I need to know if you get the same data as I do. I always get TimeDataRetrieval and TimeRendering = 0 and TimeProcessing way above 0.
SELECT C.Name, E.* FROM dbo.ExecutionLog E
INNER JOIN dbo.Catalog C ON E.ReportID = C.ItemID
WHERE E.TimeStart > '2009-09-09'
AND E.STATUS <> 'RsSuccess'
ORDER BY C.Name, E.TimeStart
November 2, 2009 at 11:15 am
November 2, 2009 at 11:31 am
29 ms, seconds, minutes?
November 2, 2009 at 11:52 am
Ok Thanks, I was hoping to get something more usefull, but apparently not.
I just did this test. I updated the sp to return the top 1% of the data. The query now returns ONLY 133 rows for 2-3K of data. The report STILL DOES NOT RENDER... the search continues!
November 3, 2009 at 6:54 am
Can you also simplify the groupings?
And possibly open it up in BIDS on another machine? I'd think this would be better than just running the query from other machines.
We had a strange one on our PerformancePoint server. A bad port in the data center, with the nic card light not staying on steady.
Moved the cable and marked the port bad.
Greg E
November 3, 2009 at 7:07 am
Ninja's_RGR'us (11/2/2009)
29 ms, seconds, minutes?
I'm gonna guess seconds, though I'm not sure what time segment is stored in that column. I'd imagine that if a report took 29 minutes I would have heard about it. My users get kinda cranky if they don't see their data within say 10 seconds to a minute...
-Luke.
November 4, 2009 at 11:09 am
Please see below.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply