Parameter sniffing degrades stored procedure performance

Question

I'm using SQL Server 2012. Recently I'm have performance issue on all my stored procedures, the code inside the procedures work very fast though.

I found something about parameter sniffing so I used defining local variables technique as workaround for all my procedures.

I asked myself, why this is happening to me on all my procedures. My only guess is, that is because all my procedures are using a single OPTIONAL parameter.

This is the header of all my procedures

CREATE PROC [dbo].[MySampleProc] (@Key  Int = NULL) 
AS
....

Am I right? or you have any other idea?

Answer 1

Perhaps it's not parameter sniffing but out-of-date statistics. With a local variable (or the OPTIMIZE FOR UNKNOWN query hint), the row count estimate is based on average density. However, the actual stats histogram values are used when parameters are specified so the row count estimates will be off when stats are stale.

Try updating stats with FULLSCAN.