Why is query going parallel?

SQL_JGood 2014-07-17 15:49:19

I'm trying to figure out what is causing this query to go parallel. The current cost threshold on the server is set to 40 and the estimated cost of the query in question is 1.43.

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.

/storage/attachments/960-tmpD94A_Anonymized.queryanalysis

SQLkiwi 2014-07-18 02:57:50

At a high level, the query optimizer can run up to three stages of exploration, known as search0, search1, and search2. Search1 may also be run twice; once with a requirement to produce a serial plan, a second time with a parallel plan required (if the estimated cost from the first run through search1 was greater than the cost threshold for parallelism).

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.

Robert L Davis 2014-07-18 04:00:49

That sound you hear is our brain exploding.

SQL_JGood 2014-07-18 12:31:12

haha yea what Robert said: brain exploding. Thanks for the info Paul.

SQL_JGood 2014-07-18 12:55:03

Here's the breakdown of the costs from the actual query – using TF's 8675 and 3604 – in case anyone was curious.

End of simplification

end exploration

end search(0), cost: 120.197

end exploration

end search(1), cost: 109.959

end exploration

end search(1), cost: 75.3379

end exploration

end search(2), cost: 1.44751

SQLkiwi 2014-07-18 14:02:52

I was curious. Thanks!

@SQLTrooper 2014-07-17 15:56:56

"In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism value. This can happen because the decision to use a parallel or serial plan is based on a cost estimate provided before the full optimization is complete."

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.

SQL_JGood 2014-07-17 16:10:08

Thanks. Performance isn't really an issue (at least for now) and there could be some easy wins with those key lookups. I was just curious more than anything as to why it was going parallel.

July 17, 2014

No Comments