February 6, 2013 at 9:05 am
SSRS : SQL Server 2008 R2 Ent Ed
I have 2 SSRS instances: S1 and S2 located on two separate physical boxes. Both are using the same SQL Server database DB1 as backend.
I have a parameterized report that is using a stored procedure deployed in the database DB1. This report has been deployed to both SSRS instances: S1 and S2
ISSUE:
When I run the stored procedure in Management Studio, it completes in 40 sec.
When I run the report in S1 and S2 , i am getting vastly different execution times:
on S1: report completes in 1 min
on S2: report completes in 6 min
The interesting detail is that the stored procedure runs the same 40 sec when the report is run on BOTH instances. I can see it in a logging table where the procedure registers itself when it starts and finishes.
So it takes 6 min for data to appear on a page in SSRS manager when the report is run in S2 instance. The bottleneck appears to be in rendering data on screen.
I am not pulling a lot of records: just 40.
QUESTION:
where should I start to troubleshoot an issue like this? Apparently the bottleneck is in S2 instance.
Another interesting point is that the box where S2 is located is actually more powerful than the one where S1 is located.
Thank for your time.
February 6, 2013 at 12:07 pm
Can you delete S2 report and copy S1 .rdl file to S2 and relink it to the data source and run it?
February 6, 2013 at 12:20 pm
February 6, 2013 at 12:49 pm
AlexSQLForums (2/6/2013)
Can you delete S2 report and copy S1 .rdl file to S2 and relink it to the data source and run it?
Alex,
By *copying rdl* you really mean deploying it to SSRS instance, correct? You cannot really *copy rdl to S1*. There was one rdl developed in VisualStudio, when was then deployed to both instances, S1 and S2. So if I delete the deployed report from S2, all I can do to restore it there is to simply redeploy the only rdl I have.
February 6, 2013 at 12:55 pm
@SQLFRNDZ (2/6/2013)
Does S1 and S2 has same configuration of RAM, CPU, traffic and NETWORK Speed ? all of these makes a difference too
S2 [slow one] has actually has more CPU power and RAM than S1; as to traffic and network, I'll have to talk to my sysadmin.
Thank you both for the feedback, Alex and SQLFRNDZ.
February 6, 2013 at 1:11 pm
Sergei Zarembo (2/6/2013)
AlexSQLForums (2/6/2013)
Can you delete S2 report and copy S1 .rdl file to S2 and relink it to the data source and run it?Alex,
By *copying rdl* you really mean deploying it to SSRS instance, correct? You cannot really *copy rdl to S1*. There was one rdl developed in VisualStudio, when was then deployed to both instances, S1 and S2. So if I delete the deployed report from S2, all I can do to restore it there is to simply redeploy the only rdl I have.
Got it. and when you run below query you get 40 seconds on S1 and S2?
select TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, Status, ReportID from executionlog
February 6, 2013 at 2:11 pm
yes , the difference betw TimeStart and TimeEnd =~ 40 sec in both cases:
on S1
TimeStart2013-02-05 15:07:26.037
TimeEnd2013-02-05 15:08:10.15727734
TimeDataRetrieval:27734
TimeProcessing: 48
TimeRendering: 60
on S2
TimeStart :2013-02-05 15:42:59.293
TimeEnd :2013-02-05 15:43:40.430
TimeDataRetrieval :28451
TimeProcessing: 58
TimeRendering : 207
the numbers are similar and according to dbo.ExecutionLog2 both reports should have had the same execution time. Yet on S2 the data APPEARED on screen in 6 min as opposed to 1 min on S1.
February 6, 2013 at 2:24 pm
on S1
<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>35</Pagination>
<Processing>83</Processing>
</EstimatedMemoryUsageKB>
<DataExtension>
<SQL>1</SQL>
</DataExtension>
</AdditionalInfo>
on S2
<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>35</Pagination>
<Processing>83</Processing>
</EstimatedMemoryUsageKB>
<DataExtension>
<SQL>1</SQL>
</DataExtension>
</AdditionalInfo>
February 11, 2013 at 12:38 pm
on s2 there is more data then on s1 so chances are some kind of an aggregate function in your sp could slow it down.
February 14, 2013 at 3:21 pm
AlexSQLForums (2/11/2013)
on s2 there is more data then on s1 so chances are some kind of an aggregate function in your sp could slow it down.
Alex,
what tells you that S2 has more data than S1? Both SSRS instances are looking at the same database. And xml field shows exactly the same stats on both instances in ExecutionLog2 table..
Thank you,
Sergei
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply