By poke

2014-01-30 20:27:14 8 Comments

While optimizing a report, I discovered that the time reported by TimeDataRetrieval in 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 ReportingServicesrService.exe.config:

<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?

New information:

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:

Use as Windows credentials when connecting to the data source

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.

Windows integrated security

Credentials stored securely in the report server


Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] SSRS 2017 reports having issues with IE 11?

  • 2018-07-03 15:03:52
  • BradC
  • 3201 View
  • 6 Score
  • 2 Answer
  • Tags:   ssrs ssrs-2017

2 Answered Questions

[SOLVED] SSRS 2012 Report with Oracle database as datasource never finishes

2 Answered Questions

[SOLVED] SSRS: First report is slow: Reloading Appdomain

1 Answered Questions

[SOLVED] Can mirroring be used to upgrade SSRS?

2 Answered Questions

[SOLVED] Slow SSRS Report in production

Sponsored Content