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?