Parameter Sniffing and Sniffing Memory

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

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

  • Very well written and thorough article!

  • loved reading it

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

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

  • 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?

  • 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]

  • 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

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

  • 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

  • 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

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

  • 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]

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

  • 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

  • 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