Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


connections with status set to 'sleeping'


connections with status set to 'sleeping'

Author
Message
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1427
Hi
We recently had a performance issue and whilst it was occurring I ran adam Mechanics sp whoisactive in a loop and logged the details into a table.
I can see lots of connections from the web app set as 'sleeping' - Would these connections'hold' onto any cpu and thus affect performance ? I can't see any long running queries burning lots of cpu either. I also have lots of 'runable' status's as well and my top wait was SOS_SCHEDULER_YIELD which I know is an absolute indication of a cpu bottleneck (my monitoring software also confirmed this. Also the main process taking the cpu was sqlserv - so it was nothing outside of slqserver.
Any ideas/comments ?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44379
Sessions that are sleeping are not doing anything, therefore they cannot be currently using CPU. A session that is currently using CPU is in the RUNNING state.

SOS_Scheduler_Yield is not an absolute indicator of CPU pressure. It's an indication that you have queries that are doing CPU-intensive tasks without requiring resources that will force them to wait for access (locks, latches, memory structures, etc). You can have SOS_Scheduler_Yield without CPU pressure and you can have CPU pressure without SOS_Scheduler_Yield


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1427
When i look at the results from whoisactive I cant really see any queries that look that intensive as regards burning cpu. I can however see a lot of logins from the account that we use for replication with numbers in the cpu field ranging from13,000 to 24,000 but then next to that is says status 'sleeping' for this login name.
Is there a way to tell if replication was the culprit for the high cpu usage ?
I take it the numbers spwhoisactive returns in the cpu column are the time measurement measured in milli seconds ?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44379
The CPU values that the DMVs return for sessions is ms of CPU time since login time. Hence sessions that have been connected for a long time (as replication jobs usually are) will usually have a high cumulative CPU because they've been doing bits of work for long periods of time. Doesn't mean they're a problem, just that they've been doing bits of work for a long time.

I don't like using the session/requests DMVs for performance tuning as it requires you poll the DMVs and you can easily miss stuff.

For example, which of these is a bigger problem?
A session that logs in when SQL starts and runs a process every 5 minutes, that process takes 50ms of CPU time. It does not disconnect. After SQL has been running for a week, this process shows a CPU total of 100800.
A session that logs in, runs a process that takes 200ms of CPU time and immediately logs out. It does this every minute.
Which are you more likely to see when querying the DMVs?


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1427
Am i right in answering your question as the first login that takes 50ms - As this will be present in the dmvs because it doesn't disconnect.
So are you suggesting spwhoisactive is not the best way to see what queries could be causing the issue - ie it might miss something when it polls the dmv's ?
I didn't want to run profiler on the server as it was under quite a lot of cpu pressure.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44379
PearlJammer1 (11/15/2013)
Am i right in answering your question as the first login that takes 50ms - As this will be present in the dmvs because it doesn't disconnect.


The one that spends 50 ms of CPU time every 5 minutes is the one you'll see when querying the DMVs, the one that spends 200ms of CPU time every minute is by far a bigger problem and you'll only see it in the DMV if you query right as the it's running and even then you'll only see it's current CPU usage, not total over all time.

So are you suggesting spwhoisactive is not the best way to see what queries could be causing the issue


It's fine for seeing if there's a CPU-hogging query running right now. Not so much for getting a complete picture of what is and has been running.

You can use the DMVs, but then you will need to poll then frequently, store the results somewhere and run analysis on those stored results.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search