3

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

  • Hmm, I've had a similar issue in the past. I would check the firewall on the SQL Server box and make sure that port 1433 is open. Just a thought – Nabil Becker Feb 1 '14 at 0:09
  • Could it be DNS lookups of the server name? Does replacing the server names in connection strings with IP Addresses help? – simon at rcl Feb 4 '14 at 10:56
  • @simonatrcl: I tried your suggestion but it didn't help. While experimenting with your suggestion, though, I discovered something else. I've updated my question with the new information. – poke Feb 4 '14 at 17:06
  • If using Windows Integrated Security is the one with 0s, then that suggests it's using a supplied username/password. This has to be validated against an AD server, and a security token issued. With Integrated Security, it supplies your security token whose validation may well be faster (NOT an AD expert). The other thing to try is to set up a SQL logon and use that: AD would not be involved, and that may be more convenient. – simon at rcl Feb 4 '14 at 17:47
  • @simonatrcl: My update wasn't quite clear. Windows Integrated Security is fast (2 ms or less). I also tested using a SQL logon, which was also fast. Based on your comment, I'm now thinking that the 2000+ delay is coming from when SSRS tries to get a security token for the stored account. – poke Feb 4 '14 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.