2

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
aduguid
  • 3,099
  • 6
  • 18
  • 37
Ketan Kotak
  • 942
  • 10
  • 18
  • Can we see the query or at least understand what the query is doing please? - also are you using sharepoint integrated or standalone? – jimmy8ball Jun 09 '17 at 12:27
  • You may also have an issue with parameters - there is a related article here: https://stackoverflow.com/questions/23963262/sql-runs-slow-in-ssrs-but-fast-in-ssms – jimmy8ball Jun 09 '17 at 12:33
  • Query is as follows : – Ketan Kotak Jun 09 '17 at 12:35
  • 1
    as suggested in the link provided in my previous comment, the issue sounds like a parameter sniffing problem, well known can you review the page I have suggested, with particular focus on the use of OPTION (RECOMPILE) - please let us know if that resolves your problem. – jimmy8ball Jun 09 '17 at 12:42

1 Answers1

1

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

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • `TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount 2017-06-09 17:06:40.210 2017-06-09 17:16:12.357 558102 12641 1318 Live rsSuccess 67744 21766 2017-06-09 17:05:39.427 2017-06-09 17:15:05.837 553992 11381 1001 Live rsSuccess 67744 21766 2017-06-09 16:56:33.577 2017-06-09 17:05:29.253 522674 11997 970 Live rsSuccess 67744 21766` – Ketan Kotak Jun 09 '17 at 12:56
  • So it's always query execution time that takes almost 10 minutes, not RENDERING. Why did you write " SQL query doesn't take any time. but report rendering takes a lot of time to display the data"? – sepupic Jun 09 '17 at 13:02
  • Do you mean you executed "the same" in management studio and it took no time at all? – sepupic Jun 09 '17 at 13:06
  • yes. in ssms it takes just 1 second to give the output – Ketan Kotak Jun 12 '17 at 10:10
  • same sp I am calling from report – Ketan Kotak Jun 12 '17 at 10:10
  • This is parameter sniffing. You have 2 different plans for your sp when launched from SSMS and from reporting. If you want to find tha cause you should take both execution plans from the cache and examine them, if you want a quick solution you can just append option(recompile) to problem statement – sepupic Jun 12 '17 at 10:14