I had this happen to me a while ago. The circumstances were a bit specific, so it’s likely not causing your problem, but just in case.
In my case the issue was a query in a stored procedure. The SP used a dozen or so CTEs, and ran fine in Visual Studio on my Dev PC against my local Dev database. In the past, it also ran fine against our Prod database, also from with Visual Studio on my Dev PC. It’s an on demand thing that I hadn’t run for six months or so. This time when I ran it, it hung on the dataflow that started with that SP. I, the past, it took two minutes tops. Now I was killing it after 30 mins, though there was no deadlock, and SP_WHO2 showed ongoing I/O.
The SP used the results from one of its first CTEs in most of the remaining CTEs. Changing that CTE to a table variable solved the issue, and it was back to running in a minute or two. A little rearranging of the query was needed; another CTE had to be changed to a table variable as well, only because it was no longer available after the insert to the first table variable.
I’d be interested in hearing from anyone who can explain why the change to table variables fixed the problem.