0

This report has 7 datasets and 7 tablixes with a 1-1 correspondence. After clicking "view report" the loading icon comes up and spins indefinitely. The report never finishes or errors; I have let it run for over an hour.

Here’s what I’ve narrowed it down to:

• Of the 7 datasets in the report, two in particular are causing the problem. If I delete both and run the report it works. If I delete all content except for EITHER of those data sets, the report hangs.

• Both problem queries run quickly ad hoc.

• WITH(NOLOCK) is being used on all tables in all queries.

• I believe I have ruled out parameter sniffing:

  • OPTION(RECOMPILE) has no effect.

  • Hard coding parameter values in the query and running it on the report server had no effect.

• It does not appear to be an issue with report rendering/processing. If I delete the tables using these datasets the report still loads indefinitely. If I delete the datasets themselves the report runs quickly.

• In all cases I have been testing with the same set of parameters.

If you have any suggestions I would appreciate it.

Vertical Sunlight
  • 57
  • 8
  • How long do these queries take to run in SSMS (or equivalent)? – StevenWhite Sep 06 '17 at 18:39
  • Most of them take <5 seconds, including one of the problem queries. The other problem query takes about 40 seconds. – Vertical Sunlight Sep 06 '17 at 19:19
  • Have you tried a trace on the SQL server to check exactly what is being executed on the server? – Alan Schofield Sep 06 '17 at 21:03
  • No- and I had to look that up on MSDN- but apparently I do not have permissions to run sp_trace_create. – Vertical Sunlight Sep 06 '17 at 21:29
  • You should be able to run a trace from SSMS. It's under Tools then SQL Server profiler. – Alan Schofield Sep 06 '17 at 22:14
  • That looks a lot like parameter sniffing. While you think you have eliminated it, it would be worth trying the two techniques I use to get around this in these answers: [SQL text expression](https://stackoverflow.com/a/24006001/20977) and [using internal parameters](https://stackoverflow.com/a/16929881/20977) – Chris Latta Sep 06 '17 at 23:50
  • Are you on a development machine and running local? You know VS will cache your local data during development. – Ross Bush Sep 07 '17 at 15:05
  • @AlanSchofield thank you for that tip, I'll be sure to get more familiar with that tool. – Vertical Sunlight Sep 07 '17 at 17:50
  • @RossBush I appreciate your comment but no, this is after deploying to the report server. – Vertical Sunlight Sep 07 '17 at 17:51
  • @ChrisLätta Thanks for those additional tips about parameter sniffing. I tried the first and it ran quickly on the report server, I'm still skeptical so I'm making sure I didn't do the expression wrong haha. If it does turn out to be parameter sniffing what can I do? I'd rather not leave the query as an expression.. – Vertical Sunlight Sep 07 '17 at 18:00
  • OPTION(RECOMPILE) applies only to individual commands within your stored procedure. You may still have a bad cached plan overall. To turn it off for the store procedure entirely you can use WITH RECOMPILE and totally turn off plan caching and statistics. – Ross Bush Sep 07 '17 at 18:48
  • @VerticalSunlight It looks like you've established it is actually parameter sniffing. As to what you can do now - well, you can use a text expression to build your query and have it run fast or put up with a really slow query. The only way I've found to get around parameter sniffing is to rebuild the query from scratch in some way so Sql Server rebuilds the execution plan. – Chris Latta Sep 08 '17 at 00:36

0 Answers0