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
 

Comments

  1. Alex11 May 2016 at 15:46

    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. Lokesh11 May 2016 at 18:58

      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

Post a Comment

Popular posts from this blog

Issues Integrating Azure Data Factory with GITHUB | IN spite of admin rights on repository

-
Image
Although I have been aware of GitHub for quite a while but got a chance to use it as a repository in the project for storing artifacts only over the past few months.  We use GitHub as the sole cloud repo for storing SQL, Databricks, and Azure Data Factory artifacts.  Got an opportunity to set up GitHub with ADF right from scratch and all the excitement to learn more about GitHub took a back seat when I stumbled upon the following issue Problem Statement - You are an admin on a repo in GitHub and even then get a permission error while linking the ADF to GitHub Solution - After scratching my hairs and going on a searching spree on google for some days, I was able to figure out what needed to be done, however was not aware of how to implement it. Finally thanks to Microsoft for getting on a call and helping me resolve this issue. What needed to be done - Need to authorize AzureDataFactory service so that Github can communicate with it.  How it should be done -  Basically, you need to auth
Read more

SQL QUERY NIGHTMARE

-
Image
Last week, we faced a basic and yet the only major problem that sql developers/admin had to deal with and yes your guess is right, It was with SQL QUERY RUNNING SLOW.  I will be explaining the problem below, however I want everyone reading this to help me find answers because I still haven't found one! One more amazing aspect of the issue was that it worked fairly quickly in Development but was running like a tortoise in Production even though the number of records were practically the same. I'll start by throwing some light on to the execution plan, so that we narrow down to the code which was the culprit here. The code is enclosed into a stored proc. On contacting my dba to enquire about the issue, I got the following replay:- I must hand it to our dba to provide such a detailed response.  So my job was now to look at the code and modify it. What I did was to shrink the calender table into a temp table consisting of few rows that would speed up the operation. Bel
Read more

Handling decimal and non numeric types using Case statement

-
Image
Hi Guys Just encountered a scenario where I needed to handle numeric and non numeric types using Case statement. Here is the synopsis of challenge and solution:- Challenge :- You have a varchar column in your input table having values such as 1.1111, 34, John. CREATE TABLE #TEST ([UpdatedValue] VARCHAR(100)) INSERT INTO  #TEST VALUES('1.1111'),('34'),('John') select * from #TEST Requirement :- You need to round off the decimal values to 2 places  Solution :- I got to know while implementing that for a case statement to work the output data i.e. the resultant data from each case should be of same type. It won't be able to treat numeric and non numerics together. That is why I personally found it a bit tricky but on brain storming solution looked to be very simple. The basic idea is to use different select queries and then union together. Here it is :- select CASE WHEN CHARINDEX('.',[UpdatedValue],0) >=1 THEN CAST( C
Read more