[스크랩] 저장프로시저 결과에 대해 정렬/where 조건 넣기 (sp_who2, sp_lock)

http://istore.tistory.com/71

DBA] sp_lock 와 sp_who2 결과에 오더링 및 필터링 설정해보기

■ sp_lock 와 sp_who2 의 필요

 DB 의 상태를 확인할때 sp_lock 과 sp_who2 를 매우 유용합니다. 일정 규모 이상의 시스템에서 여러명의 사용자가 있을 경우 데이터 베이스 사용에서 충돌이 발생하는 경우가 많이 있습니다. 어느 유저나 어느 사용자가 어떤 사유로 인해서 데이터 베이스를 점유하고 있는지를 쉽게 파악할 수있습니다. 

 sybase, MSSQL, MYSQL 등에 관계 없이 일반적으로 사용되는 명령어라서 개발자나 DBA 모두에게 꼭 필요한 정보입니다. 

■ sp_lock 와 sp_who2 의 활용 

 가장 쉽게 sp_who2 나 sp_lock 을 실행하면 기본적인 사용현황이 나옵니다. 물론 기본 내역을 스크롤 해가면서 현재 상황을 파악할 수 있습니다.  하지만, sql query 에 익숙한 개발자나 DBA 는 'SELECT / FROM / WHERE' 조건을 통해서 좀 더 결과 데이터를 가공해서 보기를 원합니다. 

*  sp_lock 실행 방법 : 'sp_lock' 혹은 'EXEC sp_lock'

 그러면 sp_lock 과 sp_who2 의 오더링(ordering) 및 필터링(filtering) 방법에 대해서 알아보겠습니다. 

△ sp_who2 실행 결과 

.

                 

■ sp_who2 의 결과를 테이블로 생성

 

sp_who2 는 물리 테이블이 아니기 때문에 직접 SELECT 나 WHERE 조건이 적용되지 않습니다. 결과를 임시 테이블 형태로 넣어서 해당 결과에 필터링과 오더링을 적용하는 방법 것입니다. 

CREATE TABLE #sp_who2_table  (SPID INT,Status VARCHAR(255),       Login  VARCHAR(255),HostName  VARCHAR(255),        BlkBy  VARCHAR(255),DBName  VARCHAR(255),        Command VARCHAR(255),CPUTime INT,        DiskIO INT,LastBatch VARCHAR(255),        ProgramName VARCHAR(255),SPID2 INT,        REQUESTID INT)  INSERT INTO #sp_who2_table  EXEC sp_who2 SELECT      *  FROM        #sp_who2_table   -- Add any filtering of the results here : WHERE       DBName <> 'master' -- Add any sorting of the results here : ORDER BY    HostName    ASC DROP TABLE #sp_who2_table

■ sp_lock 의 결과를 테이블로 생성

 

 sp_lock 의 경우도 sp_who2 와 동일합니다. 결과 스키마를 그대로 임시 테이블을 만들어서 해당 결과에 가공해서 보여줍니다.

 

 테이블 생성 스크립트가 길지만 울트리에디터 , notepad ++ 나 메모장 같은 곳에 따로 저장해 놓고 필요할때마다 파일을 열어 활용하시면 좋을 것입니다. 

CREATE TABLE #sp_lock (SPID INT,       dbid  VARCHAR(255),Objld  VARCHAR(255),        Injld  VARCHAR(255),Type  VARCHAR(255),        Resource VARCHAR(255),Mode VARCHAR(255),        Status2 VARCHAR(255) )  INSERT INTO #sp_lock EXEC sp_lock SELECT      *  FROM        #sp_lock -- Add any filtering of the results here : WHERE       Mode <> 'S' -- Add any sorting of the results here : ORDER BY    SPID ASC    DROP TABLE #sp_lock

   

■ sp_lock 결과에 대한 상세 spec 

 

 sp_lock 의 결과에 대한 해석을 하는 것도 쉽지 않습니다. 사실 많은 경험이 필요하고, 시스템의 특징에 따라 나타나는 케이스들이 다르게 때문에 초반에는 많은 노력이 필요합니다.  아래 설명들을 참고하시길 바랍니다. 

■ sp_?? 에 대한 확대 적용 가능성

 

 Oracle,  MYSQL, MSSQL 등에는 다양한 시스템 관리자 명령어 들이 있습니다. 위에서 활용한 동일한 방식으로 다른 명령어에도 결과를 오더링 및 필터링 하도록 적용할 수 있습니다. 내가 쓰는 명령어에 적용할 수 있도록 찾아 보십시요.

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

http://egloos.zum.com/dialup/v/753380

SQLServer를 사용하면서 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

-----------------------------------------------------------------------------
sp_what.sql