-1

I am running a query in SQL Server 2008r2, it is stuck with

Task State: Suspended (Sometimes changes to Running but mostly Suspended)

Wait Type: IO_Completion.

There is another query running on the tempdb at the same time.

Is there any way to solve this issue?

Thanks

daniely
  • 7,313
  • 5
  • 29
  • 46
  • Can you cancel the query or is is truely stuck in an IO operation? That would point to a faulty disk (which is unlikely by itself). – usr Jul 05 '13 at 16:27
  • I can run the query manually in SQL Server Management studio, but when it is run as part of my desktop program (in c#), it hangs... My desktop program runs very few queries, all of which can be run quickly if I do them manually on management studio – daniely Jul 05 '13 at 16:34
  • What happens in management studio if you run the query while one of those other ones is running? – Dan Bracuk Jul 05 '13 at 16:48
  • If a query does a lot of IO that's what it will show in the wait type while it's running. It doesn't necessarily mean it's stuck. You say it sometimes shows as RUNNING. This tells me it is not actually stuck. – Vlad G. Jul 05 '13 at 17:26
  • There may be many reasons why a query runs different from the SSMS vs. the app. If you give more details, we may be able to help. For starters, what is the query you are running? – Vlad G. Jul 05 '13 at 17:28
  • I am running a large Merge query (10 joins) while running some simple select queries at the same time. – daniely Jul 05 '13 at 19:19

1 Answers1

0

The problem was due to a join returning too many rows as intermediary results.

I changed the join order and forced the query to run in that order, it solved the problem.

daniely
  • 7,313
  • 5
  • 29
  • 46