[ACCEPTED]-Parameter Sniffing (or Spoofing) in SQL Server-parameter-spoofing

Accepted answer
Score: 55

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

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

usp_QueryMyDataByState 'Rhode Island'

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

usp_QueryMyDataByState 'Texas'

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

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

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

Score: 27

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

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

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

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

Score: 27

A simple way to speed that up is to reassign 2 the input parameters to local parameters 1 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 
    -- ...
Score: 5

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

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

You can work 11 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 10 it's better to not use stored procedures 9 at all but to send your queries directly 8 to the server. I recently came across the 7 same problem where I have no real solution 6 yet. For some queries the copy to local 5 vars helps getting back to the right execution 4 plan, for some queries performance degrades 3 with local vars.

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

Score: 5

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

Score: 0

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

More Related questions