일정 시간 동안의 서버 활동 추적 정보 리턴

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[sp_active_processes]    Script Date: 07/29/2010 16:57:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[sp_active_processes]

@loginame varchar(30) = NULL, -- 'ACTIVEONLY' | spid | login name

@duration int = 5 -- 예제 샘플에 대한 시간

/*

sp_active_processes @duration=10 --모든 프로세스들을 10초간 모니터한다.

sp_active_processes 'ACTIVEONLY', 30 -- 모든 프로세스들을 30초간 모니터하고, 활성 상태의 프로세스들 목록을 리턴한다.

sp_active_processes 34, 5 -- spid가 34인 프로세스를 5초간 모니터한다.

*/

AS

SET NOCOUNT ON

DECLARE @before datetime,

@after datetime,

@lowlogin sysname,

@highlogin sysname,

@spidlow int,

@spidhigh int

SELECT @lowlogin = '',

@highlogin = REPLICATE('z', TYPEPROPERTY('sysname', 'Precision')),

@spidlow = 0,

@spidhigh = 32767

--@loginname을 크랙한다.

IF (@loginame <> 'ACTIVEONLY') AND (@loginame IS NOT NULL) BEGIN

SELECT @lowlogin = @loginame, 

@highlogin = @loginame

IF SUSER_SID(@lowlogin) IS NULL BEGIN

IF @loginame LIKE '[0-9]%'

SELECT @spidlow = CAST(@loginame AS int),

@spidhigh = CAST(@loginame AS int),

@lowlogin = '',

@highlogin = REPLICATE('z', TYPEPROPERTY('sysname', 'Precision'))

ELSE BEGIN

PRINT 'Invalid loginname'

PRINT CHAR(13)

END

END

END

-- 락 정보

SELECT request_session_id AS spid, 'L1'=COUNT(*), 'L2'=0 INTO #LCKS 

FROM sys.dm_tran_locks 

WHERE request_session_id BETWEEN @spidlow AND @spidhigh GROUP BY request_session_id

-- Save off time

SELECT @before = CURRENT_TIMESTAMP

-- 프로세스 정보

SELECT spid, loginame, C1=cpu, C2=0, I1=physical_io, I2=0, CM1=cmd, CM2=CAST(' LOGGED OFF' AS CHAR(16)), S1=CAST(STATUS AS CHAR(16)),

S2=SPACE(16), B2=0, dbid=0, hostname=SPACE(10) INTO #PRCS

FROM master..sysprocesses

WHERE loginame BETWEEN @lowlogin AND @highlogin AND spid BETWEEN @spidlow AND @spidhigh

-- 지정된 시간만큼 대기

DECLARE @WAITFORSTR varchar(30)

SET @WAITFORSTR='WAITFOR DELAY ''' + CONVERT(char(8), DATEADD(ss, @duration, '19000101'), 108) + ''''

EXEC (@WAITFORSTR)

--락 정보

INSERT #LCKS 

SELECT DISTINCT request_session_id AS spid, 0, COUNT(*)

FROM sys.dm_tran_locks

WHERE request_session_id BETWEEN @spidlow AND @spidhigh

GROUP BY request_session_id

-- 시간을 다시 얻는다

SELECT @after = CURRENT_TIMESTAMP

--1초간 프로세스 정보를 다시 얻는다.

INSERT #PRCS

SELECT spid, loginame, 0, cpu, 0, physical_io, ' ', cmd, ' ', status, blocked, dbid, hostname

FROM master..sysprocesses

WHERE loginame BETWEEN @lowlogin AND @highlogin AND spid BETWEEN @spidlow AND @spidhigh

--작업 테이블에 락을 건 모든 프로세스들의 정보를 얻는다.

INSERT #LCKS 

SELECT DISTINCT spid, 0, 0 FROM #PRCS

--프로세스에서 블로킹한 것을 알아내고 작업 테이블에 락을 건다.

SELECT SPID=B2, BLKING=STR(COUNT(*), 4) INTO #BLK

FROM #PRCS

WHERE B2 <> 0

GROUP BY B2

INSERT #BLK

SELECT DISTINCT l.spid, STR(0, 4)

FROM #LCKS l LEFT OUTER JOIN #BLK b ON (l.spid <> b.spid)

WHERE b.spid IS NULL

--보고서 상단을 출력한다.

PRINT 'STATISTICS FOR ' + @@SERVERNAME + ' AS OF ' + CAST(CURRENT_TIMESTAMP AS varchar)

PRINT 'ACTIVITY OF ' + 

CASE WHEN @lowlogin=@highlogin THEN 'LOGIN ' + @loginame 

ELSE UPPER(LEFT(ISNULL(@loginame, 'ALL'),6))+' LOGINS' 

END + 

' FOR THE PAST ' + CAST(DATEDIFF(SS, @before, @after) AS varchar) + ' SECOND(S)'

PRINT CHAR(13)

--보고서 본문을 출력한다.

SELECT ' A'=CASE WHEN P.spid=@@SPID THEN '*' ELSE ' ' END+

CASE WHEN (L.L2<>L.L1)

OR (P.C2<>P.C1)

OR (P.I2<>P.I1)

OR (P.CM1<>P.CM2)

OR (P.S1<>P.S2)

THEN 'A'

ELSE 'I'

END,

SPID = STR(P.spid, 5),

LOGIN = LEFT(P.loginame, 20),

HOST = P.HOSTNAME,

--C1, C2, I1, I2, L1, L2, CM1, CM2, S1, S2

LOG_IO = STR(P.C2, 10),

' +/-' = SUBSTRING('- +', SIGN(P.C2-P.C1)+2,1)+LTRIM(STR(P.C2 - P.C1, 6)),

'%Chg' = STR(CASE WHEN P.C1<>0 THEN (1.0*(P.C2-P.C1)/P.C1) ELSE 0 END * 100, 6, 1),

PHYS_IO = STR(P.I2, 10),

' +/-' = SUBSTRING('- +', SIGN(P.I2-P.I1)+2,1)+LTRIM(STR(P.I2 - P.I1,6)),

'%Chg' = STR(CASE WHEN P.I1<>0 THEN (1.0*(P.I2-P.I1)/P.I1) ELSE 0 END * 100, 6, 1),

' +/-' = SUBSTRING('- +', SIGN(L.L2-L.L1)+2,1)+LTRIM(STR(L.L2 - L.L1,6)),

'%Chg' = STR(CASE WHEN L.L1<>0 THEN (1.0*(L.L2-L.L1)/L.L1) ELSE 0 END * 100, 6, 1),

BLK = STR(P.B2, 4),

BLKCNT = B.BLKING,

COMMAND = P.CM2,

STATUS = LEFT(P.S2, 10),

DB = DB_NAME(P.DBID)

FROM (

SELECT spid, loginame=MAX(loginame), C1 = SUM(C1), C2 = SUM(C2), I1 = SUM(I1), I2 = SUM(I2), CM1 = MAX(CM1), CM2 = MAX(CM2),

S1 = MAX(S1), S2 = MAX(S2), B2 = MAX(B2), dbid = MAX(DBID), hostname = MAX(HOSTNAME)

FROM #PRCS

GROUP BY spid

) P, 

(SELECT spid, L1 = SUM(L1), L2 = SUM(L2)

FROM #LCKS

GROUP BY spid) L,

#BLK B

WHERE P.spid = L.spid

AND P.spid = B.spid

AND (@loginame <> 'ACTIVEONLY'

OR @loginame IS NULL

OR L.L2 <> L.L1

OR P.C2 <> P.C1

OR P.I2 <> P.I1

OR P.CM1 <> P.CM2

OR P.S1 <> P.S2)

--보고서 하단을 출력한다.

PRINT CHAR(13) + 'TOTAL PROCESSES : ' + CAST(@@ROWCOUNT AS varchar) + CHAR(13) + ' (A - ACTIVE, I - INACTIVE, * - THIS PROCESS.) '

--작업 테이블을 삭제

DROP TABLE #LCKS

DROP TABLE #PRCS

DROP TABLE #BLK

RETURN 0