none
Why does the execution plan changes when I add 1 field

    Question

  • Hello,

    I am working on sql 2008 r2 and trying to make a query faster.
    I have a table with a primary key. I have a indexed view with a unique clustered index on it. The ordering of both keys is exactly the same. My query looks like this (Q1)

    select t.field, v.field from table T left outer join view V on T.key=V.key

    If you look at the execution plan you see, Clustered Index Scan (Clustered) on the Table and Clustered Index Scan (ViewClustered) on the View.  There are also Parallelism in it (repartition streams).

    Now if I add 1 field it totally makes a different plan and no longer uses the indexed view. (Q2)

    Now I go back to the first query andd add the option (MAXDOP 1) which removes the  parallelism.(Q3) Query 3 is 4 times faster then Query 1. In the plan and in execution time. If I add that field to it, it keeps using the indexed view (which is good).

    So my question are :

    - Why did the plan changed when you add one field ?

    - What is the mystery of Option (MAXDOP 1) and how did it improve the performance ?

    Constantijn

    Friday, April 05, 2013 3:52 PM

Answers

  • Friday, April 05, 2013 6:35 PM
  • The problem with your description is that you only mention the parts you understand, while the things you don't understand are probably readily available in the query plans. In other words: there is not enough information to answer your specific question.

    To answer your general question: when you add a column to the query, covering indexes may no longer be covering, the average row size of the intermediate results grows, and these changes may tip the balance to a different query plan.

    A parallel plan may have a completely different approach from a serial plan, but in the end it is the total runtime estimate that determines the optimizer's choice. A serial query plan is always generated, and in addition to that a parallel plan may be generated. In such cases, the engine will prefer the parallel plan, unless the server is too busy. In that case the serial plan is executed.  The "mystery" of MAXDOP 1 is that it forces the engine to ignore the parallel plan regardless of the server load.

    The fact that your query 3 is 4 times faster than query 1 maybe just be bad luck or a flaw in SQL Server. However - and more likely - it is also an indication that your statistics may be stale. You should definitely UPDATE STATISTICS and check whether that makes a difference.


    Gert-Jan

    Friday, April 05, 2013 9:32 PM

All replies

  • Friday, April 05, 2013 6:35 PM
  • The problem with your description is that you only mention the parts you understand, while the things you don't understand are probably readily available in the query plans. In other words: there is not enough information to answer your specific question.

    To answer your general question: when you add a column to the query, covering indexes may no longer be covering, the average row size of the intermediate results grows, and these changes may tip the balance to a different query plan.

    A parallel plan may have a completely different approach from a serial plan, but in the end it is the total runtime estimate that determines the optimizer's choice. A serial query plan is always generated, and in addition to that a parallel plan may be generated. In such cases, the engine will prefer the parallel plan, unless the server is too busy. In that case the serial plan is executed.  The "mystery" of MAXDOP 1 is that it forces the engine to ignore the parallel plan regardless of the server load.

    The fact that your query 3 is 4 times faster than query 1 maybe just be bad luck or a flaw in SQL Server. However - and more likely - it is also an indication that your statistics may be stale. You should definitely UPDATE STATISTICS and check whether that makes a difference.


    Gert-Jan

    Friday, April 05, 2013 9:32 PM