Distinct sort issue
when i execute this query, it takes too much of tempdb usage and executing forever. Last week the same query taken 10mins to execute. I have done stats update on the columns but it seems distinct sort is the issue. Can you assistance me in tuning this query. I do not have a actual execution plan as of now.
The next thing of note is all the tables are performing Clustered Index Scans. These scans are funneling into Hash Matches. I would consider creating 2 joins to each table currently using a CASE statement in the ON clause. This will allow a nonclustered index to be created for each join condition and allow the optimizer to more accurately estimate the rows and increase the performance of the new query.