November 14, 2013 at 8:54 am
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 ?
November 14, 2013 at 9:47 am
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
November 15, 2013 at 4:17 am
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 ?
November 15, 2013 at 4:23 am
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
November 15, 2013 at 4:58 am
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.
November 15, 2013 at 5:52 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply