A while ago I had a query that I ran quite a lot for one of my users. It was still being evolved and tweaked but eventually it stablised and ran quite quickly, so we created a stored procedure from it. 

So far, so normal. 

The stored procedure, though, was dog slow. No material difference between the query and the proc, but the speed change was massive. 

[Background, we&#39;re running SQL Server 2005.]

 A friendly local DBA (who no longer works here) took one look at the stored procedure and said &quot;parameter spoofing!&quot; (**Edit:** although it seems that it is possibly also known as &#39;parameter sniffing&#39;, which might explain the paucity of Google hits when I tried to search it out.) 

We abstracted some of the stored procedure to a second one, wrapped the call to this new inner proc into the pre-existing outer one, called the outer one and, hey presto, it was as quick as the original query. 

So, what gives? Can someone explain parameter spoofing? 

Bonus credit for 

  - highlighting how to avoid it 
  - suggesting how to recognise possible cause
  - discuss alternative strategies, e.g. stats, indices, keys, for mitigating the situation




All Parameter Spoofing Solutions on Gang of Coders

Total of 1 Parameter Spoofing Solutions

Parameter Sniffing (or Spoofing) in SQL Server