June 11, 2014 at 2:35 pm
Hi
I noticed that today our cpu on one of the servers seemed to running outside its normal boundaries - normally it averages around 30% usage whereas today it was averaging 50 - 60% - some might say this is not a problem but on our system it seems to transpire to the end users as 'slow response times'.
I was constantly running sp_whoisactive and couldn't see any blocking. We also have redgates sql monitor and no blocking reported on there either.
What sp_whoisactive did reveal was lots of spids from linked servers that had a status of 'dormant' - lots = approx 30 spids that arent usually there. Now that business hours have finsihed the server cpu level has fallen to its normal level for this time period - and the dormant spids have all gone.
Can spids that are dormant cause performance issues ?
June 11, 2014 at 2:53 pm
Dormant (sleeping) sessions aren't doing anything. Therefore they cannot be using CPU.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2014 at 3:35 am
I would imagine that while you're looking at them those processes might be idle, but that they're not idle all the time. When they're not idle, they could be using any amount of CPU depending on the queries being run.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply