January 5, 2015 at 12:47 pm
Ran into a small issue with SSRS today. I haven't really worked on the backend of SSRS too much, but I had a SSRS report that executes a stored procedure continiously time out on the results. When examining the stored procedure in the database, the execution of the report is flawless. It even ran one time in SSRS perfectly, but now timing out since then even though it runs perfect within SQL Server.
What am I missing here?
Stored procedure exists.
Stored procedure runs flawlessly.
Stored procedure returns correct results flawlessly.
SSRS report that executes stored procedure times out.
Manually querying stored procedure within SSRS times out.
SSRS security hasn't changed. Looks good.
SSRS report ran flawlessly in the past.
January 5, 2015 at 12:52 pm
Have you checked other reports running on that same SSRS instance?
Do those same reports access the same database with the same data source as the failing report?
Are these reports running successfully?
January 5, 2015 at 12:57 pm
logitestus (1/5/2015)
Have you checked other reports running on that same SSRS instance?Do those same reports access the same database with the same data source as the failing report?
Are these reports running successfully?
Yes to all 3 questions. Forgot to mention that.
I checked a few reports. They all use the same database and schema's. This one in particular copies into a few temporary tables, aggregates and then drops the temporary tables when the report is finished. I tested a similar report that does the same process and that report still works perfectly via SSRS. This one however is still timing out.
When I try to refresh the dataset in report build or BID's it still hangs. Very odd.
January 5, 2015 at 1:09 pm
I have ran into something similar in the past where SSRS choked on the number of records the final dataset kicked out. This was due to bad query performance (and that SSRS ran out of memory while attempting to render the "massive" report).
I can't remember off the top of my head whether or not the dataset is written temporarily to the ReportServerTempDB or not in SQL2008.
Something I would try to find out is if you can repro this issue with one of the similar "working" reports by running it repeatedly. If you can then I would start looking at perfmon counters to see if this is a memory/disk/cpu (I really doubt but without in-depth knowledge of your system config, I would suggest checking everything).
January 5, 2015 at 1:14 pm
logitestus (1/5/2015)
I have ran into something similar in the past where SSRS choked on the number of records the final dataset kicked out. This was due to bad query performance (and that SSRS ran out of memory while attempting to render the "massive" report).I can't remember off the top of my head whether or not the dataset is written temporarily to the ReportServerTempDB or not in SQL2008.
Something I would try to find out is if you can repro this issue with one of the similar "working" reports by running it repeatedly. If you can then I would start looking at perfmon counters to see if this is a memory/disk/cpu (I really doubt but without in-depth knowledge of your system config, I would suggest checking everything).
The report itself is aggregating hundreds-of-thousands of records using temporary tables, but only returning about 8 records in the final result set. Even running it for a small data time period is timing out.
I did notice the tempdb had a few temp tables that are named similar to those created and dropped in the stored procedure itself. I'm assuming someone ran the report and canceled the report mid-way through the aggregation. Now the tables are still are the tempdb.
January 5, 2015 at 1:28 pm
What happens to the "bad" report's execution if you drop those "temp" tables?
January 6, 2015 at 11:26 am
Yeah, nothing changed.
January 6, 2015 at 11:59 am
What does the result of this show for this exact report (you may need to tweak the query to tailor to your needs)? SELECT TOP 1000 STATUS,
[ReportPath]
,[Format]
,[Parameters]
,[TimeStart]
,[TimeEnd]
,(TimeDataRetrieval * .001/60 ) [ExecutionTime(min)]
,([TimeProcessing] * .001/60 ) [ProcessingTime(min)]
,([TimeRendering] * .001/60 ) [RenderTime(min)]
,[ByteCount]
,[RowCount]
FROM [ReportServer] .[dbo]. [ExecutionLog2]
WHERE Source = 'LIVE' AND Status = 'rsSuccess'
AND TimeStart >= '2015-01-06 12:30:07.887' Look for the status and the TimeDataRetrievalTime (time spent by the procedure executing) - Just wanting to make sure if it's hanging on the data / report processing / or rendering part of the action
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 6, 2015 at 12:55 pm
In terms of the last time it successfully ran, it was the following for execution time, processing time and rendering time. As you can see. It's not a dreadfully slow report and processes and renders rather fast for the amount of data it's crunching.
0.0132330.0001660.000150
Then after this last success, it just stops? It ran hundreds of times before. I swear, I'ma about to smack SQL Server Reporting Services. :hehe:
Here is the 30 minute run time one that failed in same order of execution time, processing time and rendering time. Execution and processing hit, rendering zeroed out. So, it looks like the execution of the procedure is hanging.
30.8761330.0007330.000000
Question now is why is it hanging from SSRS to SQL Server? Manually executing the stored procedure works.
January 6, 2015 at 1:09 pm
I've seen this happen with a lot of temp tables (and for the life of me I can't recall the exact reason for its cause); but try replacing some of them with "parameter" tables
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 6, 2015 at 1:12 pm
I temporarily resolved the issue by backing up the old stored procedure under a new name and then recreating it under the original name. The report now runs as normal under multiple executions.
I'll re-examine the backup and see what I'm missing here. If it's with the temp tables, then this is obviously bound to happen again. So far, the permissions and code look the same when compared. Seems the issue is specifically with executing the stored procedure from SSRS.
January 6, 2015 at 1:20 pm
Try to update statistics.
Try to sp_recompile your stored procedure.
If it doesn't help then you need to get execution plan of your stored procedure when it is been running from report. You can get it using SQL Profiler or download and install sp_WhoIsActive stored procedure and run it when your report is running. It will show execution plan as well as a lot of information about why you SP might be slow.
January 6, 2015 at 1:22 pm
xsevensinzx (1/6/2015)
I temporarily resolved the issue by backing up the old stored procedure under a new name and then recreating it under the original name. The report now runs as normal under multiple executions.I'll re-examine the backup and see what I'm missing here. If it's with the temp tables, then this is obviously bound to happen again. So far, the permissions and code look the same when compared. Seems the issue is specifically with executing the stored procedure from SSRS.
Most likely execution plan was cached with "bad" parameter values.
January 6, 2015 at 1:24 pm
I temporarily resolved the issue by backing up the old stored procedure under a new name and then recreating it under the original name. The report now runs as normal under multiple executions.
Woah hold on...that could be a completely different issue. Basically renaming the proc and creating a new one with the same name is just getting rid of the cached execution plans/marking it for a recompile - so when it runs again SQL is potentially getting a more optimal plan.
Sounds like data may be changing in the tables referenced in the procedure...you might want to look at viewing the statistics on those tables (or just look at the estimated versus actual row counts in your execution plans - if they're WAY OFF, you might want to update your statistics for those tables)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 6, 2015 at 1:25 pm
+1 Alexander Suprun
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply