Post reply

got a parameter sniffing example/demo script?

  • October 27, 2010 at 8:49 am

    #388106

    there's thousands of references to parameter sniffing on this site, and they all refer to how to fix it, what with recompiling r declaring local parameters. I enjoy helping the folks that trip on this address their issue.

    Diagnosing and fixing I have down pat...my problem is having a setup script to demo and test.

    i don't have a sample script that shows with this data...and this proc...the proc takes too long becau`se of the parameter sniffing.

    Basically I want to look a the execution plans so i can understand exactly what's going on, instead of my intutive guess.

    I'd said previously that my theory is when parameter sniffing occurs, i think the saved execution plan is used to get the data on a row by row basis, so a MillionRowTable gets the plan applied a million times. no proof of that, and i want to fix my understanding.

    My theory is the equivilent of "when the procedure runs slow, the hamsters inside the SQL Engine run in a circle instead of on the treadmill. since they step on the treadmill once per circle, it's slower"

    could be true, could be not true, until you open it up and look inside.

    voodoo vs knowledge.

    does anyone have an example that anyone could use to see paameter sniffing in action?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 27, 2010 at 8:56 am

    #1242955

    Gail has a superb blog post on the topic:

    http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    -- Gianluca Sartori

  • October 27, 2010 at 9:11 am

    #1242971

    Lowell (10/27/2010)

    I'd said previously that my theory is when parameter sniffing occurs, i think the saved execution plan is used to get the data on a row by row basis, so a MillionRowTable gets the plan applied a million times. no proof of that, and i want to fix my understanding.

    Nope. A plan is executed once and only once.

    There are some plan operators that are good for small numbers of rows (nested loop join, key/rid lookup). There are other plan operators that are good for really large numbers of rows (hash join)

    If the optimiser generates a plan and, based on the parameter values and statistics, it estimates a small number of rows will be affected it will use the operators that are good on a small number of rows. That plan is then cached. Next execution there's a large, large number of rows. The operators that previously were efficient are now completely the opposite.

    To use an analogy...

    I put a bowl on the table with 3 coins in it and ask you to move the coins to a plate. You'd probably pick them up one by one, because that's easy for a small number of items.

    Now, I put a bowl with a cup of rice in it and ask you to move each grain of rice the same way you moved the coins. Not so easy.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 27, 2010 at 9:13 am

    #1242973

    Gianluca Sartori (10/27/2010)

    Gail has a superb blog post on the topic:

    http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    Saves me from looking that up. 😀 Need to fix the formatting on that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 28, 2010 at 3:00 am

    #1243399

    GilaMonster (10/27/2010)

    Lowell (10/27/2010)

    I'd said previously that my theory is when parameter sniffing occurs, i think the saved execution plan is used to get the data on a row by row basis, so a MillionRowTable gets the plan applied a million times. no proof of that, and i want to fix my understanding.

    Nope. A plan is executed once and only once.

    Not sure that's what he was getting at... if using RBAR on the million rows, a plan will be generated and executed for each row... even if it is identical.

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

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

  • October 28, 2010 at 3:19 am

    #1243406

    Jeff Moden (10/28/2010)

    Not sure that's what he was getting at... if using RBAR on the million rows, a plan will be generated and executed for each row... even if it is identical.

    Yes, but for each query, the plan is executed only once, not multiple times. Portions of the plan may be executed multiple times (as shown by the execution count property of each query operator), but the plan as a whole is run once.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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