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

Rosiarosicrucian

It isn't just a possibility, it's a certainty--there's no such thing as parameter spoofing. It's parameter sniffing. – Usquebaugh

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.

Chance

Pick up Grant Fritchey's excellent book SQL Server 2008 Query Performance Distilled where he goes through all your options. Even though it says 2008, it's great for 2005 too. – Chance

I recommend looking a two possible solutions, the first I've tried and seen work, the second I suspect would work but have not tried in practice. – Hutchens

First, you can declare local variables for each parameter, copy the parameters into these variables, and use these variables in your query rather than using the parameters directly. I don't know why this works and it smells but it resolves the issue (using Sql Server 2008). Second, look at the OPTIMIZE FOR UNKNOWN query hint. You can read about it and other hints on MSDN: msdn.microsoft.com/en-us/library/ms181714.aspx – Hutchens

Here is a link so others can see the book to which @brent-ozar was referring - now for 2014 smile.amazon.com/SQL-Server-Query-Performance-Tuning/dp/…*Version*=1&*entries*=0 – Andorra

Answer 2

A simple way to speed that up is to reassign the input parameters to local parameters in the very beginning of the sproc, e.g.

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

    --Work w/ @SniffAvoidingLocalParameter in sproc body 
    -- ...

Excrescence

This solution cut the execution time of my query of 50%. Not perfect but better! – Botello

But why does that help anyway? – Defenestration

@TimBüthe: It works by preventing SQL Server from caching (or using a cached version of) a query plan for your procedure. Depending on your parameter's value, one query execution plan may be faster or slower than another. The first time your procedure runs it will build a plan based on that first parameter's value. Reassigning it locally will make SQL Server use a new plan each time. – Monroe

@Cory I don't believe what you've stated is accurate. It prevents the sniffing but it doesn't prevent a plan from being cached. It just causes a plan that's generic (IE not for a specific parameter value) to be cached instead. – Prent

Answer 3

Yes, I think you mean parameter sniffing, which is a technique the SQL Server optimizer uses to try to figure out parameter values/ranges so it can choose the best execution plan for your query. In some instances SQL Server does a poor job at parameter sniffing & doesn't pick the best execution plan for the query.

I believe this blog article http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx has a good explanation.

It seems that the DBA in your example chose option #4 to move the query to another sproc to a separate procedural context.

You could have also used the with recompile on the original sproc or used the optimize for option on the parameter.

Palestine

+1, but note that the "with recompile" could have performance issues of itself. I tend to try option #4 first... – Cervantez

My understanding of joins is the type of join(merge/hash/loop) is chosen based on two main factors, 1) indexes on the joined columns 2) statistics that predict the size of the join. So each time you run the query, based on the estimated size of the join it chooses the appropriate join. Is the join selection baked into a compiled query? I.e. if parameter sniffing poorly predicts the typical size of joins, will it bake into the query plan the poorly chosen type of join? – Pepys

Answer 4

Parameter sniffing is a technique SQL Server uses to optimize the query execution plan for a stored procedure. When you first call the stored procedure, SQL Server looks at the given parameter values of your call and decides which indices to use based on the parameter values.

So when the first call contains not very typical parameters, SQL Server might select and store a sub-optimal execution plan in regard to the following calls of the stored procedure.

You can work around this by either

• using WITH RECOMPILE
• copying the parameter values to local variables inside the stored procedure and using the locals in your queries.

I even heard that it's better to not use stored procedures at all but to send your queries directly to the server. I recently came across the same problem where I have no real solution yet. For some queries the copy to local vars helps getting back to the right execution plan, for some queries performance degrades with local vars.

I still have to do more research on how SQL Server caches and reuses (sub-optimal) execution plans.

Scylla

Answer 5

In my experience, the best solution for parameter sniffing is 'Dynamic SQL'. Two important things to note is that 1. you should use parameters in your dynamic sql query 2. you should use sp_executesql (and not sp_execute), which saves the execution plan for each parameter values

Cowberry

In a world of SQL Injection attacks, I don't think recommending dynamic SQL is a good idea. – Lebkuchen

He also recommended using parameters, which is how you can avoid SQL injection. – Sitting

I don't think that is the case when we use dynamic sql inside a proc. Parameter sniffing cannot be solved when we use dynamic sql and sp_executesql. sp_executesql with cache the first execution plan. If we use "exec" then it generates different plan for different parameters. – Frightful

Answer 6

I had similar problem. My stored procedure's execution plan took 30-40 seconds. I tried using the SP Statements in query window and it took few ms to execute the same. Then I worked out declaring local variables within stored procedure and transferring the values of parameters to local variables. This made the SP execution very fast and now the same SP executes within few milliseconds instead of 30-40 seconds.

Epiphytotic

Answer 7

Very simple and sort, Query optimizer use old query plan for frequently running queries. but actually the size of data is also increasing so at that time new optimized plan is require and still query optimizer using old plan of query. This is called Parameter Sniffing. I have also created detailed post on this. Please visit this url: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

Freddie

Answer 8

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.

Adiaphorism