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:-
  1. 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.
  2. Checked to see whether it could be related to temp database. On connecting with the database admin this was ruled out.
  3. 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
 

2 comments:

  1. Hi Lokesh.
    I had exactly the same issue.
    I had an existing SSRS(2012) report that had embedded sql (7 distinct queries) in it. The report rendered in ~2minutes, but if I ran the query in SSMS, it took under 2 seconds to return the same data.
    I wrote the queries into corresponding Stored Procs - the Procs return data in ~3 seconds.
    I swapped out the queries for the Stored Procs, one at a time. The report still renders in ~2minutes.
    I tried multiple online suggestions to speed up rendering. Nothing had an appreciable affect.
    In a fit of enthusiasm, I rebuilt the entire SSRS report, from scratch, using the Stored Procs instead of embedded SQL.
    Suddenly, that SSRS report renders in under 5 seconds.
    I can't explain it, but it seems that if you try and "adapt" an existing report, you bring along some sort of "overhead".
    Make sense?
    -D.

    ReplyDelete
    Replies
    1. Yes alex that makes sense. But u guess somwhere down the line it is to do with the inappropriate execution pla .... creating the report again would have done something that recompiled your query and hence a better performance.

      Delete

SSIS Issues : A Day of Learning

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...