Parameter Sniffing and Sniffing Memory

  • SQLSACT

    SSC-Insane

    Points: 21559

    Comments posted to this topic are about the item Parameter Sniffing and Sniffing Memory

  • Stan_Segers

    SSC-Addicted

    Points: 453

    Setting the 'min memory per query (KB)' to a higher value could also be considered (after thorough research) as an option to prevent spilling to tempdb.

  • CGSJohnson

    SSCertifiable

    Points: 6954

    Very well written and thorough article!

  • psingla

    Hall of Fame

    Points: 3840

    loved reading it

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • ManicStar

    SSCoach

    Points: 17992

    What about using local variables to hold the value of the parameters at the start of the stored procedure? This always seems to work for me when i encounter this situation. Or have i simply been lucky that this seemed to have worked?

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Thank you for this article.

    I knew about parameter sniffing, but not about memory sniffing and the tempdb aspect of it.

    Good to know...

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • dwilliscp

    SSCrazy Eights

    Points: 9245

    Good Article, but how do we tell what query is creating the spill over? I have been trying to figure out why a section of my job keeps taking over 2 hours to run... using your sniffing it does give both hash and sort warnings, but the plan has several of these objects.

    BTW I am doing a insert into..where does it show the memory?

    David Potter

  • SQLSACT

    SSC-Insane

    Points: 21559

    dwilliscp (7/15/2013)


    Good Article, but how do we tell what query is creating the spill over? I have been trying to figure out why a section of my job keeps taking over 2 hours to run... using your sniffing it does give both hash and sort warnings, but the plan has several of these objects.

    David Potter

    Look for queries/procedures containing sort/hash operations.

    If you have the execution plan, isolate the sort/hash operation and tune the query.

    An index will remove the need for a sort operation.

    If you are seeing hash operations, look at the columns being joined in the Hash join, look for missing indexes.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    I think the author forgot a much better, more targeted option: RECOMPILE on the statement(s) INSIDE the sproc that are being screwed by parameter sniffing/plan caching. That is almost always a better option than RECOMPILE on the entire sproc, and has been available since SQL 2005 if my memory serves.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • dwilliscp

    SSCrazy Eights

    Points: 9245

    SQLSACT (7/15/2013)


    dwilliscp (7/15/2013)


    Good Article, but how do we tell what query is creating the spill over? I have been trying to figure out why a section of my job keeps taking over 2 hours to run... using your sniffing it does give both hash and sort warnings, but the plan has several of these objects.

    David Potter

    Look for queries/procedures containing sort/hash operations.

    If you have the execution plan, isolate the sort/hash operation and tune the query.

    An index will remove the need for a sort operation.

    If you are seeing hash operations, look at the columns being joined in the Hash join, look for missing indexes.

    Thanks for the help.. I do have both the Stored Proc and the XML Plan.. I have run the sections of the Stored Proc, so I know what part is creating the problem... and it is the query that pulls everything gained from the other servers together. It has several sort operations and a three table join. I will take a look at the join again.. Profiler did not show any mising indexes, but maybe I can see one that it missed. I will also take a second look at the joins.. the data is comming from several servers... and the views SQL server has about job's and their execution. (I realy wish MS would have stored the data better)

    David

  • SQLSACT

    SSC-Insane

    Points: 21559

    TheSQLGuru (7/15/2013)


    I think the author forgot a much better, more targeted option: RECOMPILE on the statement(s) INSIDE the sproc that are being screwed by parameter sniffing/plan caching. That is almost always a better option than RECOMPILE on the entire sproc, and has been available since SQL 2005 if my memory serves.

    Thanks

    Didn't forget, wasn't aware that placing a recompile hint of the statement inside the proc would have any effect on the caching, seeing that the statement is within a proc.

  • vick.ram79

    Default port

    Points: 1415

    Use local variables to hold the values of the passed parameters. And then use the local variables instead of the passed parameters. This has ALWAYS worked for us in our really complex Stored Procedures. At the same time - make sure that even the functions that are called from the stored proc have local variables.

    Trust me - this has saved our *** more than a few times.

    S

    How To Post[/url]

  • Roddy.CAMERON

    SSCommitted

    Points: 1549

    vick.ram79 (7/15/2013)


    Use local variables to hold the values of the passed parameters. And then use the local variables instead of the passed parameters. This has ALWAYS worked for us in our really complex Stored Procedures. At the same time - make sure that even the functions that are called from the stored proc have local variables.

    Trust me - this has saved our *** more than a few times.

    S

    I'm afraid you're just lucky then. This is not good advice. The optimiser does not know the value of local variables when it creates the query plan. It does not execute any code and hence local variables are not actually set when optimisation occurs. If you use local variable and want them considered by the optimiser, you need to use "option(recompile)" at the statement level as Kevin pointed out. That way the variable is actually set when the optimiser then creates a plan for that statement as the statements which set them have already actually executed.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    vick.ram79 (7/15/2013)


    Use local variables to hold the values of the passed parameters. And then use the local variables instead of the passed parameters. This has ALWAYS worked for us in our really complex Stored Procedures. At the same time - make sure that even the functions that are called from the stored proc have local variables.

    Trust me - this has saved our *** more than a few times.

    S

    It is indeed bad advice. Even worse is your mention of "functions that are called from the stored proc", implying UDF usage, which can be DEVASTATINGLY BAD!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • ManicStar

    SSCoach

    Points: 17992

    vick.ram79 (7/15/2013)


    Use local variables to hold the values of the passed parameters. And then use the local variables instead of the passed parameters. This has ALWAYS worked for us in our really complex Stored Procedures. At the same time - make sure that even the functions that are called from the stored proc have local variables.

    Trust me - this has saved our *** more than a few times.

    S

    If you're just trying to get the optimizer to create a new query plan, then the local variable thing does seem to work. I've encountered this problem mostly with poorly written stored procedures that have too many variables that are 'optional'. Without having the option to redesign the things, i've usually resorted to making them recompile every time they run, or trying the local variable trick to replace the parameters coming into the things.

Viewing 15 posts - 1 through 15 (of 26 total)

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