July 15, 2013 at 12:02 am
Comments posted to this topic are about the item Parameter Sniffing and Sniffing Memory
July 15, 2013 at 5:04 am
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.
July 15, 2013 at 5:19 am
Very well written and thorough article!
July 15, 2013 at 7:10 am
loved reading it
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
July 15, 2013 at 8:17 am
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?
July 15, 2013 at 9:11 am
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]
July 15, 2013 at 11:14 am
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
July 15, 2013 at 11:22 am
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.
July 15, 2013 at 12:03 pm
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
July 15, 2013 at 12:57 pm
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
July 15, 2013 at 1:47 pm
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.
July 15, 2013 at 2:24 pm
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
July 15, 2013 at 4:47 pm
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.
July 15, 2013 at 9:21 pm
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
July 16, 2013 at 7:08 am
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 25 total)
You must be logged in to reply to this topic. Login to reply