February 27, 2012 at 7:07 am
Jeff Moden (2/27/2012)
One of the statements made was that dynamic SQL is a solution for parameter sniffing.
I certainly didn't make that claim.
I said that dynamic SQL is a solution to the optional parameters problem as it allows multiple execution plans for the various combinations of parameters, essentially preventing the problem where SQL reuses a plan created for one set of parameters for a completely different set of parameters.
Specifically:
GilaMonster (2/26/2012)
When there are multiple optional parameters across multiple columns, the dynamic SQL (when the existence of parameter values is used to build up the dynamic SQL) allows for the existence of multiple execution plans, each specific to the parameters that existed for the query.This reduces the chance of parameter sniffing problems (reduces, not eliminated) because the plan that was created for the case where both parameters were passed won't be used for the query where only one was passed, and it doesn't need any recompiles to do this
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 27, 2012 at 7:51 am
GilaMonster (2/27/2012)
HowardW (2/27/2012)
Presumably, the one exception to this is the OPTION(RECOMPILE) optimisation in 2k8 SP2 onwards in that it forces it not to parameterise?That's still parameterised (well, depends on whether the query you specify that on is a parameterised one or not), it still uses parameter sniffing (looks at the values of the parameters and variables to generate a plan). Because such a plan is never cached, it can't be reused and you can't have the problem where a cached plan is used inappropriately.
Option recompile actually allows more parameter sniffing because the optimiser can sniff variables as well as parameters.
My understanding from this and this that it's specifically discarding the parameterised template optimisations and evaluating all parameters in the query as constants?
In SQL Server 2008 RTM we introduced behaviour that allowed potentially better plans to be created when using the OPTION RECOMPILE syntax. The way this worked in principal was that if the OPTION RECOMPILE syntax was used, SQL could assume that the plan for the statement in question would not need to re-used, since it was always going to recompile. Therefore the query optimizer could safely use the actual parameter values passed to the statement when choosing a plan, as opposed to just using a parameterised template. In certain circumstances this can lead to dramatic performance gains, especially when you have parameter values which vary wildly and need different plans dependent upon the values passed.
And from Erlands blog:
This hint forces the query to be recompiled each time, in which case SQL Server will use the actual variable values as if they were constants.
Am I getting the wrong end of the stick? I thought this was a specific path in the optimiser that's only reached when OPTION(RECOMPILE) is specified.
February 27, 2012 at 8:07 am
OK, but for the purposes of 'parameter sniffing', constants and parameters are much the same. SQL can tell their value at compile time meaning it can generate a plan based on the estimated row counts for the exact value passed.
SELECT * FROM SomeTable WHERE SomeColumn = 42 option (maxdop 1) -- the hint is just to prevent this getting simple parametrisation applied.
and
DECLARE @SomeValue int = 42
SELECT * FROM SomeTable WHERE SomeColumn = @SomeValue OPTION(RECOMPILE)
and
DECLARE @sSQL nvarchar(200) = 'SELECT * FROM SomeTable WHERE SomeColumn = @SomeValue'
exec sp_executesql @sSQL, N'@SomeValue INT', @SomeValue = 42
In all 3 of those cases the optimiser can see the value passed to the query at compile time and can generate a plan optimal for that. Without option (recompile) on the second it would not be able to see the value of the variable at compile time.
What you see in the plan (XML plan) for those 3 are:
1) <ScalarOperator> <Const ConstValue="(42)" /> </ScalarOperator>
2) <ScalarOperator> <Const ConstValue="(42)" /> </ScalarOperator>
3) <ParameterList> <ColumnReference Column="@SomeValue" ParameterCompiledValue="(42)" ParameterRuntimeValue="(42)" /> </ParameterList>
Again, parameter sniffing is simply the ability of the optimiser to see the values of parameters and constants at compile time and generate a plan optimal for the estimated rows for that exact value. Nothing more.
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 27, 2012 at 10:53 am
Sorry, I guess what I meant was the problems associated with Parameter Sniffing rather than strictly Parameter Sniffing. Obviously in your 3rd example, if you changed the parameter value to another static value, this would likely use the plan from the original set of parameters, even if this was not optimal for values other than 42.
With OPTION(RECOMPILE), it deals with both optional parameters and the problems associated with parameter sniffing in that it will pick the best plan for both the parameters that are present and the parameter values on each execution.
I guess there are some extra plans that aren't safe for re-use that can only be used with RECOMPILE, but most of these will revolve around conditional logic that the dynamic SQL approach intentionally avoids.
February 27, 2012 at 11:39 am
Well parameter sniffing problems require that the plan is cached and reused, and with option recompile the plan is never cached and hence can't be reused.
Note that using option recompile for optimising queries with optional parameters requires SQL 2008 SP2 or above. Below that either the optimiser came up with the same poor plan or there was an incorrect results bug that you could run into
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
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply