메뉴 건너뛰기

Dev tips

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

taknim 2009.08.13 03:34 조회 수 : 10929 추천: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

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

번호 제목 글쓴이 날짜 조회 수
82 [MySQL] FIND_IN_SET 배열 형식의 값을 비교할 때 사용 taknim 2017.10.18 28
81 mysql split 함수 (split_str) taknim 2017.03.14 125
80 [MSSQL] DataBase 안에 있는 procedure에 텍스트 내용을 검색 프로시저 생성 taknim 2015.11.19 1372
79 sqlite3 테이블 정보 관련 쿼리 taknim 2014.03.22 3637
78 [MSSQL] mdf 파일로 DB 복원하기 taknim 2013.08.28 4651
77 [MySQL] 전체 테이블 mysqlcheck repair & optimize taknim 2013.06.11 5246
76 [oracle] isqlplus 에서 특수문자(&) 입력하는 방법 taknim 2012.08.10 16050
75 [오라클] 프로시저 확인하기 taknim 2011.10.04 8065
74 오라클9i 에서 MD5 암호화 taknim 2010.06.08 13038
73 [MSSQL] MS-SQL 동적쿼리 이용하기 taknim 2009.12.28 16962
72 [oracle] PLSQL에서 LONG RAW DATA DML 하기 taknim 2009.09.25 12330
71 [oracle] 실행계획 보기, 토드 설정법 explain plan taknim 2009.09.24 12154
70 [MySQL] 사용자 계정 관리하기 (추가/변경/삭제) taknim 2009.09.18 10036
69 [mysql] mysqlbinlog 실행시 캐릭터셋(utf8) 오류 taknim 2009.08.25 15753
68 [mysql] 동적 쿼리 생성시 변수를 대입하거나 값을 받아오기 taknim 2009.08.17 17168
» [MSSQL] sp_who2 를 대신할 sql 코드 file taknim 2009.08.13 10929
66 [MSSQL] 변수를 TABLE로 선언 후 데이터 입력 taknim 2009.07.30 11692
65 [ORACLE] Flashback (과거시점에서 질의실행) taknim 2009.07.06 10141
64 [Oracle] DBNEWID Utility - DBNAME,DBID변경하기 taknim 2009.07.06 15572
63 [Oracle] INSERT … ON DUPLICATE KEY UPDATE taknim 2009.06.25 13653