Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wrote a report that is executed by a few hundred users each day and the underlying stored procedure for the data set only takes milliseconds to execute. According to the ReportServer.dbo.ExecutionLog3 table I noticed the TimeDataRetrieval was regularly showing 5-10 seconds. The AdditionalInfo column shows the ExecuteReaderTime is a few milliseconds as expected and the TotalTimeDataRetrieval shows 5-10 seconds. What can I do to reduce the TotalTimeDataRetrieval for each report execution?

A few more useful facts. The server itself has 128 GB of RAM devoted to SSRS and the OS, so it isn't a memory constraint. The number of rows retrieved is 1-20 rows and estimated memory usage is well under 1,000KB.

My best guess is that it is taking about 5-10 seconds to open a connection to the database. I believe this can be substantiated by the ConnectionOpenTime attribute in the AdditionalInfo column. It shows 5-10 seconds and the sum of the ConnectionOpenTime and ExecuteReaderTime equals the TotalTimeDataRetrieval. Perhaps there is a way to force SQL Server to keep connections open longer? Maybe the worker threads are being killed after being idle for more than a few seconds?

share|improve this question
    
are your statistics up to date? –  Mitch Wheat Jan 14 '13 at 23:39
    
Yes. SQL Profile Traces show the stored procedures are taking milliseconds. Internal logging in the stored procedures show they are taking milliseconds. –  Registered User Jan 14 '13 at 23:41
    
Yes, statistics are updated multiple times a day on the underlying table that powers the report. This is not a query performance issue. This is something else. –  Registered User Jan 14 '13 at 23:43
    
Which code? The stored procedure is SELECT Column1, Column2, Column3 FROM SomeDatabase.dbo.SomeTable (NOLOCK) WHERE FilterId = @SomeIntParameter; The report RDL file itself is a simple tablix, a single dataset, and a single parameter. I've noticed the report execution is fine after the first execution completes for a user so long as the ExecutionId doesn't change even if they pick completely different parameter values. When digging through the AdditionalInfo column in ExecutionLog3, it shows the ConnectionOpenTime goes to almost nothing in the subsequent executions. –  Registered User Jan 14 '13 at 23:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.