performance difference between exec of SP and running the statements

Question

I am experiencing a huge performance difference when calling a stored procedure with EXEC (with parameters) or running the exact same statements inside the stored procedure separately in Management Studio. The EXEC is 3-4 times slower. What causes this?

Answer 1

See this answer.

It's a phenomenon called "parameter sniffing" - basically SQL tries to create the optimal execution plan based on what it expects your parameters to be, which is often not accurate at all.