SQL Server performance - running ad-hoc queries versus compiled in a stored procedure

Question

Is there any logical reason why a stored procedure would run very slow (>60 secs), but if I run the exact same code as a regular SQL script it would execute in less than 3 seconds?

To my way of thinking, they should run the same, but that is not what I am seeing. I suspect there is something else going on, but wanted to see if anyone else had seen something similar.

The situation is my client reported a slow running SP, which I confirmed, so I added an index, ran the code outside the SP and it run fantastically fast, but then I re-ran the SP and it didn't improve.

I also dropped and re-created the SP just in case, but somehow it seems it might be using an old execution plan each time the SP runs??

Answer 1

could be parameter sniffing or the proc is maybe called with the setting ARITHABORT to OFF

can you show the code?

Answer 2

A single execution plan is cached for stored procedures. If based on parameters the procedure typically produces wildly different results/search patterns its possible a sub-optimal cached execution plan was used to resolve the procedure query.

Some ways to fix:

Use WITH RECOMPILE so that a new plan is used each time

Use hints to tell the optimizer how to behave (index=,robust plan..etc)

Redesign procedure/system to ensure similiar access patterns regardless of parameter values.

Answer 3

This is probably a cached execution plan issue.. I've seen it happen quite a few times where the stored procedure will timeout but running the same SQL from query analyzer will come back instantly. The two easy ways I know to fix it at the moment:

Clear the Execution Cache

This will clear the bad cached plan from the server (along with everything else). Not exactly a long-term solution since the stored procedure will probably have the issue again in the future but it's a good temporary solution.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Add WITH RECOMPILE to the Stored Procedure

CREATE PROCEDURE MyExample
WITH RECOMPILE
AS ...

Adding the WITH RECOMPILE parameter to the stored procedure makes SQL Server create a new execution plan each time the procedure is run. This will hurt performance but it's definitely better to take a small performance hit than having the whole procedure run thousands of times slower or timeout like before.

Parameter Sniffing

Take a look at this article on parameter sniffing in stored procedures. According to the article, you can slightly modify your stored procedure code to disable MS SQL's parameter sniffing which may also help fix the problem.