MS SQL Server 2005 - Stored Procedure "Spontaneously Breaks"

Question

A client has reported repeated instances of Very strange behaviour when executing a stored procedure.

They have code which runs off a cached transposition of a volatile dataset. A stored proc was written to reprocess the dataset on demand if:
1. The dataset had changed since the last reprocessing
2. The datset has been unchanged for 5 minutes

(The second condition stops massive repeated recalculation during times of change.)

This worked fine for a couple of weeks, the SP was taking 1-2 seconds to complete the re-processing, and it only did it when required. Then...

• The SP suddenly "stopped working" (it just kept running and never returned)
• We changed the SP in a subtle way and it worked again
• A few days later it stopped working again
• Someone then said "we've seen this before, just recompile the SP"
• With no change to the code we recompiled the SP, and it worked
• A few days later it stopped working again

This has now repeated many, many times. The SP suddenly "stops working", never returning and the client times out. (We tried running it through management studio and cancelled the query after 15 minutes.)

Yet every time we recompile the SP, it suddenly works again.

I haven't yet tried WITH RECOMPILE on the appropriate EXEC statments, but I don't particularly want to do that any way. It gets called hundred of times an hour and normally does Nothing (It only reprocesses the data a few times a day). If possible I want to avoid the overhead of recompiling what is a relatively complicated SP "just to avoid something which "shouldn't" happen...

• Has anyone experienced this before?
• Does anyone have any suggestions on how to overcome it?

Cheers,
Dems.

EDIT:

The pseduo-code would be as follows:

• read "a" from table_x
• read "b" from table_x
• If (a < b) return
• BEGIN TRANSACTION
• DELETE table_y
• INSERT INTO table_y <3 selects unioned together>
• UPDATE table_x
• COMMIT TRANSACTION

The selects are "not pretty", but when executed in-line they execute in no time. Including when the SP refuses to complete. And the profiler shows it is the INSERT at which the SP "stalls"

There are no parameters to the SP, and sp_lock shows nothing blocking the process.

Answer 1

This is the footprint of parameter-sniffing. Yes, first step is to try RECOMPILE, though it doesn't always work the way that you want it to on 2005.

Update: I would try statement-level Recompile on the INSERT anyway as this might be a statistics problem (oh yeah, check that automatics statistics updating is on).

If this does not seem to fit parameter-sniffing, then compare th actual query plan from when it works correctly and from when it is running forever (use estimated plan if you cannot get the actual, though actual is better). You are looking to see if the plan changes or not.

Answer 2

I totally agree with the parameter sniffing diagnosis. If you have input parameters to the SP which are varying (or even if they aren't varying) - be sure to mask them with a local variable and use the local variable in the SP.

You can also use the WITH RECOMPILE if the set is changing but the query plan is no longer any good.

In SQL Server 2008, you can use the OPTIMIZE FOR UNKNOWN feature.

Also, if your process involves populating a table and then using that table in another operation, I recommend breaking the process up into separate SPs and calling them individually WITH RECOMPILE. I think the plans generated at the outset of the process can sometimes be very poor (so poor as not to complete) when you populate a table and then use the results of that table to carry out an operation. Because at the time of the initial plan, the table was a lot different than after the initial insert.

Answer 3

As others have said, something about the way the data or the source table statistics are changing is causing the cached query plan to go stale.

WITH RECOMPILE will probably be the quickest fix - use SET STATISTICS TIME ON to find out what the recompilation cost actually is before dismissing it out of hand.

If that's still not an acceptable solution, the best option is probably to try to refactor the insert statement.

You don't say whether you're using UNION or UNION ALL in your insert statement. I've seen INSERT INTO with UNION produce some bizarre query plans, particularly on pre-SP2 versions of SQL 2005.

• Raj's suggestion of dropping and recreating the target table with SELECT INTO is one way to go.

• You could also try selecting each of the three source queries into their own temporary table, then UNION those temp tables together in the insert.

• Alternatively, you could try a combination of these suggestions - put the results of the union into a temporary table with SELECT INTO, then insert from that into the target table.

I've seen all of these approaches resolve performance problems in similar scenarios; testing will reveal which gives the best results with the data you have.