중소기업을 위한 SQL Server 기본 모니터링 - 3

출처 : dbguide.net 에 올려진 SSA 성대중 책임 컨설턴트 님의 글

해결! DB D&A>> 중소기업을 위한 SQL Server 기본 모니터링 - 3

악성 쿼리 진단

 

연재 순서

1회 – 시스템 진단(백업 및 시스템 현황, 디스크 현황)  /  2회 – 서버 점검 및 운영 진단  /  3회 – 쿼리 진단(악성 쿼리) 및 기타 논의 사항

 

4. 쿼리 진단

<전문가> 애플리케이션 성능의 경우 애플리케이션에서 데이터베이스로 호출할 때 문제가 있는데, 이 부분은 파워빌더로 개발하면서 오류가 발생한 것 같다. 이것은 직접 수정하기는 어려운 상황이니까, 쿼리 상에서 오류점이 있는지 살펴보고 수정을 하도록 하겠다.

<전문가> attention 되는 부분을 중심으로 살펴보면, 테이블 디자인이 잘못 되어 있는 것이 있다. tb_szd20 테이블의 경우, 모든 컬럼이 varchar(510)으로 설정되어 있다. 물론 실제 저장된 데이터는 이보다 작은 값으로 저장되어 있지만, 만약 전체 칼럼의 합이 8000Bytes 이상이 되면, 저장시 오류가 발생한다. 이 부분은 개발과정에서 담당자의 실수로 인한 것으로 판단되기 때문에, 적절하게 수정되어야 한다. 또한 인덱스를 생성하기 위해서도 컬럼의 길이를 조정해야 할 필요가 있다. PGM_ID 칼럼을 varchar(10)로 변경하고, 인덱스를 추가하면 성능에 도움이 될 것이다.

해결 쿼리 1>>

select PGM_ID, PGM_NM FROM comm.dbo.tb_szd20 WHERE pgm_id ='saa02'   증상) 테이블 스캔 발생   문제원인) PK 없음, 데이터모델링 오류(모든 칼럼이 VARCHAR(510)으로 되어 있음)   문제해결) PK 추가 alter table tb_szd20 add constraint PK_tb_szd20 PRIMARY KEY (PGM_ID)   칼럼 데이터형식 변경 alter table tb_szd20 alter column PGM_ID varchar(10) not null alter table tb_szd20 alter column PGM_NM varchar(50) not null

 

<전문가> 출하 업무 부분에서 출하 부분(수주 번호)만 인덱스를 가지고 있는데, 검색시 날짜 조건을 요구하다 보니 프로세싱이 원활하지 않다. 검색 조건을 지원하기 위한 인덱스를 추가해야 한다.

<신청자> 프로그램에서 검색 목록을 선택할 때, 어떤 목록을 선택하느냐에 따라 속도가 많이 차이 난다.

해결 쿼리 2>>

SELECT  v11.SAL_DT             AS            SAL_DT,                                                      v11.SAL_NO            AS            SAL_NO,                                                      v11.CUST_CD         AS            CUST_CD,                                                      … (중간생략)                                                      v11.REMARK           AS            REMARK FROM      TB_SAA20               v11,                                                      TB_SZA10                v12                    WHERE v11.SAL_DT             >=                               '20070501'                      AND   v11.SAL_DT             <=                               '20070521'                      AND   v11.CUST_CD         LIKE         RTRIM(LTRIM('')) + '%'                      AND   v11.CUST_CD         *=                               v12.CUST_CD ORDER BY               v11.SAL_NO DESC   증상) 테이블 스캔 발생   문제원인) 검색 조건에 지원하기 위한 인덱스 누락(SAL_NO 에만 인덱스 존재)   문제해결) 인덱스 추가 create index IX_SAL_DT ON TB_SAA20(SAL_DT,CUST_CD)

 

<전문가> 현재 시스템에서 가장 많은 I/O를 발생시키는 쿼리를 찾기 위해, 프로필러에서 Read 컬럼이 1000보다 큰 쿼리를 추적하였다. 그 중 가장 문제가 되는 쿼리를 보면, ITEM_NAME() 이라는 사용자정의 함수를 SELECT 절에서 반복 호출하고 있다. 또한 ITEM_NAME() 사용자정의함수의 내부 코드를 분석한 결과, 쿼리를 지원하기 위한 인덱스가 전혀 없는 상태이고, 테이블의 칼럼의 데이터형도 잘못 설정되어 있는 상태이다.

<신청자> 거래처 공통 코드 불러오는 부분이다.

<전문가> 잘못된 칼럼 길이를 수정해서라도, 인덱스를 만들어 주면 성능을 개선할 수 있다. TB_SZC10 테이블의 GRP_CD 칼럼과 KND_CD 칼럼에 인덱스를 생성해 주면 된다.

해결 쿼리 3>>

SELECT  v11.SAL_NO                              AS            SAL_NO,                   v11.SAL_SQ                              AS            SAL_SQ,                   v11.SAL_DT                              AS            SAL_DT,                   … (중간생략)                   SALE.DBO.ITEM_NAME(V11.ITEM_CD, '1')          AS            STY_NM,                   RTRIM(LTRIM(SALE.DBO.ITEM_NAME(V11.ITEM_CD, '2'))) + ' * ' +                   RTRIM(LTRIM(SALE.DBO.ITEM_NAME(V11.ITEM_CD, '3')))                 AS            ITEM_SIZE,                   SALE.DBO.ITEM_NAME(V11.ITEM_CD, '4')                            AS            COLO_NM,                   SALE.DBO.ITEM_NAME(V11.ITEM_CD, '5')                            AS            GRAD_NM,                   … (중간생략)                   v11.REMARK                             AS            REMARK   FROM  TB_SAA21               v11,                   TB_SZA10                v12                    WHERE v11.SAL_NO            =                                 '200705020003'                      AND   v11.CUST_CD         *=                               v12.CUST_CD   ORDER BY           v11.SAL_NO,                                                      SUBSTRING(v11.ITEM_CD, 1, 1) +                                                      SUBSTRING(v11.ITEM_CD, 12, 2) +                                                      SUBSTRING(v11.ITEM_CD, 2, 10)   증상) 과도한 IO 발생   문제원인) SELECT 절에 반복적으로 사용되는 dbo.ItemName() 사용자정의함수의 문제   문제원인) PK 없음, 데이터모델링 오류(모든 칼럼이 NVARCHAR(510)으로 되어 있음)   문제해결) 칼럼 데이터형식 변경 alter table TB_SZC10 alter column GRP_CD nvarchar(4) not null alter table TB_SZC10 alter column KND_CD nvarchar(20) not null PK 추가 alter table TB_SZC10 add constraint PK_TB_SZC10 PRIMARY KEY (grp_cd,knd_cd)   성능이 20배 이상 개선됨

개선사항>> 현재 데이터베이스에 있는 데이터 모델에 칼럼이 시스템 설계서와 다르게 구성되어 있는 부분도 많고, 인덱스 전략도 누락된 부분이 있는 것으로 판단된다. 또한 불필요한 사용자정의 함수의 반복호출로 인해 과도한 IO가 발생하고 있다. 이에 대해서는 좀 더 시간을 가지고 접근해야 하는 상황으로 보인다. 우선, 월말 결산 시점의 쿼리를 수집하여 추가적인 점검을 해야 하는 상황이라고 판단된다. 이를 위해 템플릿으로 5월말 결산시점의 쿼리를 수집하여 결과를 전달해 주기를 요청했다.

 

5. 기타 논의 사항

<전문가> 문제 상황을 진단하는 방법에 대해서는 일단 설명을 했지만, 문제 상황을 파악한 뒤 이를 직접 해결하는 것은 좀더 교육이 필요할 것 같다. 일단 비전문가이지만, 이 업무를 맡은 이상 기본적인 교육을 받고, 유지 관리를 하는 것이 바람직할 것 같다.

<신청자> 안 그래도 그 점이 고민이 된다. DBA가 없는 상황에서, 교육을 받는 것이 좋을지, 아웃소싱을 하는 것이 좋을지.

<전문가> 아웃소싱을 하더라도 회사 내에 DB에 대한 관리를 할 수 있는 주체는 필요하다. 아웃소싱으로 들어오는 인력들이 내부 담당자만큼 업무를 파악하거나 설계상의 문제까지 고려해서 관리해주긴 힘들다. 적어도 어떤 문제가 있고, 이것이 어떤 방법들로 해결될 수 있는지를 아웃소싱 담당자와 협의해서 결정할 수 있는 내부의 주체는 필요하다. 그러려면 아웃소싱을 하더라도 기본적인 교육을 통한 지식 획득이 필요하다고 본다.

<신청자> 다른 회사들은 주로 어떻게 관리하나. DBA가 있나?

<전문가> 몇몇 대기업을 제외하곤, DBA가 있는 경우가 드물다. 또 DBA가 있더라도 해당 업무 DB를 전담하는 경우도 별로 없다. 결국 전산 관리자가 DB 관리를 책임지는 경우가 대부분이라고 볼 수 있다.

<신청자> 어떻게 관리를 하면 좋을지. 평소에 관리할 시간은 부족하고, 그러다 문제가 발생하면 어디부터 손을 대야 할지 막막하다.

<전문가> 얘기한 두 사항이 사실 상충되는 부분이다. 평소에 관리를 잘 하면, 문제가 잘 안 생길 것이고, 또 문제가 생기더라도 어떤 부분이 문제인지 빨리 찾아 해결할 수 있다. 하지만 평상시에 방치하면, 당연히 문제 지점을 찾기가 어렵다. 지금까지 설명한 순서대로 적어도 월 2회(마감 전/후)는 사전 모니터링을 하면, 원인을 찾기 힘든 문제는 발생하지 않을 것이다.