When you undertake some performance improvement task, you think it is frustrating however at the same time you tend to learn more. So the other day we had to brain storm on one of the similar issues where the database stored procedure was returning data quickly as compared to the SSRS report (using same stored procedure) which seemed to be taking ages to run and display data.
The initial investigation and their results were:-
- Finding out from excutionlog table where the issue is. Turned out that 'TimeDataRetrieval' was considerably high for previous runs of the report. This proved that the real cause was indeed the slow performance of the stored procedure.
- Checked to see whether it could be related to temp database. On connecting with the database admin this was ruled out.
- Checked to see the cpu utilization at the report server. Nothing significant was found.
After doing all these steps, I thought that this issue might be related to the parameter sniffing. Parameter sniffing in short is when a particular query with input parameters takes more time to execute because it is using the execution plan of the any of the previous run of the same query with different parameter values. So we recompiled the stored procedure and this did the trick and got us back on track as far as performance of Report is concerned.
You can find a detailed informative article on PARAMETER SNIFFING here:-
https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
I would like to know your comments on the following questions :
1. What would confirm that the issue was only because of parameter sniffing?
2. If it was due to PS, why would we encounter the issue on running the report only and not by running the stored procedure?
Thanks
Lokesh
