February 8, 2012 at 4:34 am
I just wanted a little bit more information on parameter sniffing as we have a web application that utilises some search functionality, so parameters are passed via a stored procedure.
Now we have had speed issues, and we may have narrowed it down to the sproc but we are still looking at a number of possibilities 🙂
Anyway, according to some documentation it is an option to alter procedure parameters to be local variables, this would make query execution better. I read that if a procedure was to take in 'varying' parameters then this is a good option.
I dont understand what it means by varying parameters, is it purely by value, or by how many rows they have etc? I am aware of query plans and the underlying execution contexts, is there a limit to how many contexts a plan can have?
One point I suggested that by making the change, could that actually reduce performance as contexts in theory would no longer exist, is this right? I know the best way will be to analyse the plans in both scenarios but I just wondered if there was a general rule.
As always, any help would be massively appreciated.
'Only he who wanders finds new paths'
February 8, 2012 at 4:41 am
david.alcock (2/8/2012)
I just wanted a little bit more information on parameter sniffing as we have a web application that utilises some search functionality, so parameters are passed via a stored procedure.Now we have had speed issues, and we may have narrowed it down to the sproc but we are still looking at a number of possibilities 🙂
Anyway, according to some documentation it is an option to alter procedure parameters to be local variables, this would make query execution better. I read that if a procedure was to take in 'varying' parameters then this is a good option.
I dont understand what it means by varying parameters, is it purely by value, or by how many rows they have etc? I am aware of query plans and the underlying execution contexts, is there a limit to how many contexts a plan can have?
One point I suggested that by making the change, could that actually reduce performance as contexts in theory would no longer exist, is this right? I know the best way will be to analyse the plans in both scenarios but I just wondered if there was a general rule.
As always, any help would be massively appreciated.
Try Gail's blog posts on the subject: -
February 8, 2012 at 4:59 am
Excellent, i thought I read read every bit of matter on the subject and I have to say it was a great read.
I am certainly interested to see the trade off between using variables and parameters.
I am still a little unsure of how parameters are stored in the optimised plans as execution contexts. If there were say, 100 search values could SQL in theory hold a plan and context for each of these?
'Only he who wanders finds new paths'
February 8, 2012 at 6:01 am
Parameter values are not stored. If a procedure has 4 parameters, then a plan will be created that has spaces for 4 parameters. Sniffing will ensure that the plan stored is based on the specific values that were initially passed, but no other plans are created as the parameter values change.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2012 at 6:28 am
That is what I always understood, this is what has caused my confusion from MSDN:
"Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user. "
As it says they are reused thats why I wondered if parameters are actually held somewhere?
'Only he who wanders finds new paths'
February 8, 2012 at 6:49 am
When it says that the context is reused, it's just the structure, not the parameter values. As that quote says, it's reinitialised for the next user - wiped, cleared and repopulated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 8:46 am
I see, thank you ever so much everyone!
The example on MSDN had a number of contexts in a diagram which had me confused. I always thought parameters were not stored. Very interesting though, I very much enjoyed reading your mini series, always good having a real world example to play along with too.
It still seems a little like a catch 22, a possible fix for parameters is to use variables and a fix for variables is to use parameters!
'Only he who wanders finds new paths'
February 8, 2012 at 8:53 am
If you're looking for more, my chapter in the SQL Server MVP Deep Dives Volume 2[/url] book is all on parameter sniffing. (proceeds from the book go to charity in case anyone is wondering).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2012 at 10:13 am
Always looking for more, and I may well do!
Thanks again.
'Only he who wanders finds new paths'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply