patternsqlMinor
Sleeping SPID with Open Transcations
Viewed 0 times
withopensleepingspidtranscations
Problem
This is an output from
I have hundreds of these at any given time...would this impact CPU?
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?
Solution
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.
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.
When its not applying any changes it looks something similar.
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.
Context
StackExchange Database Administrators Q#185655, answer score: 4
Revisions (0)
No revisions yet.