1

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?

Improve this question
0

1 Answer 1

Reset to default
4

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.