Stored procedure running for a long time We have a stored procedure which is running for ever, whereas when it is run as script it takes just couple of minutes to complete. When I just declare the params and assign values to them and run the script it's done in couple of mins. There is no application that is consuming the result set,it's a DW proc.

I tried couple of things:


I tried to flush the plan for that procedure  and run again but didn't run.
I tried to run the SP with recompile  but didn't run.


What can be the possible reasons that it runs as plain script and not as SP even after flushing the cache for that SP?
One possibility is that the stored procedure was created with settings for ANSI_NULLS and QUOTED_IDENTIFIER that differ from those set on your local connection.

These settings are stored with the procedure definition and override the session's current value. These two options can dramatically change an execution plan, and may prevent the use of indexes on computed columns and indexed views. Script the stored procedure from SSMS to check the stored settings values.

For a very comprehensive look at general issue see Slow in the Application, Fast in SSMS? by Erland Sommarskog.