Parameter Sniffing (or Spoofing) in SQL Server

Question

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're running SQL Server 2005.]

A friendly local DBA (who no longer works here) took one look at the stored procedure and said "parameter spoofing!" (Edit: although it seems that it is possibly also known as 'parameter sniffing', 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

Answer 1

FYI - you need to be aware of something else when you're working with SQL 2005 and stored procs with parameters.

SQL Server will compile the stored proc's execution plan with the first parameter that's used. So if you run this:

usp_QueryMyDataByState 'Rhode Island'

The execution plan will work best with a small state's data. But if someone turns around and runs:

usp_QueryMyDataByState 'Texas'

The execution plan designed for Rhode-Island-sized data may not be as efficient with Texas-sized data. This can produce surprising results when the server is restarted, because the newly generated execution plan will be targeted at whatever parameter is used first - not necessarily the best one. The plan won't be recompiled until there's a big reason to do it, like if statistics are rebuilt.

This is where query plans come in, and SQL Server 2008 offers a lot of new features that help DBAs pin a particular query plan in place long-term no matter what parameters get called first.

My concern is that when you rebuilt your stored proc, you forced the execution plan to recompile. You called it with your favorite parameter, and then of course it was fast - but the problem may not have been the stored proc. It might have been that the stored proc was recompiled at some point with an unusual set of parameters and thus, an inefficient query plan. You might not have fixed anything, and you might face the same problem the next time the server restarts or the query plan gets recompiled.

Answer 2

Changing your store procedure to execute as a batch should increase the speed.

Batch file select i.e.:

exec ('select * from order where  order id ='''+ @ordersID')

Instead of the normal stored procedure select:

select * from order where  order id = @ordersID

Just pass in the parameter as nvarchar and you should get quicker results.