Sporadic SSRS Report Performance

Question

I have an SSRS instance with a dashboard report that frequently runs 100-200 times per minute throughout the organization. Most of the time, the report runs quickly with consistent times for data retrieval, processing, and rendering. The rowcounts don't vary much.

On some occasions, the report experiences poor performance. Data retrieval times increase a bit. Processing and rendering times increase dramatically. However, the number of report executions and the rowcounts don't change much.

Solarwinds Database Performance Analyzer is monitoring the SQL instance that hosts the SSRS databases. During the periods of poor report performance, DPA shows stored proc ReportServer.dbo.CheckSessionLock is generating a large number of LCK_M_S waits and stored procedure ReportServer.dbo.WriteLockSession is generating a large number of LCK_M_X waits.

Not surprisingly, there is a spike in the number of blocked sessions (along with a corresponding increase in the number of active sessions):

Within the SSRS log files I see this error during the periods of poor performance:

ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

The main report has 14 sub-reports, and they all use two shared data sources. I've edited the connection string to include the max pool size parameter. I've incremented the value from 150 to 250 to 1,000 to 2,000 and finally to 4,000. I have not tried 0 yet (unlimited). Increasing the parameter has not made any perceived improvement to the situation.

As for the sub-reports, it's my understanding their executions are not logged directly in ReportServer.dbo.ExecutionLog3. The [AdditionalInfo] column of XML data has some info that can be parsed out. But the info I found was for data retrieval times, not processing or rendering time. Even looking at the data retrieval times (which only have a slight to moderate increase during the poor performance periods), I do not see any indication of a specific sub-report being the culprit.

Other Notes: The SQL Server instance hosting the SSRS databases is a dedicated instance--there are no other user databases. SSRS data sources generally are connections to another SQL instance on a different server.

I'm not sure what the problem is. Is there something wrong with the SSRS/SQL environment? Or is the report design the problem?

UPDATE
It has dawned on me that the max pool size parameter that I set for the shared data sources has nothing to do with the "Timeout expired" error message. That is referring to the connection to the SQL Server instance that hosts the [ReportServer] database (not the SQL Server instance that hosts the database for the report data source). I have looked for a way to change connection string parameters for the SSRS service, but the connection string info is encrypted and stored within file RSReportServer.config in the <Dsn> node. The connection can be changed via the rsconfig.exe utility. But it does not appear to offer a way to set every connection string parameter.

Here is what's going on with SQL client connections from the SSRS service executable to the [ReportServer] database, according to Perfmon:

Under normal conditions, there are 80-100 pooled connections and about 10 active connection pools. Report performance started to suffer at 6:36pm. Over the course of the next 8-10 minutes, the number of pooled connections ramped up to over 2,000. But the number of connection pools stayed roughly the same. Shortly after 6:45pm, the SSRS service restarted.

If the SSRS service executable creates connections with the default max pool size of 10, I can see where there would be a problem. If the parameter could be pushed to a higher number, would SSRS eventually "right the ship"? Or would it simply prolong the agony and delay an inevitable restart of the service?