CODEWITHSUNDEEP
sqlssmssql-server-2014activity-monitor
user1017882
user1017882

Reputation:

My SQL process keeps switching from 'suspended' to 'running' and back again

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.

Upvotes: 0

Views: 902

Answers (1)

steoleary
steoleary

Reputation: 9298

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)

Upvotes: 2

Related Questions