Adding a clustered index fixed it. After adding the clustered index on those two columns the query returns in 0 seconds. Here are the new results from those two queries:

name rows reserveddata index_sizeunused

Registration_loaderr881866 93704 KB92392 KB656 KB 656 KB

index_idavg_fragmentation_in_percentpage_countavg_page_space_used_in_percent

1 0.0606113083383843 11549 99.2834198171485

Notice the table went from 266M to 93M and the page count went from 31k to 11k. I didn't know heaps reacted that way to a delete statement. It's really good to know, thank you.

Bhuvnesh, that would certainly return the same results and would probably be a better way of writing the query.

Thank you both for your help! It's greatly appreciated!