asked
Is execution time change due to Parameter Sniffing?
Hi Folks So the other day we faced an issue where one of the SSRS reports suddenly started running very slow. The report had input parameters and it was referring a stored proc at the back end. On executing the stored procedure at the database level there were no performance issues. I dwelled upon it for a while and then recompiled the stored proc. This resolved the issue and the performance of the SSRS report was back to the normal. Do you all agree it was because of parameter sniffing? Thanks
It could be, but it could also be several other things. Since a recompile fixed the issue, I would probably vote for a bad/out-of-date query plan. But now for the big question, what else was going on with that server? Was this running during a peak time? Was there any blocking going on? Was there paging on the disk? Was there parallelism? A yes to any of the above could also affect the performance of the entire server. But for now, I'm sticking with the possibility of the query plan.
Everything was same...we got it checked from the dba....this is how the situation unfolded in orderwise 1. Ran a report by selecting some input parameters 2. It ran quicly as usual 3. For stress test I passed all the available values of input parameters 4. Report ran for some time without showing any results. I aborted the proces 5. Ran it for the third time and it stayed like that for subsequent tries 6. Went to dba to ask about the server, tempdb and eveything seemed to be normal 7. Ran the sp with all the available values of ip parameters and got the results in seconds 8. Recompiled and run the report again, the day got saved What I am trying to understand is if it is parameter sniffing. why would it happen from ssrs's ends?
One more thing I just though of. Run this code against your SSRS database. Find the report in question. It will show you the time it took to retrieve the data as well as the amount of time it tool to render the report. (There's a lot more info there as well). If the amount of time to retrieve the data is high, you probably have run into some parameter sniffing. If the rendering time is high, then the problem is with the report. Also, ensure that the parameter type calling the SP is the same type as the SP is expecting.
Correct we actually did that and found timedataretreieval was higher way more than usual which kind of confirms that it was due to parameter sniffing. Thanks
It is difficult to say yes that the issue was caused by parameter sniffing. As stated above, there could be a number of items which can come into play, and not having the ability to see what was going on it is difficult. I have had SP's that run just as you described for any of the above to include parameter sniffing. Then the next there may or may not be an issue. Sometimes clearing the queryplan by forcing a recompile works and sometimes not. (Note - that is still not a reason to put WITH RECOMPILE in your code, as one of the DBA's here quickly found out.) I am going to give you a link telling a lot more and better about parameter sniffing, and you can use the info to talk to your DBA.
https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
yup I did go through that, its a wonderful link. Thanks for your help mate!
18 People are following this question.
