While optimizing a report, I discovered that the time reported by
ExecutionLog2 was a couple of seconds longer than the query times shown in SQL Server Profiler for that report execution.
After doing a few more things to rule out that the queries were really not running slow, I set Component tracing to Verbose in
<add name="Components" value="all:4" />
The SRRS logs now show an entry like this every time a report is run:
processing!ReportServer_0-2!18d0!01/30/2014-12:11:30:: v VERBOSE: Opening a connection for DataSource: PHData took 2044 ms.
The times are always over 2000 ms. I've tried running a report and then running it again a few seconds later, but it still logs a long delay.
SSRS and the SQL Server instance referenced by PHData are both on the same box.
I can't find the root cause of this. I even went so far as to create a console app to time how long it takes to open a connection to the SQL Server instance. The first run was about 1000 ms but subsequent runs were much quicker (presumably due to connection pooling in SQL server).
Are there any logs / tables I can look at to find out why this is happening?
I ran a report that gets its data from our other SQL Server box.
Opening a connection took about ~ 2055 ms all three times I ran the report. This seems to eliminate the DB engine instance as the problem. I'm wondering if something is timing out in SSRS after 2000 ms.
New information #2:
I setup a shared data source for test purposes and played around with it. This setting seems to be causing my slowness:
I did some testing and was able to get to 2 ms or less using either of the following settings. I can jump through the necessary hoops to start using one of these in production, but it would be really nice to just find the underlying cause.