CODEWITHSUNDEEP
sql-serversql-server-2008reporting-services
Ketan Kotak
Ketan Kotak

Reputation: 957

SSRS Report takes almost 10 minutes to render report

We have a development server which runs SQL Server 2012 (SQL Server 11.0.2100). We have configured separate SSRS instance as we are running multiple projects on the same server.

SQL Server: WIN-SRVR\SQL2012 SSRS Instance: WIN-SRVR\SQL2012SSRS

We have a database in 2012 instance. SSRS instance Dataset pointing to 2012.

Now the scenario is when we execute stored procedure it takes less than 1 second to return the dataset. while the same report takes almost 10 mins to render on the page. I have tried to generate directly from http:// WIN-SRVR/Reportserver2012 from the browser as well as Report Builder. Every time it takes a lot of time to display the report.

Points I have checked are:

  1. 64 GB RAM
  2. Xeon Processor
  3. Average memory utilization 18 GB (Free memory > 40 GB )
  4. Dataset is returning approximately 4000 rows. which I guess easily handled by 4 GB RAM server.
  5. Application, DB Server, SSRS Server all are on the same machine.

Activity Monitor shows one suspended query while generating this.

ReportServer.dbo.GetSessionData;1

I could see expensive or waiting query is: EXEC #am_get_querystats

Checked for resource locking on the databases and this shows that ReportServer.dbo.GetSessionData;1 is getting blocked by ReportServer.dbo.WriteLockSession;1. This continues for ~ 2 minutes the lock is released and the report finishes generating.

Troubleshoot I have tried

  1. Restart SSRS Service.. SQL Service. All 4 services including Agent and Browser.
  2. Restart Server
  3. Rebuild all indexes on the server. check execution plan and if any index was missing. created it.
  4. Disable all running job.
  5. Generate report directly on the server using report builder and browser.

Nothing helps.

Following are the screenshot of Activity Monitor

screenshot1 screenshot2

Anyone having an idea why does this happen when SQL query doesn't take any time. but report rendering takes a lot of time to display the data.

    DECLARE @fromdate VARCHAR(50)
    DECLARE @todate VARCHAR(50)
    DECLARE @usagetypeid NVARCHAR(10)
    DECLARE @paytype INT
    DECLARE @userid INT

    SET @fromdate = '01/01/2017'
    SET @todate = '09/06/2017'
    SET @usagetypeid = '0'

    SET @paytype = 0
    SET @userid = 1



    SELECT  CONVERT(VARCHAR, pb.PaymentDate, 103) AS PaymentDate ,
            pm.OldFormNo AS PropertyFormNo ,
            ROUND(CAST(pb.ReceiptNo AS NUMERIC), 0) AS ReceiptNo ,
            ISNULL(pm.OwnerFirstName, '') + ' ' + ISNULL(pm.OwnerMiddleName, '')
            + ' ' + ISNULL(pm.OwnerLastName, '') AS UserPersonName ,
            pm.OwnerFirstName AS 'OwnerFirstName' ,
            pb.PaidAmount ,
            pb.PayableAmount ,
            pb.ChequeDDno AS ChequeNo ,
            pb.BillPaymentId
    FROM    propertymaster pm
            JOIN PropertyBillPayment pb ON pm.PropertyId = pb.PropertyId
    WHERE   pb.PaymentDate BETWEEN CONVERT(DATE, @fromdate)
                           AND     CONVERT(DATE, @todate)
            AND pm.UsageTypeId = CASE WHEN @usagetypeid = 0 THEN pm.UsageTypeId
                                      ELSE @usagetypeid
                                 END
            AND pb.isDeleted = 0
            AND pb.cancel = 0
    ORDER BY pb.PaymentDate DESC

Upvotes: 2

Views: 4830

Answers (1)

sepupic
sepupic

Reputation: 8697

The first step is to find out what takes so much time, you can find it using ExecutionLog3 view of your ReportServer database. This view contains TimeDataRetrieval, TimeProcessing and TimeRendering columns and you can filter it by TimeStart and ItemPath.

Onced you know for sure it's rendering you can investigate on pagination. If instead it's TimeDataRetrieval than you should tune your query

Upvotes: 1

Related Questions