1

We are moving from SQL Server 2014 to 2017, I see in the options that we have Parameter Sniffing, I believe we should we keep it OFF, but what about the options that we used in 2014 to avoid that? can be affected in 2017, like creating local variables of the parameters and put option(recompile) to the queries.

2 Answers 2

0

All the ways of influencing parameter sniffing before SQL 2017 remain. There's just a new database scoped configuration option to turn off parameter sniffing entirely.

3
  • Thank you David!
    – user198450
    Commented Jan 16, 2020 at 14:37
  • I was reading and it says: PARAMETER_SNIFFING = { ON | OFF | PRIMARY} Enables or disables parameter sniffing. The default is ON. Setting PARAMETER_SNIFFING to OFF is equivalent to enabling Trace Flag 4136. The flag 4136 mention this: Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR <value> is used. I have many places using OPTION(RECOMPILE), keeping PARAMETER_SNIFFING ON and the hint should not affect the performance?
    – user198450
    Commented Jan 16, 2020 at 14:44
  • Correct. PARAMETER_SNIFFING ON is matches the behavior of previous versions of SQL Server. Commented Jan 16, 2020 at 14:47
0

... and just to be clear. Turning off parameter sniffing gives the same result as if you were to do something like (example assumes a stored proc with a variable named a_parm):

DECLARE @a_var int
SET @a_parm = @a_var
SELECT ... FROM tbl WHERE col = @a_var

Or

SELECT ... FROM tbl WHERE col = @a_parm OPTION(OPTIMIZE FOR UNKNOWN)

I.e., SQL Server has no idea what is in the variable and uses either density for the statistics (if exists, for a equal condition) or hard-wired values (for instance greater then assumes selectivity of 25%, or whatever those values might be).

Turning off parameter sniffing is not the same as OPTION(REOMPILE). Which is what I wanted to clarify. :-)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.