0

I have a query that takes a long, long time to run (relatively speaking). It retrieves many rows with multiple varbinary(max) columns. This query needs optimising, no doubt about it - but my question is very specific to the ever-changing 'task state' I'm witnessing in SQL activity monitor.

Every 5 seconds or so the task state changes from suspended to running, then back again. What does this imply?

Note: I may raise a separate question regarding optimisation of such a query - but I'm not asking that for now, I'm asking very specifically about the quick change in state.

NOT A DUPLICATE BECAUSE:

I'm asking about the change in quick succession of the task state, I'm not asking what suspended means. I'm asking (if suspended means a wait on I/O) why it would wait on I/O, then not, quickly, many times per query.

  • 1
    Possible duplicate of [What does a Status of "Suspended" and high DiskIO means from sp\_who2?](http://stackoverflow.com/questions/17817177/what-does-a-status-of-suspended-and-high-diskio-means-from-sp-who2) – Chris Pickford Jun 21 '16 at 10:03
  • I google'd what suspended meant Chris, which led me to that question - it's not what I'm asking here. Thanks anyway. –  Jun 21 '16 at 10:08
  • Sorry, I thought that the explanation in the other question would have sufficed. The state changes rapidly due to accessing pages on disk then processing them repeatedly. As you mention, optimising the query to reduce disk IO would reduce the % the task spends suspended. – Chris Pickford Jun 21 '16 at 10:36

1 Answers1

2

This is normal, SUSPENDED simply means that the session is waiting for an event, such as I/O, to complete. You will find that sessions flick in and out of this state rather frequently.

You can see the explanations of the different statuses in this document here:

sp_who (Transact-SQL)

steoleary
  • 8,968
  • 2
  • 33
  • 47