Improve query performance
The following query is executing for more than 18mins. need your assistance in tunning this query and ways to improve the query execution performance.We have updated the stats for the table but still see lot of difference in estimated and actual row counts.
…is not too bad at all. You should identify the tables in this section and break it out into a separate query that stores its results (only 42,108 rows) in a temporary table. Be sure to create a (unique) clustered index if at all possible.
The rest of the plan suffers from intra-query parallelism deadlocks, which cause exchange buffers to spill to tempdb:
This can be very slow and resource intensive. Parallel deadlocks are usually associated with multiple order-generating and -preserving operators. In your plan, these are the seeks, exchanges (not the Parallelism operators have an Order By property), and the merge joins. You may be able to reduce or eliminate the parallel deadlocks by reducing the DOP, changing to an odd-numbered DOP (rarely), or using hash joins instead of merge joins (with a hint).
The rows counts in this portion of the plan are not excessive, so I would expect hash join to be a reasonable choice, and you would not need to reduce DOP, since hash joins do not preserve order, and cannot therefore contribute to parallel deadlocks.
By joining to the temporary table mentioned earlier, the whole plan should get better cardinality estimates, and be able to use hash joins efficiently.