August 9, 2011 at 1:09 pm
I have been having some performance issues in my environment and to debugg it have written an utility and configured it with DynaTrace to get the sysprocess dump at the time when a particular request goes beyond a threashold.
Coming to my query - in the sysprocess tables, I see SPID's that are in "sleeping", "Awaiting command" state but are consuming cpu but the wait time is 0. What should I infer from this. Activities that are in sleeping state - why should they be consuming CPU and that too it is growing every second.
Any thoughts on this front will help.
August 9, 2011 at 1:11 pm
Blocking chain (query from activity monitor).
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
I'd also checkout sp_WhoIsActive from adam Machanic
August 9, 2011 at 1:23 pm
August 9, 2011 at 1:28 pm
@SQLFRNDZ (8/9/2011)
If you are working on 2008 versionyou can make use of this dmv in the script
sys.dm_resource_governor_workload_groups
See the comment lines in the above script
Tx I forgot I'm on 2k5 and had to edit that out ;-). When are we going to upgrade!!!!!!!! 😀
August 9, 2011 at 1:40 pm
Ninja's_RGR'us (8/9/2011)
@SQLFRNDZ (8/9/2011)
If you are working on 2008 versionyou can make use of this dmv in the script
sys.dm_resource_governor_workload_groups
See the comment lines in the above script
Tx I forgot I'm on 2k5 and had to edit that out ;-). When are we going to upgrade!!!!!!!! 😀
I remmember that comment only because I commented that line on that script 🙂
August 12, 2011 at 9:38 am
Thanks for the info - my query was why do activities that are in sleeping state consume CPU and that too it is growing every second.
August 12, 2011 at 9:52 am
sakharpe.aashish (8/12/2011)
Thanks for the info - my query was why do activities that are in sleeping state consume CPU and that too it is growing every second.
The only way I can see this is if you have a user using a erp like system where he's constantly opening, closing windows and then hitting a command button once in a while. This will cause a lot of small batches which execute really quickly. But then there will be a lot of time where the user is not doing anything... hence the query sleeping. My ERP keeps the same connection for the user and doesn't use connection pooling so I don't know how things would look on that angle.
If the query was runnable, then it could be a problem. It would mean that this query is waiting on some ressource to be available to be able to run (running state). I say could be a problem because over 150 connections in activity monitor on a busy erp you often have 1-2 connection in that state, but that state might last only for 2 ms and you just happen to run your check at that time.
Check out sp_WhoIsActive by Adam Machanic, it will give you a heck of a lot more info, including what the spid is waiting for and for how long. That's the info you need to base you decision on.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply