Index seek used in query but index scan in stored procedure

Question

I'm experiencing some strange behaviour in SQL Server 2008 R2.

I have the following query:

UPDATE  TableToUpdate
SET     ColumnToUpdate = @ColumnValue
WHERE   ColumnA IN
        (
            SELECT  ColumnA
            FROM    SubQ1Table
            WHERE   ColumnToUpdateReference = @ColumnValue
        )
and     ColumnB in
        (
            SELECT  ColumnB
            FROM    SubQ2Table
            WHERE   ColumnToUpdateReference = @ColumnValue
        )

When I run the query manually and declare the variable @ColumnValue as follows:

DECLARE @ColumnValue INT = 123;

It runs in seconds and does an Index Seek on the index covering columns ColumnA, ColumnB and ColumnToUpdate on the TableToUpdate table.

When I create a stored procedure using the exact query except the parameter is passed in.. eg:

EXEC sp_Query 123

An Index Scan is used on the same index and takes around 30 seconds to complete.

I've looked at both query plans which are different. Reading from left to right the first difference is the stored procedure seems to do a Nested Loops (Inner Join) Whereas the direct query does a Stream Aggregate (Aggregate).

Why would calling this SQL through a stored-procedure make a difference? If you need me to provide any more information let me know.

Thanks in advance,

Tom.

Sounds like [parameter sniffing](http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options) to me. — GarethD, Oct 13 '14 at 12:33

score 2 · Answer 1 · answered Oct 13 '14 at 13:04

2

Create your SP with RECOMPILE OPTION and then check Execution Plan for the same. Great article on the same as below:

http://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/

answered Oct 13 '14 at 13:04

knkarthick24

3,106
15
21

score 2 · Accepted Answer · edited May 23 '17 at 12:22

Like GarethD suggested, it does sound like it may be parameter sniffing, in case you don't know what that is, let me (try to) explain.

SQL Server compiles stored procedures by "sniffing" the parameters sent the first time the procedure is executed and the plan for this execution is put into the plan cache for future reference.

Every subsequent time the procedure executed, SQL Server retrieves the execution plan from the cache and uses it (unless there is a reason for recompilation).

A problem can arise if the first time the stored procedure is executed a set of parameters is used that generates an acceptable plan for that set of parameters but very bad for other more common sets of parameters.

There are a few ways around this that I can think of:

There are some workarounds to overcome this problem.

OPTION (RECOMPILE)
OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
Use local variables

It depends on your setup which will be best for you, but have a read around.

SQL Server - parameter sniffing

Parameter Sniffing (or Spoofing) in SQL Server

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Also on a side note watch out for the use of 'sp_' when naming stored procedures - it's a big no no. Check out this article, it details a thorough examination of using sp_.

Thanks this is what was causing the query plan to be different! — Tom Miller, Oct 13 '14 at 15:25

score 2 · Answer 3 · answered Oct 13 '14 at 13:13

2

Can you check the data type parameter in the stored procedure?

if it's not of int type then some implicit conversation might be happening, which is resulting in index scan.

answered Oct 13 '14 at 13:13

Satyajit

2,150
2
15
28

I did check this and it was an int. Thanks – Tom Miller Oct 13 '14 at 15:24

Index seek used in query but index scan in stored procedure

3 Answers3