Now that's a terrifying possability 🙂

However, I got around it by dropping the table.

Before I did that I had to delete all the FK constraints and I scripted them out so I could recreate them on the new table.

None of them have an action specified for UPDATE or DELETE (or all of them are set as NO ACTION that is to say).

After I dropped the table I recreated it and re-added all the data, and it works fine.

I just don't understand what caused the issue.

As for parallelism, I tried setting it to 1 (vs the default of 0) for Max Degree of Parallelism.

After setting it to 1 and trying again, no change (still ran for a while and I killed it).

Also, a new row was added to the table and I tried a delete on the new row (which would have no corresponding values in other FK tables) and same outcome.

I am MOST perplexed....

I really would like to understand a root cause for it all.

Is there anything I could do if I encounter this again to further troubleshoot exactly what SQL server is doing during the long running query?