7

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

Nathan Tregillus
  • 5,248
  • 2
  • 43
  • 73
  • 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. – Nathan Tregillus Mar 30 '11 at 14:46
  • 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. – Martin Smith Mar 30 '11 at 17:14

2 Answers2

5

see this

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

Beth
  • 9,345
  • 1
  • 19
  • 40
  • 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!) – Nathan Tregillus Mar 30 '11 at 16:56
  • 1
    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. – Nathan Tregillus Mar 30 '11 at 17:38
  • TL;DR: use ISNULL, or COALESCE methods within the query to correctly assign defaults if null, rather than using if statements before the query. – Nathan Tregillus Mar 30 '11 at 17:39
  • (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! – Nathan Tregillus Mar 30 '11 at 17:55
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.

John Hartsock
  • 78,484
  • 22
  • 123
  • 143
  • 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 – Nathan Tregillus Mar 30 '11 at 16:46