3

Sleeping SPID

This is an output from sp_whoisactive. Should I be worried about the sleeping spids with high cpu numbers and an open transaction? The sql_command value is the same as sql_text. CPU and reads do increase over time.

I have hundreds of these at any given time...would this impact CPU?

Improve this question
2
  • Depends on 1) is the user a service/app account? 2) how long has it been idling? 3) is there DDL involved? 4) what are the risks of killing it? 5) are you sure there are no outer queries involved? I know that in general, a dead session with a tran count still active is a useless transaction, but a sleeping one might still have some use...maybe. Ask around
    – clifton_h
    Sep 12, 2017 at 0:11
  • You should understand how the dm views work. Connections can have one session which in turns can have many requests. If the connection has no session which has any active requests, yet has a tran count for a long time, then I would be concerned.
    – clifton_h
    Sep 12, 2017 at 1:23

1 Answer 1

Reset to default
4

I am not sure if your case is similar, but you can see similar behavior with Distribution Agent SPIDs on subscriber.

When its not applying any changes it looks something similar.

enter image description here

So in this case its normal behavior.

Hundreds of idle sessions, as you can imagine, would not impact CPU. But those sessions might be waking up and doing things. The real question is where are those spids coming from? Are all of them from the same application? If so, it might be time to ask the application support guys why it has so many connections hanging around. Maybe they are not closing connections properly.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.