August 5, 2010 at 6:38 am
We have a single SQL Server 2008 server for our production environment.
It has two physical report servers that connect back to it for a data source and for their ReportServer and ReportServerTempDb databases.
We noticed that some of the reports didn't go out via an email subscription and it was apparently due to it timing out.
So looking into it our dev discovered that when they run their subscription on Production it takes 60 minutes but when they run the same subscription on the test server (which is half as "beefy" as the production one) it takes only 20 minutes.
It was run on a non-business day so we didn't have hardly any activity on the production system.
The test server that is running laps around production now (in the past this was not the case) hosts reporting services and SQL on the same physical box.
That test server is hosting the ReportServer and ReportServerTempDb databases, but the connection string (data source) is pointing to production for this exercise.
I'm not really sure how to properly track this down.
The two production report servers NEVER exceeded 15% to 20% CPU or RAM utilization during this time period.
The production SQL server did seem to jump to nearly 80% at a few short intervals, but the overall trend was closer to 20% utilization for the server.
I was wondering:
Could the bottleneck be the ReportServer and ReportServerTempDb databases on the production server?
The test report server had its own dedicated ReportServer databases hosted locally on that machine. It had no activity other than ReportServer database queries as no one else was using the server.
Production had to sustain the load of being the data source for the queries needed to create the reports as well as hosting the single ReportServer and ReportServerTempDB database shared by the two production report servers.
Could that be my problem?
Suggestions on how I can improve performance and troubleshoot this to locate the bottleneck?
(Our development server is also setup the same way as test, and it also outperformed the production SQL server with its two physical report servers)
Thank you.
August 29, 2010 at 3:48 pm
I had a very similar issue and would suggest you look into this which fixed my issues. I was calling Stored Procedures in my SSRS reports and by adding a with recompile to the stored procedures, I went from 30 minutes to execute and timeouts to 2-3 seconds.
Give that a go and if it doesn't help then give me a shout and I will have another think.
Steve.
September 7, 2010 at 8:03 am
Steve Newton (8/29/2010)
I had a very similar issue and would suggest you look into this which fixed my issues. I was calling Stored Procedures in my SSRS reports and by adding a with recompile to the stored procedures, I went from 30 minutes to execute and timeouts to 2-3 seconds.Give that a go and if it doesn't help then give me a shout and I will have another think.
Steve.
I am going to test that in depth, the issue is we have a single sproc that the report uses as a data driven subscription.
Then this single report has ~ 30 subreports that each have their own sproc that drives them (being individual reports).
So I would have to make that change for each of those sprocs.
[EDIT]
I forgot to say, in the execution log, none of the 300 reports run took more than 8 seconds to render, some as little as 2 seconds... so I don't know if a WITH RECOMPILE would really help in this case, since nothing was taking a long time to run, even at 8 seconds per report at 300 reports that gives us 40 minutes total... however, the report server and SQL server had periods of zero activity, so it wasn't churning for a full 40 minutes and 8 seconds was the most it every took reports to run, many were much MUCH faster i.e. 2 seconds.
[/EDIT]
However, while I was getting ready to do that I ran the main sproc that drives the subscription and found that when I executed the report subscription something interesting happened.
I kicked it off at (say) 2:00 PM. Within 3 minutes in the report server execution log I saw 300 entries. There were exactly 300 people who should receive reports, so the master report should have executed 300 times and sent out 300 emails.
So this looked good in the execution log.
But when I checked report manager's status page for the subscription it was listed as processing 50 out of 300...
Then all activity stopped, about 15 minutes some more reports started emailing out, and now I see there are around 590 rows in the execution log and maybe 97 emails sent out... Then finally I get a grand total of 846 rows in the execution log and a grand total of 260 of 300 emails sent out with 40 errors so 260+40=300 exactly.
The time between these start and stop spurts was about 15 minutes each time.
Also, of the 300 reports that should have been generated, and the 846 total rows, not each report was run three times. About 46 reports were only run once (searching for duplicates in the execution log found 46 had no duplicates and the rest of the log was full of duplicates ranging from 2 to 4 iterations).
So the whole thing is quite the quagmire!
I am looking at the error logs on the two report servers.
Event viewer logs are clean, so I'm checking the Report Server Trace Log, but I don't see any errors actually listed there.... perhaps those 40 errors that were reported are NOT going to stored in that log after all?
I expected to see "ERROR..." and then some semi-cryptic lines, that at least would give me something to go on.
Also, what would cause multiple execution like this? Could it be that the two report servers are not viewing themselves as being in a farm together, so they don't realize they should be sharing the workload equally?
In the Execution Log that I searched for duplicates, there does not seem to be any pattern in the sense of which report server the report was executed on. Some reports were executed 3 times on server A and 1 time on Server B, others half and half, and a few only on Server A (all three or four times) or Server B (all three or four times).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply