CODEWITHSUNDEEP
sqlindexing
user1607162
user1607162

Reputation: 11

SQL Server 2005 - Same query, same server, two different execution plans

I've been fighting with an ongoing issue on our server for a few weeks now. There is a simple query that gets run in several different stored procedures, but in a few of them, the query can take nearly 4 minutes to run. Some procedures have this exact same query and they run in < 1 second. Running it from a query window returns in < 1 second.

I cannot recreate this - I can't run the procedure the user is running (because it changes data), and the problem does not arise on our test server. I can sometimes recompile (F5) the procedure, which seems to recreate the execution plan, and fix it for a while, but it always returns.

The query is as simple as:

SELECT '1' 
FROM TRANSACTION_LOG WITH (NOLOCK) 
WHERE TYPE = 15 AND SERIAL_NO = @P_TRAVELER_SERIAL

After some research and profiler capturing, I found something odd in the execution plan. I managed to capture the execution plan while it was running quickly and another when it was running slowly. These are from the exact same procedure, exact same query - the only difference would be the value of the @P_TRAVELER_SERIAL parameter:

FAST:

Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))          
  |--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
    |--Constant Scan
    |--Filter(WHERE:([MICS].[dbo].[Transaction_Log].[TYPE]=(15)))
      |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1010]) WITH UNORDERED PREFETCH)
        |--Index Seek(OBJECT:([MICS].[dbo].[Transaction_Log].[IX_SERIAL_NO]), SEEK:([MICS].[dbo].[Transaction_Log].[SERIAL_NO]=[@P_TRAVELER_SERIAL]) ORDERED FORWARD)
        |--RID Lookup(OBJECT:([MICS].[dbo].[Transaction_Log]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

SLOW:

Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
  |--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
    |--Constant Scan 
    |--Table Scan(OBJECT:([MICS].[dbo].[Transaction_Log]), WHERE:([MICS].[dbo].[Transaction_Log].[TYPE]=(15) AND [MICS].[dbo].[Transaction_Log].[SERIAL_NO]=[@P_TRAVELER_SERIAL]))

Why would it use an index seek with one plan and a table scan with the other? It's the same query and the same procedure? Would table activity have anything to do with it? It's a busy table...

Thanks

Upvotes: 1

Views: 1243

Answers (1)

Alan
Alan

Reputation: 11

This sounds like a parameter sniffing issue to me. What happens is that over time SQL Server determines that the execution plan for your procedure is out of date, and the next time the procedure is executed a new plan is compiled. Unfortunately when this happens, SQL server uses the currently passed in value(s) when determining what plan to generate (and cache) for the procedure. If the value(s) that were passed in at that time happen to be uncommon enough, SQL Server may build, and subsequently cache, an execution plan that does a table scan. While this may be optimal for the uncommon value that was passed in, it's clearly not so ideal for a value that would benefit from an index seek.

For more info and possible workarounds, see this technet blog post. http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Upvotes: 1

Related Questions