Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

connections with status set to 'sleeping' Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 8:54 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:30 AM
Points: 247, Visits: 976
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 ?
Post #1514332
Posted Thursday, November 14, 2013 9:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1514364
Posted Friday, November 15, 2013 4:17 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:30 AM
Points: 247, Visits: 976
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 ?
Post #1514654
Posted Friday, November 15, 2013 4:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1514658
Posted Friday, November 15, 2013 4:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:30 AM
Points: 247, Visits: 976
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.
Post #1514669
Posted Friday, November 15, 2013 5:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1514690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse