Suddenly Slow Execution Plan for Stored Proc

Question

I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, and two dates.

Now depending on the dates, and the customer, this query can return anything from zero to 1000s of rows.

The problem: what we've experienced is that suddenly we will get a number of errors (typically Execution Timeout Expired or similar) for a particular client while they try execute that stored proc. So we examine the query, run it in SSMS and and find that it takes 30s. So we recompile the stored proc and -bang- it runs now in 300ms.

I've spoken to our DBA about this. He has told me that the database created a query plan when we created the stored proc. He said that it was a good plan for that set of parameters, but if you throw a certain set of parameters at it, then the plan will not be the best plan for that data, and so you will see it running slow.

The options presented to me are the move that problem query from a stored proc and back into dynamic SQL that has it's execution plan created on every run.

This feels like a step back to me and I feel like there must be a way around this. Is there any other way to deal with this issue?

Any and all responses are appreciated.

Answer 1

This issue is called parameter sniffing.

Later versions of SQL Server give you more options in dealing with it such as OPTION (RECOMPILE) or OPTIMIZE FOR hints.

You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time you are getting a reasonably satisfactory plan.

Normally the most catastrophically bad plans are those compiled for parameters with very high selectivity but ran with parameters with low selectivity.

Assuming the plan generated is more robust with this approach and satisfactory for all parameter values then the advantage of this approach over that suggested by JNK is that it does not incur a compilation cost for every call.

The disadvantage is that for some executions run time might be greater than for a plan tailored specifically for those parameter values so it is a trade off of compile time vs execution time.

Answer 2

Instead of using dynamic SQL, you could always just change your proc calls to:

EXEC Database.dbo.usp_Myprocedure 'Parameter' WITH RECOMPILE

The WITH RECOMPILE forces (you guessed it!) a recompile of the execution plan whenever it is run.

You can also include WITH RECOMPILE in the definition of the stored proc:

CREATE PROCEDURE usp.MyProcedure (Parameters)
WITH RECOMPILE
AS
...

Answer 3

You could also attempt to decide for the database which plan to use, though you would be fighting with the optimizer a little bit, so it's more brittle than you would hope.

The technique is this - break the stored procedure into 2, one meant for one set of parameters, one for another. Add where clauses to each such that between them they cover all possible cases. Look at the query plans - one should be optimized for one set of parameters, the other for the other set. You might have to tinker with the query to get this to happen, or this may not be possible to achieve for your query, in which case this approach won't work.

Now make your original stored procedure check the parameter values and dispatch to the appropriate one of the two stored procedures from the previous paragraph.

This can work, but it's kind of a hack to force the optimizer to work more effectively for your query. Like all such hacks, in future versions of the database it might be unnecessary or even make things worse. So even if it works you have to decide if it's worth it.

Answer 4

You can also try SET FORCEPLAN and index hints.

http://msdn.microsoft.com/en-us/library/ms188344.aspx
It basically allows you to choose what order the join happens in.

You can had index hints to ensure that SQL server uses the correct indexes.

Answer 5

Hmmm...if we are focused just on this one stored procedure, I would be surprised that using the cached execution plan would be causing the issue you are seeing. I would ask to see the execution plan of the stored procedure using a set of parameters for customer and the two dates. I wonder if a more specific index would be helpful -> such as on customerId, and the two dates only?

Answer 6

Suddenly degrading performance sounds like an inefficient query plan that is produced, probably as a result of missing statistics. Run a SQL Server profiler with the "Errors and Warnings" event categories set and see if there are warnings about missing stats.

You might also be missing an index, or you might need to defrag the indexes as they may be too fragmented for SQL Server to use, resulting it in thinking that a Table Scan will produce less I/O.

@JNK raises a great point about stored procs - these get compiled upfront and the query plan will be stored along with the the stored procedure.

I don't necessarily agree with using WITH RECOMPILE as you then lose the benefit of the query plan being stored and re-used. There are some cases when this is necessary - i.e. if your distribution statistics in the underlying tables differ greatly between calls, but generally, once the data in the tables is mature, the distribution of data within the tables will vary minimally.

So, to summarise:

1. Check for missing stats
2. Check index fragmentation
3. Create and use a stored proc
4. Please rename the proc - sp_ is a softly reserved prefix namespace for internal system SQL Server procs - resulting in SQL Server always looking in the master database for those stored procedures first. Renaming the proc usp_ instead of sp_ will result in a performance increase, but I doubt its your problem in this case.