sql server - Parameter sniffing degrades stored procedure performance -

i'm using sql server 2012. i'm have performance issue on stored procedures, code inside procedures work fast though.

i found parameter sniffing used defining local variables technique workaround procedures.

i asked myself, why happening me on procedures. guess is, because procedures using single optional parameter.

this header of procedures

create proc [dbo].[mysampleproc] (@key  int = null)  .... 

am right? or have other idea?

perhaps it's not parameter sniffing out-of-date statistics. local variable (or optimize unknown query hint), row count estimate based on average density. however, actual stats histogram values used when parameters specified row count estimates off when stats stale.

try updating stats fullscan.