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?