Will TSQL return faster results than stored proced

Question

I have a stored procedure that works fine previously. It took 4 to 5 secs to get the results.

I didn't used this stored procedure for the past two months. When I call the same procedure now it takes more than 5 minutes to produce the result. (There is no records populated to my source tables in the past two months)

I converted the stored procedure and executed as TSQL block it is back to normal. But when I convert back to stored procedure again it is taking more than 5 minutes.

I am wondering why it is behaving like this. I used 6 table variables. I just populating those table variables to get the desired results by joining all those.

I already tried the below options

With Recompile at the stored procedure level
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
sp_updatestats

but there is no improvement. When I execute it as TSQL it works fine.

Please suggest me any ideas to optimize the stored procedure.

Answer 1

You may have bad execution plan associated with that proc. Try this one

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

You may also find this interesting to read http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

Answer 2

In your queries, add OPTION(OPTIMIZE FOR UNKNOWN) (as the last clause) to prevent parameter sniffing. For syntax and explanation, see the documentation on Query Hints.

What SQL Server does the first time it runs a Stored Procedure is optimize the execution plan(s) for the parameters that were passed to it. This is done in a process that is called Parameter Sniffing.

In general, execution plans are cached by SQL Server so that SQL Server doesn't have to recompile each time for the same query. The next time the procedure is run, SQL Server will re-use the execution plan(s) for the queries in it... However, the execution plan(s) might be totally inefficient if you call it (them) with different parameters.

The option I gave you will tell to the SQL compiler that the execution plan should not be optimized for specific parameters, but rather for any parameter that is passed to the Stored Procedure.

To quote the documentation:

OPTIMIZE FOR UNKNOWN

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.

In some cases Stored Procedures can benefit from Parameter Sniffing, in some cases they don't. For the Stored Procedures that don't benefit from Paramater Sniffing, you can add the option to each query that uses any of the parameters of the Stored Procedure.