Query runs endlessly

Fingers88 2017-01-31 18:54:30

The query was generated by a ETL tool. When I run it, sp_who2, shows 50+, copies of the same query (threads). Most of the queries are suspended and 1 or 2 are runnable but those queries can run for hours, all the while still showing 48 suspended threads of the same task. A wierd part of this is that while the query is running, Windows Task Manager shows CPU around 0%, 40GB free RAM, and disk activity near 0 (no hard faults). I can't figure out what is going on.

In PLan explorer my eye was drawn to the "Hash Match" (76%). But then it fell on the Concatenation below the hash match, which reportedly shows something like 6.4 billion rows! This is an estimated execution plan as I never got the query to complete.

Aaron Bertrand 2017-02-03 16:56:29
Are any of these objects views? What do the functions do? It's really hard to piece together the logic here or why SQL Server is coming up with crazy estimates, especially since the query text has been anonymized and the seemingly problematic object (Object3) doesn't even appear on the join diagram.