September 12, 2003 at 1:59 am
Hi!
I have an every minute job, that records all runnable processes into a table named SYSHISTORY, with the following query:
INSERT INTO SYSHISTORY ...
SELECT GETDATE(),spid, kpid, blocked, waittype, waittime, lastwaittype, waitresource, dbid, uid, cpu, physical_io,
[memusage], login_time, last_batch, ecid, open_tran, status, sid, hostname, program_name, hostprocess,
cmd, nt_domain, nt_username, net_address, net_library, loginame, [context_info], sql_handle, stmt_start,
stmt_end
FROM master..sysprocesses (nolock)
WHERE status='runnable'
To figure out what processes use most cpu or physical_io, I need to write a query from this table, and return the cpu increments since the last time the process was runnable. To simplify the explanation, I take data for one process (to tell one process, from another we need to consider its login_time in addition to spid)
This is part of the table SYSHISTORY for process 136, that logged in at 2003-09-12 07:54:30.890:
===========================================================
dt, spid, cpu, physical_io, login_time
2003-09-12 10:20:03.747, 136, 493060, 1380, 2003-09-12 07:54:30.890
2003-09-12 09:55:01.433, 136, 457607, 1233, 2003-09-12 07:54:30.890
2003-09-12 09:50:03.057, 136, 438389, 1107, 2003-09-12 07:54:30.890
2003-09-12 09:48:00.853, 136, 401436, 1065, 2003-09-12 07:54:30.890
2003-09-12 09:47:00.603, 136, 373045, 1035, 2003-09-12 07:54:30.890
2003-09-12 09:43:00.150, 136, 348326, 999, 2003-09-12 07:54:30.890
2003-09-12 09:01:00.620, 136, 281576, 856, 2003-09-12 07:54:30.890
2003-09-12 08:53:01.293, 136, 240467, 803, 2003-09-12 07:54:30.890
2003-09-12 08:42:00.073, 136, 190170, 640, 2003-09-12 07:54:30.890
2003-09-12 08:16:00.453, 136, 136484, 556, 2003-09-12 07:54:30.890
2003-09-12 08:11:00.407, 136, 123405, 501, 2003-09-12 07:54:30.890
2003-09-12 07:57:00.530, 136, 25890, 358, 2003-09-12 07:54:30.890
This is what I want to get for cpu:
============================================================
dt spid login_time delta
12.09.2003 10:20 136 12.09.2003 7:54 35453
12.09.2003 9:55 136 12.09.2003 7:54 19218
12.09.2003 09:50 136 12.09.2003 7:54 36953
12.09.2003 9:48 136 12.09.2003 7:54 28391
12.09.2003 9:47 136 12.09.2003 7:54 24719
12.09.2003 9:43 136 12.09.2003 7:54 66750
12.09.2003 9:01 136 12.09.2003 7:54 41109
12.09.2003 8:53 136 12.09.2003 7:54 50297
12.09.2003 08:42 136 12.09.2003 7:54 53686
12.09.2003 8:16 136 12.09.2003 7:54 13079
12.09.2003 8:11 136 12.09.2003 7:54 97515
12.09.2003 7:57 136 12.09.2003 7:54 25890
Thanks.
September 12, 2003 at 3:20 am
hi,
i created a table as follows :-
create table cpu_usage
(dt datetime, spid int, cumulative_cpu bigint, cumulative_io bigint, login_time datetime)
inserted the data from your original post and did the following :-
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cumulative_cpu - cpu2.cumulative_cpu 'delta'
from cpu_usage cpu1, cpu_usage cpu2
where cpu1.dt > (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt)
and cpu2.dt = (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt)
and cpu1.spid =cpu2.spid
and cpu1.login_time=cpu2.login_time
union
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cumulative_cpu 'delta'
from cpu_usage cpu1
where cpu1.dt = (select min(cpu3.dt) from cpu_usage cpu3)
order by cpu1.dt desc
the union is to get the 12th row that wouldn't otherwise appear because of the
cpu1.dt > (select max(cpu3.dt) bit of the first clause
HTH
Paul
September 12, 2003 at 4:20 am
/*********************
Move DAta to Tempt table with RowID ordered by SPID, loginTime
*/
Insert into #tmp
Select SPID, LoginTime, DT, CPU
FRom cpu_usage cpu1
order by SPID, LoginTime, DT (desc ) -- This is Important
Select
a.SPID, a.LoginTime, a.DT,
Delta = a.CPU - b.cpu
From cpu_usage a
Left join cpu_usage a
on a.RowID = 1+ b.rowid
And a.SPID = b.SPID
And a.LoginTime = b.LoginTime
September 14, 2003 at 6:05 am
2ripg1011:
It works fine for one process, so I took a few others:
dt spidcpuphysical_iologin_time
12.09.2003 12:2715178012.09.2003 12:27
12.09.2003 12:15151281410012.09.2003 12:03
12.09.2003 11:271511515912.09.2003 11:26
12.09.2003 11:05136643407172712.09.2003 7:54
12.09.2003 10:52136637077166912.09.2003 7:54
12.09.2003 10:42136572171155712.09.2003 7:54
12.09.2003 10:20136493060138012.09.2003 7:54
12.09.2003 9:55 136457607123312.09.2003 7:54
12.09.2003 9:50 136438389110712.09.2003 7:54
12.09.2003 9:48 136401436106512.09.2003 7:54
12.09.2003 9:47 136373045103512.09.2003 7:54
12.09.2003 9:43 13634832699912.09.2003 7:54
12.09.2003 9:01 13628157685612.09.2003 7:54
12.09.2003 8:53 13624046780312.09.2003 7:54
12.09.2003 8:42 13619017064012.09.2003 7:54
12.09.2003 8:16 13613648455612.09.2003 7:54
12.09.2003 8:11 13612340550112.09.2003 7:54
12.09.2003 7:57 1362589035812.09.2003 7:54
12.08.2003 14:15151330486186312.08.2003 9:28
12.08.2003 14:14151303033175012.08.2003 9:28
12.08.2003 14:13151261205168712.08.2003 9:28
12.08.2003 14:12151222799153612.08.2003 9:28
12.08.2003 14:10151136407126812.08.2003 9:28
12.08.2003 14:09151101563116512.08.2003 9:28
12.08.2003 14:0715182189109912.08.2003 9:28
12.08.2003 14:0515178859109312.08.2003 9:28
12.08.2003 14:041515210997912.08.2003 9:28
12.08.2003 7:46 13632112.08.2003 7:46
(28 row(s) affected)
I got:
dt, spid, login_time, delta
2003-09-12 11:05:00.2931362003-09-12 07:54:30.8906330
2003-09-12 10:52:00.5401362003-09-12 07:54:30.89064906
2003-09-12 10:42:00.5731362003-09-12 07:54:30.89079111
2003-09-12 10:20:03.7471362003-09-12 07:54:30.89035453
2003-09-12 09:55:01.4331362003-09-12 07:54:30.89019218
2003-09-12 09:50:03.0571362003-09-12 07:54:30.89036953
2003-09-12 09:48:00.8531362003-09-12 07:54:30.89028391
2003-09-12 09:47:00.6031362003-09-12 07:54:30.89024719
2003-09-12 09:43:00.1501362003-09-12 07:54:30.89066750
2003-09-12 09:01:00.6201362003-09-12 07:54:30.89041109
2003-09-12 08:53:01.2931362003-09-12 07:54:30.89050297
2003-09-12 08:42:00.0731362003-09-12 07:54:30.89053686
2003-09-12 08:16:00.4531362003-09-12 07:54:30.89013079
2003-09-12 08:11:00.4071362003-09-12 07:54:30.89097515
2003-08-12 14:15:01.4771512003-08-12 09:28:15.46027453
2003-08-12 14:14:04.8371512003-08-12 09:28:15.46041828
2003-08-12 14:13:02.3531512003-08-12 09:28:15.46038406
2003-08-12 14:12:03.2101512003-08-12 09:28:15.46086392
2003-08-12 14:10:02.5701512003-08-12 09:28:15.46034844
2003-08-12 14:09:00.8831512003-08-12 09:28:15.46019374
2003-08-12 14:07:00.7571512003-08-12 09:28:15.4603330
2003-08-12 14:05:00.6501512003-08-12 09:28:15.46026750
2003-08-12 07:46:00.8201362003-08-12 07:46:00.78732
(23 row(s) affected)
But this is what is needed:
dt spidlogin_time delta
12.09.2003 12:2715112.09.2003 12:2778
12.09.2003 12:1515112.09.2003 12:032814
12.09.2003 11:2715112.09.2003 11:261515
12.09.2003 11:0513612.09.2003 7:54 6330
12.09.2003 10:5213612.09.2003 7:5464906
12.09.2003 10:4213612.09.2003 7:5479111
12.09.2003 10:2013612.09.2003 7:5435453
12.09.2003 9:55 13612.09.2003 7:5419218
12.09.2003 9:50 13612.09.2003 7:5436953
12.09.2003 9:48 13612.09.2003 7:5428391
12.09.2003 9:47 13612.09.2003 7:5424719
12.09.2003 9:43 13612.09.2003 7:5466750
12.09.2003 9:01 13612.09.2003 7:5441109
12.09.2003 8:53 13612.09.2003 7:5450297
12.09.2003 8:42 13612.09.2003 7:5453686
12.09.2003 8:16 13612.09.2003 7:5413079
12.09.2003 8:11 13612.09.2003 7:5497515
12.09.2003 7:57 13612.09.2003 7:5425890
12.08.2003 14:1515112.08.2003 9:2827453
12.08.2003 14:1415112.08.2003 9:2841828
12.08.2003 14:1315112.08.2003 9:2838406
12.08.2003 14:1215112.08.2003 9:2886392
12.08.2003 14:1015112.08.2003 9:2834844
12.08.2003 14:0915112.08.2003 9:2819374
12.08.2003 14:0715112.08.2003 9:283330
12.08.2003 14:0515112.08.2003 9:2826750
12.08.2003 14:0415112.08.2003 9:2852109
12.08.2003 7:46 13612.08.2003 7:4632
(23 row(s) affected)
What can I do?
September 15, 2003 at 1:52 am
hi,
just change the order by to order by clause to :-
cpu1.dt desc, cpu1.spid
Paul
September 15, 2003 at 3:45 am
2ripg1011: I changed clause "order by cpu1.dt desc" to "order by cpu1.dt desc, cpu1.spid", but still get 23 instead of 28 rows for previous example. This is the query I tested:
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cpu - cpu2.cpu 'delta'
from cpu_usage cpu1, cpu_usage cpu2
where cpu1.dt > (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt)
and cpu2.dt = (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt)
and cpu1.spid =cpu2.spid
and cpu1.login_time=cpu2.login_time
union
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cpu 'delta'
from cpu_usage cpu1
where cpu1.dt = (select min(cpu3.dt) from cpu_usage cpu3)
order by cpu1.dt desc, cpu1.spid
Did I misunderstood you?
September 15, 2003 at 4:46 am
hi,
try this new and improved version :-
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cumulative_cpu - cpu2.cumulative_cpu 'delta'
from cpu_usage cpu1, cpu_usage cpu2
where cpu1.dt > (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt and cpu3.spid=cpu1.spid)
and cpu2.dt = (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt and cpu3.spid=cpu2.spid)
and cpu1.spid =cpu2.spid
union
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cumulative_cpu 'delta'
from cpu_usage cpu1
where cpu1.dt = (select min(cpu3.dt) from cpu_usage cpu3 where cpu3.spid=cpu1.spid)
order by cpu1.dt desc
I've removed the join on login time, and also edited the date subselects to take account of spid .
Let me know how it goes !
Paul
September 15, 2003 at 5:04 am
Hi!
It works almost perfect. I does not handle "single row" processes. So I got all 28 rows,
but the first three of them were not correct:
dtspidlogin_timedelta
12.09.2003 12:2715112.09.2003 12:27-2736
12.09.2003 12:1515112.09.2003 12:031299
12.09.2003 11:2715112.09.2003 11:26-328971
12.09.2003 11:0513612.09.2003 7:546330
12.09.2003 10:5213612.09.2003 7:5464906
12.09.2003 10:4213612.09.2003 7:5479111
12.09.2003 10:2013612.09.2003 7:5435453
12.09.2003 9:5513612.09.2003 7:5419218
12.09.2003 9:5013612.09.2003 7:5436953
12.09.2003 9:4813612.09.2003 7:5428391
12.09.2003 9:4713612.09.2003 7:5424719
12.09.2003 9:4313612.09.2003 7:5466750
12.09.2003 9:0113612.09.2003 7:5441109
12.09.2003 8:5313612.09.2003 7:5450297
12.09.2003 8:4213612.09.2003 7:5453686
12.09.2003 8:1613612.09.2003 7:5413079
12.09.2003 8:1113612.09.2003 7:5497515
12.09.2003 7:5713612.09.2003 7:5425858
12.08.2003 14:1515112.08.2003 9:2827453
12.08.2003 14:1415112.08.2003 9:2841828
12.08.2003 14:1315112.08.2003 9:2838406
12.08.2003 14:1215112.08.2003 9:2886392
12.08.2003 14:1015112.08.2003 9:2834844
12.08.2003 14:0915112.08.2003 9:2819374
12.08.2003 14:0715112.08.2003 9:283330
12.08.2003 14:0515112.08.2003 9:2826750
12.08.2003 14:0415112.08.2003 9:2852109
12.08.2003 7:4613612.08.2003 7:4632
(28 row(s) affected)
I need the first three rows look this:
dtspidlogin_timedelta
12.09.2003 12:2715112.09.2003 12:2778
12.09.2003 12:1515112.09.2003 12:032814
12.09.2003 11:2715112.09.2003 11:261515
Thanks.
September 15, 2003 at 6:39 am
hi,
hopefully the last one :-
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cumulative_cpu - cpu2.cumulative_cpu 'delta'
from cpu_usage cpu1, cpu_usage cpu2
where cpu1.dt > (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt and cpu3.spid=cpu1.spid)
and cpu2.dt = (select max(cpu3.dt) from cpu_usage cpu3 where cpu3.dt < cpu1.dt and cpu3.spid=cpu2.spid)
and cpu1.spid =cpu2.spid
and cpu1.login_time=cpu2.login_time
union
select cpu1.dt ,cpu1.spid ,cpu1.login_time ,cpu1.cumulative_cpu 'delta'
from cpu_usage cpu1
where cpu1.dt = (select min(cpu3.dt) from cpu_usage cpu3 where cpu3.spid=cpu1.spid
and cpu3.login_time=cpu1.login_time)
order by cpu1.dt desc
what i've done is put the join on login time back into the first part of the union and added a clause in the second part for login time.
The problem was that the records that were in-correct had the same spid but different login time.
I think that this should sort out your problem
Paul
September 15, 2003 at 6:52 am
Hi!
Thanks a lot! This one works perfect.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply