- 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.
Friday, 29 April 2016
Possible PARAMETER SNIFFING Scenario in SQL
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:-
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:-
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?
Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fello...
Sometimes the mistakes you make in the coding lead you to a better understanding of a particular concept. This happened recently with m...
I got a massive satisfaction last week when I was able to bring down the execution time of a sql stored procedure from > 3 hours to me...
Requirements : A valid azure account, Local Instance of SQL Server (>= 2012), SQL Server Data Tools, Excel 2007 or above Scenario : Du...