Why is calling a stored procedure slower than running the code within the stored procedure?

I have a stored procedure that takes over a minute to run. If I take the code within the stored procedure and just run that directly, it takes about 20 seconds. I can't think of anything that would cause that...

If I look at the execution plans they are different, but getting the execution plan on the query itself inc开发者_StackOverflow中文版reases the time to be on par with the stored procedure call.

I tried creating a new sproc with that query, but it was just as slow as the old one...

I'm totally stealing this from Grant Fritchey, but at least I'm giving him proper credit:

Parameter sniffing is usually the cause of something like this. When you run the query as just a query, all the parameters are local, so SQL Server can look at them, sniff them, and determine an execution plan based on the values. As soon as you put parameters in a stored procedure, SQL Server assumes an unknown value in the parameter, correctly, and creates a different execution plan. In most cases, this works well. In some cases it doesn't.

Sounds like you have 'Include Actual Execution Plan' turned on when you run the stored procedure. If so, try it with that option turned off.

Is it possible that the data has changed/grown a lot since the stored procedure was created? Recall that one point of a stored procedure is to cache an execution plan so that the next run didn't have to do that. If the data drastically changed over time the sproc might not perform as well.

To force SQL to build a new execution plan for an sproc and learn more go here