0

We have an SSRS report that uses a stored procedure with several parameters. Until this morning, the report ran as usual.

So today I modify the stored procedure and run the SSRS report. After the changes, the query runs fast but the SSRS report takes several minutes to display the data.

I ended up declaring variables inside the SP instead of using the parameters (as suggested in this SO question) and that fixed the issue.

My question is: what changed that the SSRS report takes much longer to generate data? I understand parameter sniffing, but why would it change today? Parameter sniffing should be occurring all the time, not today after the the SP was modified.

fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110
  • 2
    Posting the code for your SP is going to considerably help here – Thom A Sep 17 '18 at 17:23
  • Parameter sniffing **does** happen all the time. It is that today it started to become a performance issue. – Sean Lange Sep 17 '18 at 18:17
  • @Larnu: The SP is 300 lines long, so I won't be able to post it. Besides, there's nothing wrong with it. – fdkgfosfskjdlsjdlkfsf Sep 17 '18 at 19:00
  • It most generally starts to become a performance issue when the data is skewed such that for one parameter a certain execution plan makes sense, but for another value a different execution plan would make sense. Gail Shaw has a great series on the topic here. https://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/ The behavior you are experiencing is very much in line with common challenges of parameter sniffing. – Sean Lange Sep 17 '18 at 19:08
  • Do you use OPTION RECOMPILE? – tbrookside Sep 17 '18 at 19:57

0 Answers0