Parameter Sniffing In Action

  • Nisarg Upadhyay

    SSC Veteran

    Points: 220

    Comments posted to this topic are about the item Parameter Sniffing In Action

  • Jeff Moden

    SSC Guru

    Points: 997205

    I like short "spackle" articles like this.  Good job. 

    My only suggestion is that people like to run the code you've included in your article.  Instead of using some canned database that a lot of people won't download, provide some code to generate the test data.  It's pretty easy to quickly build a million row test table nowadays.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Nisarg Upadhyay

    SSC Veteran

    Points: 220

    Jeff Moden - Monday, October 23, 2017 10:01 PM

    I like short "spackle" articles like this.  Good job. 

    My only suggestion is that people like to run the code you've included in your article.  Instead of using some canned database that a lot of people won't download, provide some code to generate the test data.  It's pretty easy to quickly build a million row test table nowadays.

    Thanks for feedback Jeff. I do agree with your suggestion.

  • steve.powell1

    SSC Enthusiast

    Points: 104

    The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.

    we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.

  • carjara

    SSC Enthusiast

    Points: 168

    Hi, how can I prevent this behavior?
    Is it "recompile" option the right way?

    Thank you in advance.

  • Alan Burstein

    SSC Guru

    Points: 61088

    carjara - Tuesday, October 24, 2017 8:48 AM

    Hi, how can I prevent this behavior?
    Is it "recompile" option the right way?

    Thank you in advance.

    It depends. 

    First, and as Nisarg mentioned at the end of the article, parameter sniffing is not always a bad thing. In fact, sometimes lack of parameter sniffing is a bad thing. Parameter sniffing is bad when the optimizer is choosing a terrible execution plan due to it. 

    I have found it's a good thing to always recompile store procedures that do ETL work and run on a schedule. I would not do it, however, on say - an proc that runs hundreds/thousands of times an hour. 

    All that said, I've always learned a lot about this topic from Grant Fritchey and Gail Shaw. Here's a couple good articles 

    https://www.scarydba.com/2016/12/12/optimize-hints-parameter-sniffing-turned-off
    https://www.sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    This article (with perhaps the most uninspiring title ever) is also good: https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • carjara

    SSC Enthusiast

    Points: 168

    Alan.B - Tuesday, October 24, 2017 11:02 AM

    carjara - Tuesday, October 24, 2017 8:48 AM

    Hi, how can I prevent this behavior?
    Is it "recompile" option the right way?

    Thank you in advance.

    It depends. 

    First, and as Nisarg mentioned at the end of the article, parameter sniffing is not always a bad thing. In fact, sometimes lack of parameter sniffing is a bad thing. Parameter sniffing is bad when the optimizer is choosing a terrible execution plan due to it. 

    I have found it's a good thing to always recompile store procedures that do ETL work and run on a schedule. I would not do it, however, on say - an proc that runs hundreds/thousands of times an hour. 

    All that said, I've always learned a lot about this topic from Grant Fritchey and Gail Shaw. Here's a couple good articles 

    https://www.scarydba.com/2016/12/12/optimize-hints-parameter-sniffing-turned-off
    https://www.sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    This article (with perhaps the most uninspiring title ever) is also good: https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/

    Alan, thank you a lot!

  • Grey Cat

    Mr or Mrs. 500

    Points: 514

    "with recompile" is one way to avoid parameter sniffing.
    I think another way is using dynamic queries in SPs. This will cause extra plans in plan cache. So make sure "optimize for Ad hoc Workloads" are turned on.

  • Jeff Moden

    SSC Guru

    Points: 997205

    Grey Cat - Wednesday, October 25, 2017 5:29 PM

    "with recompile" is one way to avoid parameter sniffing.
    I think another way is using dynamic queries in SPs. This will cause extra plans in plan cache. So make sure "optimize for Ad hoc Workloads" are turned on.

    For large batch jobs, RECOMPILEs aren't so bad.  Using "optimize for Ad hoc Workloads" actually masks the terrible problem of multiple renditions of the same code being just far enough off to require a separate and sometimes very expensive recompile of code coming from poorly formed embedded SQL, SQL generated from an ORM, and dynamic SQL.  It's a real bitch when front end code (regardless of method) "only" takes 100ms to run but takes 2-22 seconds to execute and it's execute thousands of times per hour.  Most folks never find that type of problem and just chalk it up to the "server being slow" when it's actually the fault of the code and the recompiles that are taking place.

    To wit, "with recompile" should be used very, very sparingly and with great caution and understanding.  It is NOT a panacea to avoid parameter sniffing and should only be used with the greatest of care.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Grey Cat

    Mr or Mrs. 500

    Points: 514

    steve.powell1 - Tuesday, October 24, 2017 2:02 AM

    The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.

    we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.

    We are talking about extreme situations. First, parameter sniffing is good in most cases (that's why it is implemented). But this makes things worse in some cases. This is why we need to find a way to avoid sniffing. Any way, Kimberly Tripp has very good sessions, articles on this topic.

  • Jeff Moden

    SSC Guru

    Points: 997205

    Grey Cat - Thursday, October 26, 2017 4:03 PM

    steve.powell1 - Tuesday, October 24, 2017 2:02 AM

    The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.

    we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.

    We are talking about extreme situations. First, parameter sniffing is good in most cases (that's why it is implemented). But this makes things worse in some cases. This is why we need to find a way to avoid sniffing. Any way, Kimberly Tripp has very good sessions, articles on this topic.

    Got any links to share?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jason A. Long

    SSC-Insane

    Points: 23711

    Jeff Moden - Thursday, October 26, 2017 9:51 PM

    Grey Cat - Thursday, October 26, 2017 4:03 PM

    steve.powell1 - Tuesday, October 24, 2017 2:02 AM

    The problem with Parameter Sniffing occurs where the execution plan is optimized for small data as a result (here SQL can be quite lazy) and you mostly want big data, where a good execution plan is better.

    we had an instance of this a few months ago - execution plans were refreshed and the first parameter passed returned a few rows only when normally there are quite a few more rows expected. End result: The SP ran like a three legged dog dragging a Challenger tank when normally it ran like a greyhound. Took a while to track down why and to fix the problem so we wouldn't have this again. However, the recommendation is to let SQL sort out the Execution plan and that includes Parameter Sniffing - only 'fix' the sniffing if it is actually causing a problem. And to know that you need to do regular health checks.

    We are talking about extreme situations. First, parameter sniffing is good in most cases (that's why it is implemented). But this makes things worse in some cases. This is why we need to find a way to avoid sniffing. Any way, Kimberly Tripp has very good sessions, articles on this topic.

    Got any links to share?

    Building High Performance Stored Procedures

    Stored Procedure Optimization with Kimberly Tripp - Alaska SQL User Group

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply