SSRS report runs a lot slower on one of 2 SSRS instances

  • 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.

  • Can you delete S2 report and copy S1 .rdl file to S2 and relink it to the data source and run it?

    Alex S
  • Does S1 and S2 has same configuration of RAM, CPU, traffic and NETWORK Speed ? all of these makes a diffrence too

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • 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.

  • @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.

  • 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

    Alex S
  • 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.

  • 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>

  • 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 S
  • 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