Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1157
Есть таблица с поминутными срезами "runnable" процессов за длительный интервал времени (взято из таблицы sysprocesses), через джобу, которая каждую минуту записывает в таблицу результаты след. запроса:

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'

Понятно, что поле spid не гарантирует уникальности, т.к. по завершению процесса тот же spid возьмет другой процесс. Т.о. уникальным будет сочетание spid + login_time.

Так вот по этой комбинации, нужно посчитать и вывести историю потребления процессом ресурса cpu или physical_io в след. виде:
Дата среза /*получено через GETDATE()*/, spid, login_time, [приращение значения cpu/physical_io] (c момента последнего присутствия этого процесса в таблице).

Если процесс отсутствует в "прошлой" минуте, значит он в это время был спящим и нужно искать его в прошлых минутах, но не раньше login_time.

Этот результат нужно получить сразу по всем процессам, записанным в таблице.

Заранее спасибо.
8 сен 03, 14:21    [329839]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог

CREATE TABLE stat (dt datetime not null,
spid int not null,
login_time datetime not null,
cpu int not null,
physical_io int not null,
constraint stat_pk primary key (spid, login_time))
go

select s1.dt, s1.spid, s1.login_time,
s1.cpu/s1.physical_io - isnull(s2.cpu,0)/isnull(s2.physical_io,1) as delta
from stat s1 left outer join stat s2 on s1.spid = s2.spid and
s1.login_time = s2.login_time and
datediff(ms, s1.dt, s2.dt) =
(select min(datediff(ms, s1.dt, s3.dt)
from stat s3
where s1.spid = s3.spid and
s1.login_time = s3.login_time and
s1.dt > s3.dt)
8 сен 03, 14:42    [329888]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1157
Не могу понять:

select s1.dt, s1.spid, s1.login_time,
(s1.cpu - isnull(s2.cpu,0)) as delta
from SYSPROCESSES_HISTORY s1 (nolock) left outer join
SYSPROCESSES_HISTORY s2 (nolock) on s1.spid = s2.spid and
s1.login_time = s2.login_time and
datediff(ms, s1.dt, s2.dt) =
(select min(datediff(ms, s1.dt, s3.dt))
from SYSPROCESSES_HISTORY s3 (nolock)
where s1.spid = s3.spid and
s1.login_time = s3.login_time and
s1.dt > s3.dt)
========================================================
Server: Msg 8124, Level 16, State 1, Line 1
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
8 сен 03, 14:59    [329933]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
а, ну да..


select s1.dt, s1.spid, s1.login_time,
s1.cpu/s1.physical_io - isnull(s2.cpu,0)/isnull(s2.physical_io,1) as delta
from stat s1 left outer join stat s2 on s1.spid = s2.spid and
s1.login_time = s2.login_time and
s2.dt =
(select min( s3.dt)
from stat s3
where s1.spid = s3.spid and
s1.login_time = s3.login_time and
s1.dt > s3.dt)

8 сен 03, 15:08    [329958]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
только не min а max тогда уж. прошу пардону
8 сен 03, 15:10    [329962]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1157
Уже большое спасибо, но:

select s1.dt, s1.spid, s1.login_time,
s1.cpu/s1.physical_io - isnull(s2.cpu,0)/isnull(s2.physical_io,1) as delta
from SYSPROCESSES_HISTORY s1 (nolock) left outer join
SYSPROCESSES_HISTORY s2 (nolock) on s1.spid = s2.spid and
s1.login_time = s2.login_time and
s2.dt =
(select min( s3.dt)
from SYSPROCESSES_HISTORY s3 (nolock)
where s1.spid = s3.spid and
s1.login_time = s3.login_time and
s1.dt > s3.dt)
order by s1.dt desc
=========================================================
dt,spid,login_time,delta
2003-09-08 15:21:02.220,1011,2003-09-08 15:11:11.800,2
2003-09-08 15:21:02.220,999,2003-09-08 13:05:16.707,6
2003-09-08 15:21:02.220,968,2003-09-08 12:54:07.517,3
2003-09-08 15:21:02.220,767,2003-09-08 10:41:08.017,3
2003-09-08 15:21:02.220,758,2003-09-08 10:22:16.470,18

(6 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
8 сен 03, 15:24    [330012]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
блин, ну неужели не можешь сам эту ситуацию обработать? Ведь ясно же, что если ДЕЛИШЬ на что-нибудь, то НУЖНО проверять на 0. Вставь CASE
8 сен 03, 15:35    [330051]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1157
Кажись так:

select s1.dt, s1.spid, s1.login_time,
s1.cpu/CASE s1.physical_io
WHEN 0 THEN 1
ELSE s1.physical_io
END
- isnull(s2.cpu,0)/CASE isnull(s2.physical_io,1)
WHEN 0 THEN 1
ELSE isnull(s2.physical_io,1)
END as delta
from SYSPROCESSES_HISTORY s1 (nolock) left outer join
SYSPROCESSES_HISTORY s2 (nolock) on s1.spid = s2.spid and
s1.login_time = s2.login_time and
s2.dt =
(select max( s3.dt)
from SYSPROCESSES_HISTORY s3 (nolock)
where s1.spid = s3.spid and
s1.login_time = s3.login_time and
s1.dt > s3.dt)
order by s1.dt desc
8 сен 03, 16:04    [330131]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1157
Точнее везде cpu:

select s1.dt, s1.spid, s1.login_time,
s1.cpu/CASE s1.cpu
WHEN 0 THEN 1
ELSE s1.cpu
END
- isnull(s2.cpu,0)/CASE isnull(s2.cpu,1)
WHEN 0 THEN 1
ELSE isnull(s2.cpu,1)
END as delta
from SYSPROCESSES_HISTORY s1 (nolock) left outer join
SYSPROCESSES_HISTORY s2 (nolock) on s1.spid = s2.spid and
s1.login_time = s2.login_time and
s2.dt =
(select max( s3.dt)
from SYSPROCESSES_HISTORY s3 (nolock)
where s1.spid = s3.spid and
s1.login_time = s3.login_time and
s1.dt > s3.dt)
order by s1.dt desc

Спасиба ;)
8 сен 03, 16:10    [330147]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104760
CASE isnull(s2.cpu,1) WHEN 0 THEN 1 ELSE isnull(s2.cpu,1) END

есть

ISNULL(NULLIF(s2.cpu, 0), 1)
8 сен 03, 17:22    [330343]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1157
Взял строки для одного процесса (136) в отдельную таблицу:
===========================================================
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

Запрос вернул:
============================================================
dt, spid, login_time, delta
2003-09-12 10:20:03.747, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 09:55:01.433, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 09:50:03.057, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 09:48:00.853, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 09:47:00.603, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 09:43:00.150, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 09:01:00.620, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 08:53:01.293, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 08:42:00.073, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 08:16:00.453, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 08:11:00.407, 136, 2003-09-12 07:54:30.890, 0
2003-09-12 07:57:00.530, 136, 2003-09-12 07:54:30.890, 1

А должен был вернуть: (для cpu)
============================================================
dt spid login_time delta
12.09.2003 10:20 136 12.09.2003 7:54 35 453
12.09.2003 9:55 136 12.09.2003 7:54 19 218
12.09.2003 09:50 136 12.09.2003 7:54 36 953
12.09.2003 9:48 136 12.09.2003 7:54 28 391
12.09.2003 9:47 136 12.09.2003 7:54 24 719
12.09.2003 9:43 136 12.09.2003 7:54 66 750
12.09.2003 9:01 136 12.09.2003 7:54 41 109
12.09.2003 8:53 136 12.09.2003 7:54 50 297
12.09.2003 08:42 136 12.09.2003 7:54 53 686
12.09.2003 8:16 136 12.09.2003 7:54 13 079
12.09.2003 8:11 136 12.09.2003 7:54 97 515
12.09.2003 7:57 136 12.09.2003 7:54 25 890

Текст запроса:

select s1.dt, s1.spid, s1.login_time,
s1.cpu/CASE s1.cpu
WHEN 0 THEN 1
ELSE s1.cpu
END - isnull(s2.cpu,0)/ISNULL(NULLIF(s2.cpu, 0), 1) as delta
from SYSHISTORY s1 (nolock) left outer join
SYSHISTORY s2 (nolock) on s1.spid = s2.spid and
s1.login_time = s2.login_time and
s2.dt =
(select max( s3.dt)
from SYSHISTORY s3 (nolock)
where s1.spid = s3.spid and
s1.login_time = s3.login_time and
s1.dt > s3.dt)
order by s1.dt desc

Подскажите, что не так, пжлста.
12 сен 03, 11:00    [336293]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1157
Может нельзя такое сделать одним запросом?
12 сен 03, 13:08    [336687]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить