How can I get a parallel plan without forcing QUERYTRACEON 8649
The partitioned tables are partitioned a CntyCd char(5) and that is the first column on the primary key at the same time.
In this query every table except the driver table on the top was partitioned. The driver table knows the rows (in the rows column) therefore it scans with rows descending to balance the workload going underneath the threads.
All the tables underneath the cross apply (nested loop) are using a top to eliminating parallelism under the nested loop but only to initiate it from the cross apply above.
This query beats our other queries in magnitudes in performance but I am not able to get an parallel query plan without forcing the trace 8649.
The other thing is is there any way to stop to hash join spilling to disk by maybe faking the numbers coming to it using a top or so?
As a general rule, I look at ways to refactor the design of the table schema, or at a bare minimum the way that the data is being queried before I would ever look at using hints inside of my code. Anytime that you use hints, to introduce limitations to future changes/improvements that might occur inside of SQL Server that have to be tested. Additionally, anytime that I see a hash or sort spill to tempdb, I look at redesign factors that could help limit the expensive operation leasing to the spill from occuring well before I ever try and come up with workarounds to try and increase the memory grants associated with the operation being performed. It is critical to keep in mind that the memory required for the grant associated with the operation is memory that can't be used by the buffer pool for caching data pages, so you risk having a memory grant reserve memory from the buffer pool and the end result is higher disk I/O that slows down query performance overall as a result, or worse further pressure on the data cache that forces lazy writes of tempdb pages out to disk which may even further degrade performance.
I fully understand that change database schemas is a drastic approach that may require significant rewrites of application code or data loading processes, but in the long term, solving any database design flaws pays the best dividends for performance.