September 30, 2013 at 9:11 am
Sometimes I find the reason behind a poorly performing stored procedure lies in passing the external parameters to internal variables and then using those internal variables instead of referencing the external parameters.
Can anyone explain why this is happening, or point me to some microsoft technet article on the subject? I need to explain this to the rest of my team, but I don't know exactly what's going on. I just know of the problem and solution. hah!
-------------------------------------------------------------------------------------------------
My SQL Server Blog
September 30, 2013 at 9:18 am
amenjonathan (9/30/2013)
Sometimes I find the reason behind a poorly performing stored procedure lies in passing the external parameters to internal variables and then using those internal variables instead of referencing the external parameters.Can anyone explain why this is happening, or point me to some microsoft technet article on the subject? I need to explain this to the rest of my team, but I don't know exactly what's going on. I just know of the problem and solution. hah!
What you are describing sounds like the polar opposite of parameter sniffing. One way to help with parameter sniffing is to create a local variable and set it to the value of the parameter. This is generally done to help increase performance but you are saying the exact opposite thing is happening.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2013 at 9:23 am
Whatever your situation is, perhaps it is related to execution plan recompiling. Execution plan reuse matters in an OLTP database where the same procedure is called 1000s of times per hour, with only the input parameters changing.
http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
In a data warehousing environment, where you are running aggregate queries against large tables that are bulk loaded, plan compile time is not relevent, and it's actually beneficial to get a fresh recompile for each query, so it is adjusted to take advantage of latest data volume statistics.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 30, 2013 at 9:30 am
I think I worded my post wrong, because it is exactly parameter sniffing. Once I passed the externals to internals the sproc ran very fast. Thanks!
-------------------------------------------------------------------------------------------------
My SQL Server Blog
September 30, 2013 at 10:40 am
amenjonathan (9/30/2013)
I think I worded my post wrong, because it is exactly parameter sniffing. Once I passed the externals to internals the sproc ran very fast. Thanks!
Cool glad we were able to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply