0

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?

FLICKER
  • 6,439
  • 4
  • 45
  • 75
  • It is because the value of your parameter changes and the cached plan may not be the best for the new value. That is the nature of parameter sniffing. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/ – Sean Lange Sep 25 '15 at 21:12
  • Try RECOMPILE hint http://sqlmag.com/sql-server/using-recompile-query-hint-solve-parameter-sniffing-problems – Vojtěch Dohnal Sep 25 '15 at 21:14
  • @SeanLange the point is all my queries in the procedure will return almost the same count of records because of the nature of my data. I meant any value I pass to the procedure will result some thing between 200k or 250k records. So basically the plan should be the same for any parameter. – FLICKER Sep 25 '15 at 23:34
  • @VojtěchDohnal I used WITH RECOMPILE also used sp_recompile but no difference. BTW, I've already fixed the issue by using local variables and now I'm trying to understand why parameter sniffing is an issue for all my 15 procedures! – FLICKER Sep 25 '15 at 23:37
  • Could the issue be reproduced with an example? – Vojtěch Dohnal Sep 26 '15 at 11:48
  • @VojtěchDohnal, Sorry I can't publish the scripts because of confidentiality, but as I have mentioned in the question, all the procedures have only one parameter and many queries with the parameter in the where clause and group by. the procedures are using temp tables too. – FLICKER Sep 28 '15 at 20:39

1 Answers1

0

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71