How to improve the performance of this query?
Two billion reads seems excessive for a scan that returns only 12 million rows without a predicate. You should look into the reasons for this.
That whole plan section is concerning:
You should check to see you have proper indexes to support these joins and predicates. If there really is no alternative to large hash joins, you should at least look to enable parallelism.
The query takes 2 hours to run and return a distinct of 251K records but the estimated records are 12 billions. How can I fix this?
Even with up-to-date statistical information, cardinality estimation has limits. Queries with complex expressions and many joins often benefit from being broken down into smaller pieces. This is helpful to the optimizer and humans alike. Tuning smaller sections of code is always easier.
The query plan has several areas where cardinality estimation errors are rooted. Consider breaking the query down at these points using temporary tables. These may present additional tuning options including index creation.