CODEWITHSUNDEEP
sql-serverperformancestored-procedures
Gerrie Schenck
Gerrie Schenck

Reputation: 22368

performance difference between exec of SP and running the statements

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?

Upvotes: 1

Views: 202

Answers (1)

JNK
JNK

Reputation: 65187

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.

Upvotes: 1

Related Questions