August 29, 2016 at 8:08 am
i have a Parameter Sniffing issue due to the execution plan that gets created at runtime for a particular Stored procedure A , this SP uses Variables that are provided at the parent SP and also uses the same variables within the execution of several SP within the body of the SP A .
SQL creates an execution plan/Cache plan with the initial Variable values and the SQL engine tries to use the same execution plan for subsequent processes that requires the same variables this clogs the process.
The Option ( recompile) has been used but this does not take care of the issue.
What is the best and permanent way to take care of parameter sniffing?
I learn from the footprints of giants......
August 29, 2016 at 8:31 am
You say OPTION (RECOMPILE) doesn't help. Exactly what form of code/parameter sniffing is going on? Given that you are on SQL Server 2008 I wonder if it isn't the old IS NULL OR issue. If so, see Gail Shaw's blog post on catch-all queries:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Note that dynamic SQL is often a GREAT solution for parameter sniffing problems. Do be sure to guard against SQL Injection though!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 29, 2016 at 8:33 am
JALLYKAMOZE (8/29/2016)
What is the best and permanent way to take care of parameter sniffing?
Option(Recompile). If that doesn't work, it's probably not simply a bad parameter sniffing problem. Can you post the code of the procedures? An execution plan showing the problem would also be very useful.
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
August 29, 2016 at 10:04 am
You're saying parameters and variables. Let's get real clear, because it matters. Parameters are values defined with the CREATE statement for a stored procedure, as part of the preparation of a prepared statement, or within the defined parameter values of sp_executesql. Variables are defined within a procedure or prepared statement. They look the same, but their location of definition matters. If you are using variables, then they are not sniffed except in the case of a recompile event and, because the variable value is unknown the optimizer, use only averages for the statistics outside of the variable sniffing situation. It's possible that you're getting bad plans because you're using variables (if you are) and they aren't giving the optimizer accurate enough row estimates. So which is it, variables or parameters?
"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
August 29, 2016 at 12:19 pm
Grant Fritchey (8/29/2016)
You're saying parameters and variables. Let's get real clear, because it matters. Parameters are values defined with the CREATE statement for a stored procedure, as part of the preparation of a prepared statement, or within the defined parameter values of sp_executesql. Variables are defined within a procedure or prepared statement. They look the same, but their location of definition matters. If you are using variables, then they are not sniffed except in the case of a recompile event and, because the variable value is unknown the optimizer, use only averages for the statistics outside of the variable sniffing situation. It's possible that you're getting bad plans because you're using variables (if you are) and they aren't giving the optimizer accurate enough row estimates. So which is it, variables or parameters?
Thanks for your response, this is the scenerio, The parameters are used as part of the prepared statetment of the parent procedure, Now within the parent procedure i have a couple of other SPs that use the same values of the parameters for parent SP but this time they are used as variables in the subsequent Sps..
I learn from the footprints of giants......
August 30, 2016 at 4:51 am
JALLYKAMOZE (8/29/2016)
Grant Fritchey (8/29/2016)
You're saying parameters and variables. Let's get real clear, because it matters. Parameters are values defined with the CREATE statement for a stored procedure, as part of the preparation of a prepared statement, or within the defined parameter values of sp_executesql. Variables are defined within a procedure or prepared statement. They look the same, but their location of definition matters. If you are using variables, then they are not sniffed except in the case of a recompile event and, because the variable value is unknown the optimizer, use only averages for the statistics outside of the variable sniffing situation. It's possible that you're getting bad plans because you're using variables (if you are) and they aren't giving the optimizer accurate enough row estimates. So which is it, variables or parameters?Thanks for your response, this is the scenerio, The parameters are used as part of the prepared statetment of the parent procedure, Now within the parent procedure i have a couple of other SPs that use the same values of the parameters for parent SP but this time they are used as variables in the subsequent Sps..
So within the code you're doing this:
SET @Variable = @Parameter;
If so, then the variables will use average statistics rather than specific values to create the execution plans except in the case of a recompile when the actual value of the variable can be used. This means that if you don't use a recompile and you get slow performance, it's because average statistics are not generating a good enough plan. If, no the other hand, you're using recompile and you get slow performance, then you're hitting bad variable sniffing where the specific value is generating a plan that doesn't work well with the data. Frequently this second situation is in indication that your statistics at simply out of date.
"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
August 30, 2016 at 6:58 pm
JALLYKAMOZE (8/29/2016)
What is the best and permanent way to take care of parameter sniffing?
A clustered or covering index having the column used for the range selection as a first column in the index definition.
_____________
Code for TallyGenerator
August 31, 2016 at 1:53 am
Sergiy (8/30/2016)
JALLYKAMOZE (8/29/2016)
What is the best and permanent way to take care of parameter sniffing?A clustered or covering index having the column used for the range selection as a first column in the index definition.
How does that help out of date statistics or skewed data/statistics? It's not a permanent fix for those situations.
"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
August 31, 2016 at 3:17 am
Grant Fritchey (8/31/2016)
Sergiy (8/30/2016)
JALLYKAMOZE (8/29/2016)
What is the best and permanent way to take care of parameter sniffing?A clustered or covering index having the column used for the range selection as a first column in the index definition.
How does that help out of date statistics or skewed data/statistics? It's not a permanent fix for those situations.
Range selection, if it's along clustered index, will choose clustered index seek regardless of statistics.
Btw, skewed or perfectly maintained statistics won't make any difference, if the values of perameters are not "visible" to optimiser when the query is compiled. Recompilation will only help if parameters are explicitly included into a dynamic SQL query.
_____________
Code for TallyGenerator
August 31, 2016 at 9:35 pm
Sergiy (8/31/2016)
Grant Fritchey (8/31/2016)
Sergiy (8/30/2016)
JALLYKAMOZE (8/29/2016)
What is the best and permanent way to take care of parameter sniffing?A clustered or covering index having the column used for the range selection as a first column in the index definition.
How does that help out of date statistics or skewed data/statistics? It's not a permanent fix for those situations.
Range selection, if it's along clustered index, will choose clustered index seek regardless of statistics.
Btw, skewed or perfectly maintained statistics won't make any difference, if the values of perameters are not "visible" to optimiser when the query is compiled. Recompilation will only help if parameters are explicitly included into a dynamic SQL query.
I'd be shocked if your guaranteed behavior regardless of statistics, especially regardless of statistics.
Parameters are visible. It's why it's called parameter sniffing. Variables are not visible except during a recompile. Parameters come from stored procedure parameter definitions, or prepared statement parameter definitions. Variables are local variables, regardless of the type of query they're running within.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply