메뉴 건너뛰기

Dev tips

DataBase [MSSQL] sp_who2 를 대신할 sql 코드

taknim 2009.08.13 03:34 조회 수 : 10941 추천:2708

http://dialup.egloos.com/753380SQLServer를 사용하면서 sp_who2 시스템 procedure를 사용해 보지 않은 개발자는 거의 없을 것이다.
내가 보통 sp_who2나 sp_lock 시스템 procedure를 사용할 때는 database에 문제가 있어서 어떤 부분이
문제가 있는지를 확인하기 위해서 이거나, 간단한 테스트를 진행하며 process간의 관계를 설명할 때
현재의 상태를 보여주기 위해서 일때가 대부분이다. 그런데 쓰면 쓸수록 불편한 생각이 늘어나기 시작한다.


(이해가 되지 않는 sp_who2내의 코드)
-parameter명이 @loginame인데, 특정 login으로 접속한 것만 보기 위한게 아니다. 'active'라고 지정하면
status가 sleep이면서 명령이 'AWAITING COMMAND', 'LAZY WRITER' ,'CHECKPOINT SLEEP'인것중 block당하고
있는 것이 아닌 것만 return한다.
-한 번에 query가 가능한데 temptable에 data집어 넣고 난리다.
-indentation이 엉망이다. 말 그대로 별로 보고 싶지 않은 코드다.


뭐, 이때까지 잘쓰고 불평은... 이라고 누군가 따진다면 할 말 없다.


(불편한 점)
-내가 원하는 내용들만 보고 싶다구!!!*4
-내가 원하는 것부터 보고 싶다구!!!*2



(필요한 filtering 조건들)
-database명
-host명
-login명
-program명
-transaction이 open된 것만
-block되어 있는 것만


그래서 간단하게 아래의 procedure를 만들어 보았다.
사용 예)
1)database명이 mydb인 것만 보고 싶을 때
sp_what '', 'mydb', '', '', 1, 1, ''
or
sp_what @pi_strDBName = 'mydb'


2)transaction이 open된 것만 보고 싶을 때
sp_what '', '', '', '', 2, 1, ''
or
sp_what @pi_intTranOpened = 2

3)현재 block된 것만 보고 싶을 때
sp_what '', '', '', '', 1, 2, ''
or
sp_what @pi_intBlocked = 2


4)사용자 usera로 연결된 것 중 cpu사용 누계가 가장 높은 것 부터 보기
sp_what 'usera', '', '', '', 1, 1, 'C'
or
sp_what @pi_strLoginName = 'usera', @pi_intSortOption = 'C'


※아래 procedure를 생성 할 때 아래와 같이 'allow updates'를 1로 설정하고 생성하면 시스템 procedure로 등록된다.
sp_configure 'allow updates', 1
go
RECONFIGURE WITH OVERRIDE
GO


-----------------------------------------------------------------------------
use master
go


-----------------------------------------------------------------
-- Procedure Name  : SP_WHAT
-- Description     : check out the active processes of SQLServer
-- Inner SP        : NONE
-- Copyleft to everybody
-- Author          : dialup71@gmail.com, 2008-08-26
-- Modify History  : none
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_what]
@pi_strLoginName        NVARCHAR(12)=NULL,      --login name(equal comparison)
@pi_strDBName           NVARCHAR(128)=NULL,     --database name(equal comparison)
@pi_strHostName         NVARCHAR(256)=NULL,     --host name(equal comparison)
@pi_strProgramName      NVARCHAR(256)=NULL,     --program name(like comparison)
@pi_intTranOpened       TINYINT=1,              --whether the transation is opened or not(1:both, <>1:opened only)

@pi_intBlocked          TINYINT=1,              --whether the process is blocked or not(1:both, <>1:blocked only)
@pi_intSortOption       CHAR(1)='S'             --sorting order option('S':spid, 'L':login name, 'H':host name, 'D':database name, 'C':high cpu usage, 'I':high disk io)
AS


--declare variables
DECLARE @v_strSql       NVARCHAR(MAX)  --buffer for dynamic sql statement
DECLARE @v_strParams    NVARCHAR(MAX)  --buffer for parameters definition
DECLARE @v_strCRLF      NCHAR(2)       --crlf for good formatting the sql statement


--initialize variables.
SET @v_strSql = ''
SET @v_strParams = ''
SET @v_strCRLF = CHAR(13) + CHAR(10)


--make a main sql statement
SET @v_strSql = @v_strSql + 'SELECT spid as SPID ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,status as Status ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,open_tran as TranCount ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,loginame as Login ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,rtrim(isnull(hostName,''.'')) as HostName ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,isnull(blocked, 0) as BlkBy ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,isnull(case dbid when 0 then null else db_name(dbid) end,''NULL'') as DBName ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,cmd as Command ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,cpu as CPUTime ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,physical_io as DiskIO ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,convert(char(19), login_time, 120) as LoginTime ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,convert(char(19), last_batch, 120) as LastBatch ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,rtrim(program_name) as ProgramName ' + @v_strCRLF
SET @v_strSql = @v_strSql + '      ,request_id as REQUESTID ' + @v_strCRLF
SET @v_strSql = @v_strSql + 'from   master.dbo.sysprocesses with (nolock) ' + @v_strCRLF
SET @v_strSql = @v_strSql + 'where  1 = 1 ' + @v_strCRLF


--make a where clause
IF ISNULL(@pi_strLoginName,'') <> '' BEGIN
    SET @v_strSql = @v_strSql + 'and    rtrim(loginame) = @pi_strLoginName ' + @v_strCRLF
END


IF ISNULL(@pi_strDBName,'') <> '' BEGIN
    SET @v_strSql = @v_strSql + 'and    dbid = DB_ID(@pi_strDBName) ' + @v_strCRLF
END


IF ISNULL(@pi_strHostName,'') <> '' BEGIN
    SET @v_strSql = @v_strSql + 'and    rtrim(hostname) = @pi_strHostName ' + @v_strCRLF
END


IF ISNULL(@pi_strProgramName,'') <> '' BEGIN
    SET @v_strSql = @v_strSql + 'and    rtrim(program_name) like @pi_strProgramName + ''%'' ' + @v_strCRLF
END


IF @pi_intTranOpened <> 1 BEGIN
    SET @v_strSql = @v_strSql + 'and    open_tran > 0 ' + @v_strCRLF
END


IF @pi_intBlocked <> 1 BEGIN
    SET @v_strSql = @v_strSql + 'and    isnull(blocked, 0) <> 0 ' + @v_strCRLF
END


--make oder by clause
IF UPPER(@pi_intSortOption) = 'L' BEGIN
    SET @v_strSql = @v_strSql + 'order by loginame ' + @v_strCRLF
END
ELSE IF UPPER(@pi_intSortOption) = 'H' BEGIN
    SET @v_strSql = @v_strSql + 'order by hostname ' + @v_strCRLF
END
ELSE IF UPPER(@pi_intSortOption) = 'D' BEGIN
    SET @v_strSql = @v_strSql + 'order by db_name(dbid) ' + @v_strCRLF
END
ELSE IF UPPER(@pi_intSortOption) = 'C' BEGIN
    SET @v_strSql = @v_strSql + 'order by cpu desc ' + @v_strCRLF
END
ELSE IF UPPER(@pi_intSortOption) = 'I' BEGIN
    SET @v_strSql = @v_strSql + 'order by physical_io desc ' + @v_strCRLF
END


--make parameter definition
SET @v_strParams = '@pi_strLoginName NVARCHAR(12),
                    @pi_strDBName NVARCHAR(128),
                    @pi_strHostName NVARCHAR(256),
                    @pi_strProgramName NVARCHAR(256)'


--check out the whole sql statement
--print @v_strSql


--execute sql
EXEC SP_EXECUTESQL @v_strSql, @v_strParams, @pi_strLoginName, @pi_strDBName, @pi_strHostName, @pi_strProgramName


RETURN 0

-----------------------------------------------------------------------------

번호 제목 글쓴이 날짜 조회 수
333 PHP 에서 MySQL 바인딩 구현 taknim 2009.09.12 9141
332 file_get_content 사용하기 GET, POST taknim 2009.08.26 10651
331 [mysql] mysqlbinlog 실행시 캐릭터셋(utf8) 오류 taknim 2009.08.25 15765
330 커피 만드는 방법 taknim 2009.08.17 6534
329 [mysql] 동적 쿼리 생성시 변수를 대입하거나 값을 받아오기 taknim 2009.08.17 17198
» [MSSQL] sp_who2 를 대신할 sql 코드 file taknim 2009.08.13 10941
327 [HTML] euc-kr and CP949 file taknim 2009.08.13 11679
326 [PHP] 소켓 통신할때 패킷이 길어서 받지 못 할 때 해결 방법 taknim 2009.08.08 8636
325 vsftp 계정별로 폴더 제한, 권한 옵션 설명 taknim 2009.08.01 10817
324 ssh 특정 계정 제한하기 taknim 2009.08.01 11815
323 [MSSQL] 변수를 TABLE로 선언 후 데이터 입력 taknim 2009.07.30 11704
322 WOL 관련한 사이트 (매직패킷 전송) file taknim 2009.07.22 11766
321 [ORACLE] Flashback (과거시점에서 질의실행) taknim 2009.07.06 10154
320 [Oracle] DBNEWID Utility - DBNAME,DBID변경하기 taknim 2009.07.06 15576
319 리눅스 콘솔 단축키 taknim 2009.07.06 21943
318 bash 팁1 - 단축키, 환경변수 taknim 2009.07.06 12215
317 명령 프롬프트 창이 보이지 않는 배치 파일 실행하기 taknim 2009.07.04 12854
316 [Oracle] INSERT … ON DUPLICATE KEY UPDATE taknim 2009.06.25 13864
315 알아두면 유용한 윈도우의 환경 변수들 taknim 2009.06.25 8716
314 ext3grep – ext3 복구를 쉽게 taknim 2009.06.20 8366