SQL Server 2016 - Parameter Sniffing Turned Off - Still seeing where a recompile improves performance We are on SQL Server 2016 and have turned Parameter Sniffing off, since at times it caused major performance issues. I am seeing that a few stored procedures run in 30 secs, When they are re-compiled they run in 2 secs. It seems to be an awful lot like the behavior we saw with Parameter Sniffing. 
Any ideas why this is still happening?

bill
According to this link, turning the Parameter Sniffing option off in 2016 is equivalent to setting trace flag 4136. 
You should read up on that trace flag.

Especially important information from that trace flag link:


  Enabling trace flag 4136...is equivalent to adding an OPTIMIZE FOR UNKNOWN hint


and


  Queries that meet any of the following conditions still undergo parameter sniffing even if the trace flag is set.
  
  
  The parameter is specified by using the "optimize for (@p = )" query hint.
  The query uses the "option(recompile)" query hint.
  The query is a part of a stored procedure that uses the "with recompile" option.
  


So by adding a recompile, you're telling the optimizer to build a plan based on whatever parameter is passed, but not to reuse the plan. With Parameter Sniffing off (and no recompile), a plan is built based off of statistics on the parameters and will be reused, even for values that would otherwise generate a different (perhaps better) plan.