CODEWITHSUNDEEP
sql-servert-sqlsql-execution-plansp-executesql
Nathan Tregillus
Nathan Tregillus

Reputation: 6334

Why would the exact same SQL query result with a different execution plan when executed via the sp_executeSQL procedure?

As the title states, I don't understand why the sp_executeSQL would generate a completely different execution plan than running the query from Sql Management Studio.

My query in question will take 3 seconds when run from SQL management Studio, where as the query run in management studio via sp_executeSQL will take 5 minutes.

I've updated statistics, and reviewed indexes, but the fact remained in my head that the execution plan from sp_executeSQL was FAR worse than running the sql directly from my management studio.

So here is my question: Why would the execution plans differ SO much between running the query in these two different ways?

Thanks

Upvotes: 7

Views: 4510

Answers (2)

Beth
Beth

Reputation: 9607

see this

basically, there can be multiple [execution] plans for the same procedure

Upvotes: 5

John Hartsock
John Hartsock

Reputation: 86872

Consider this. When you execute a stored procedure, this procedure will have its own execution plan. When you execute a query statement, again it will have its own execution plan. Now when using sp_executeSQL you are running this stored procedure to execute a query dynamically. So in essence its execution plan is the combination of sp_executeSQL and your query.

Upvotes: 2

Related Questions