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

Question

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

Glut

Please note, I have seen the other questions about execution plans in regards to the sp_executeSQL procedure. I just haven't seen an explanation on why the procedure generates such a different plan than management studio, or a custom stored procedure. – Glut

It will generate exactly the same plan as a custom stored procedure would (AFAIK). For both stored procedures and sp_executesql the plan is compiled according to the value of the first set of parameters it sees. When your query has constants and the plan is not auto parameterised then SQL Server can sometimes use contradiction detection to not execute part of the plan and also will use cardinality estimates for those specific values. – Carmarthenshire

Answer 1

see this

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

Ayr

I have not been able to read the whole article you linked, but he makes a statement that is at the crux of my question: "While the procedure is compiled in one go, each query is optimised on its own, and there is no attempt to analyse the flow of execution." so basically, because the query is executed within sp_executeSQL, the query it optimized individually, which to me says, both executions SHOULD have the exact same execution query plan regardless of how I submitted the query. Which is not the case. Does that make sense? (I am continuing to read this article though!) – Glut

Ah HA! so disregard my previous comment. while individual statements are processed independently, any PARAMETERS provided are used in generating the execution plan are considered. AKA Parameter Sniffing. In my case, while the parameters are passed in null, I had if conditions to set them if null. because the parameters will never be null, but the query execution plan generator IS assuming they will be null, the engine does not correctly take the logic in mind when creating the execution plan. – Glut

TL;DR: use ISNULL, or COALESCE methods within the query to correctly assign defaults if null, rather than using if statements before the query. – Glut

(hope you don't mind, I wanted to give you the credit for the answer, because the article had the answer, but I wanted additional detail in this answer to better describe the scenario I ran into). Thanks! – Glut

Answer 2

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.

Catalpa

Totally understand that, but an execution plan is a combination of queries, each with its own step. The problem is that Query 4, which is exactly the same query text between the two, is radically different between the sp_executeSQL call and the adhoc query run from sql management studio – Glut