Why is query going parallel?
The weird thing about this is that when I force the query to go serializable, the cost is actually LESS than the parallel plan. So why is SQL Server choosing to make this query go parallel?
Statement 1 is how the query currently executes in production.
Statement 2 has the added MAXOP 1 hint to show the cost is less when the query does not go parallel.
Any help is appreciated.
EDIT: This query isn't really causing any performance problems, and there are some obvious easy tuning wins. I'm really just curious more than anything as to why it is going parallel.
Where search1 is run twice, a permanent decision is made at that point about whether to use parallelism in the final plan.
Using TF 8687 or MAXDOP 1 does not always show the cost of the serial plan considered at the end of the the first search1 run, because further optimization activity can take place in search2. One of the requirements for entry to search2 is that at least 5 tables are referenced in the query. Your query does, so it is very likely search2 ran for you, leading to the result you see.
You can see a breakdown of the costs at each stage using TF 8675 (together with TF 3604). Example output below (slightly edited for clarity):
End of simplification end exploration end search(1), cost: 302.604 end exploration end exploration end exploration end exploration end search(1), cost: 223.317 end exploration end exploration end exploration end exploration end search(2), cost: 179.559 End of post optimization rewrite End of query plan compilation
The first search1 cost is serial, the second is parallel.
End of simplification
end exploration
end exploration
end search(0), cost: 120.197
end exploration
end exploration
end search(1), cost: 109.959
end exploration
end exploration
end search(1), cost: 75.3379
end exploration
end exploration
end search(2), cost: 1.44751
http://technet.microsoft.com/en-us/library/ms188603(v=sql.105).aspx
I would really focus on eliminating some of those key lookups – that will greatly help the performance and stabilize run times.