Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameter Sniffing and Sniffing Memory


Parameter Sniffing and Sniffing Memory

Author
Message
SQLSACT
SQLSACT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 2931
Comments posted to this topic are about the item Parameter Sniffing and Sniffing Memory
Stan_Segers
Stan_Segers
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 501
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
CGSJohnson
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1966 Visits: 1686
Very well written and thorough article!
psingla
psingla
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 1249
loved reading it

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Code Monkey, the SQL
Code Monkey, the SQL
SSC Eights!
SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)

Group: General Forum Members
Points: 915 Visits: 3096
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
Marios Philippopoulos
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2584 Visits: 3748
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
dwilliscp
dwilliscp
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 767
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
SQLSACT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 2931
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
TheSQLGuru
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7017 Visits: 8395
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 at GMail
dwilliscp
dwilliscp
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 767
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search