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