Trying to get a parallel plan
All the tables in the plan except the driver Temporary.dbo.CntyCdList is partitioned by CntyCd (char(5)) column
The Trans like tables have clustered key CntyCd (char(5), BatchDt (int), BatchSeq (int) and the Property table has a clustered key CntyCd (char(5), PclId (varchar(45)), PclSeqNbr (tinyint).
CntyCd = us county, there are 3300+ of them (= # of partitions) and the amount of records within any county can highly vary according to population.
I have seen Adam Machanic use a driver table and run the query per driver and I thought I could do this with this tables.
This ran on my desktop, which have about 1/50 th data of the production (still has 3300+ counties but the rest tables have a lot more data).
1-) Can this query made so that I drive it by CntyCd and reduce the amount of records processed to per county instead of running all with non cross apply and running against the full database
2-) Is there any way I can get a parallel query underneath the cross apply (nested loop)
3-) Am I doing anything wrong that does not give me a good plan?
The longest wait is EXECYSNC – caused by the parallel index spool. As I explain in that blog post, index spools are always built by a single thread, and the other threads wait on EXECYSNC while that happens. This index spool also inserts rows into a b-tree index without pre-sorting, so it is very likely to cause extensive page splits. You will want to avoid this index spool by providing an appropriate index on the base table.
Some more general comments on the wider query:
The hash join spills to tempdb for some values of the correlated parameter. You should look to eliminate this if possible.
The parallel APPLY pattern generally works best where all the joins are parallel nested loops, following a for-each x compute-y pattern. This tends to distribute work evenly across threads and minimize workspace memory grant.
Refactoring the query to achieve optimal parallel APPLY pattern performance is well beyond the scope of an answer here, however.
As far as TF 8649 in production is concerned, that is a personal decision but I would recommend against it. It is often possible to avoid the need by tinkering with the T-SQL syntax or refactoring, but again that work is out-of-scope here.