MSSQL 너무 오래 걸리는데 DB Lock 일까?

[ 컴퓨터 장애처리 ]

장애처리

MS-SQL에서 쿼리를 열심히 작성해서 실행했는데,

한 시간 그리고 두 시간이 지나도 끝나지가 않는다.

 

이럴 때 문제를 확인하고 문제를 해결하는 방법은

명령어 4개이다. 

 

하나씩 알아보자

 

 

 

 

 

[ 저장 프러시저 ]

 

MS-SQL에는 관리자에게 기본빵으로 제공하는 프로그램들이 있다.

관리의 편리를 위해서 제공하는 도구들로 모두 저장 프로시저 만들어져 있다.

 

저장 프러시저는 영어로 Stored Procedure라고 하며

SQL 문법으로 만들어진 도구라고 보면 된다.

쓸만한 도구들의 이름은 sp_로 시작한다.

 

이런 명령어들이 무엇이 있는지 확인하기 위해서는

"sp_help"라고 쿼리 창에 넣어주면 된다. 

그럼 다양한 명령어들이 전부 확인할 수 있다.

 sp_help 명령어의 결과

 

 

 

 

 

[ 오래 걸리는 쿼리(Query) 확인 ]

현업에서 SQL을 사용하게 되면 세미콜론(;)을 사용해서

여러 개의 쿼리를 한꺼번에 사용하곤 하는데

이때 UPDATE나 INSERT에서 DB 락(Lock)이 발생하곤 한다.

때론 SELECT에서 오랜 시간 종료되지 않는 쿼리를 만날 수 도 있다.

 

이 상황을 타파하기 위해서는 3가지 명령어면 충분하다.

• sp_lock : 현재 DB에 Lock이 걸린 내역이 있는지 확인

• sp_who2 : 어떤 쿼리들이 동작하고 있는지 확인

• dbcc inputbuffer("SPID") : 현재 Lock 걸린 쿼리를 확인

• KILL "SPID" : Lock 걸린 쿼리를 종료

 

 

 

 

 

[ sp_lock ]

sp_lock 명령어는 저장 프러시저로 시스템에서 제공하는 명령어이다.

간단하게 이야기하자면, Mode에서 X라고 되어 있는 부분이 현재 Lock이다.

sp_lock 명령어의 결과

아래 표에서 각각의 칼럼 별 의미를 살펴보겠다.

컬럼명	타입	설명
spid	smallint	데이터베이스 엔진 세션 ID
dbid	smallint	SELECT DB_NAME("dbid") 함수를 사용하여 데이터베이스를 식별할 수 있는 ID
ObjId	int	SELECT OBJECT_NAME("Objid") 함수를 사용하여 쿼리에 영향받는 개체를 식별하는 ID
IndId	smallint	잠금이 설정된 인덱스의 ID
Type	nchar (4)	RID = RID(행 식별자)로 식별되는 테이블의 단일 행에 대한 잠금 PAG = 데이터 또는 인덱스 페이지에 대한 잠금 등등 있으나 RID, PAG를 가장 많이 접하게 된다.
Resource	nchar (32)	잠긴 리소스를 식별하는 값(4:2767505:3) PAG: fileid: pagenumber 형식의 숫자 => 4 fileid는 페이지가 포함된 파일을 식별하고 => 2767505 pagenumber는 페이지를 식별 => 3
Mode	nvarchar (8)	X = 배타. 보유 중인 세션이 리소스에 배타적으로 액세스 할 수 있도록 권한을 부여 IX = 의도 배타. 잠금 계층 구조의 일부 하위 리소스에 X 잠금을 설정하려는 의도를 표시 등등 많지만 위 두 개가 대표적이다. 내용이 어려우면 X가 락이다라고 알고 있으면 편하다.
Status	nvarchar (5)	GRANT: 잠금 CNVRT와 대기가 있지만 GRANT만 보면 된다.

 

 

 

 

 

[ sp_who2 ]

sp_who2 명령어는 누가 어떤 쿼리를 사용하고 있는지를 알 수 있다.

또한 쿼리가 현재 어떤 상태로 진행되고 있는지를 확인할 수 있다.

 

Login 컬럼에서 어떤 사용자가 사용 중인지가 입력되어 있다.

HostName 컬럼에는 그 사용자의 컴퓨터의 IP가 확인된다.

DBName 컬럼에서 문제가 되고 있는 쿼리가 동작 중인 DB를 알 수 있다.

Command 칼럼은 문제가 되는 쿼리의 명령어가 무엇인지 알려준다.

 

CPUTime 내역을 보면 문제가 되는 쿼리가 얼마나 지연시키고 있는지를 보여준다.

 

 

 

 

 

[ dbcc inputbuffer("SPID") & KILL "SPID" ]

dbcc inputbuffer("SPID") 명령어는 문제가 발생 중인 SQL을 확인할 수 있게 한다.

문제가 되는 SQL을 확인했으면, KILL "SPID" 명령어로 문제가 된 쿼리를 종료시킨다.

 

그 후 문제가 되는 쿼리를 수정한다.

문제의 대부분은 너무나 많은 양을 불러와서 조인하거나

전체 인텍스를 Search 하는 like 같은 조건문이 문제를 만든다.

 

문제가 발생할 소지가 있는 쿼리들은

부분 쿼리를 실행시키면서 동작 시각을 확인하고 추진하는 것이 현명하다.