Query runs endlessly

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.

avatar image By Fingers88 0 asked Jan 31 at 06:54 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Feb 03 at 04:56 PM

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.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x650
x463
x90
x19

asked: Jan 31 at 06:54 PM

Seen: 224 times

Last Updated: Feb 03 at 04:56 PM