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

도스코 영업부의 송구현 주임은 비전산 담당자지만, 약 1년 전부터 사내 입출과 관리 프로그램의 관리를 맡고 있다. 최근 성능 저하 현상이 나타나는 애플리케이션에 어떤 문제가 있는지, 기본적으로 SQL Server를 유지 관리하는 방법이 무엇인지를 몰라 답답하던 상황이다. SSA(SQL Server Academy)의 성대중 책임 컨설턴트가 직접 안산 반월공단의 도스코를 방문해 기본적인 SQL Server 설정부터 쿼리 분석, 인덱스 추가에 이르는 방법론을 제시했다.

• 일시: 2007년 5월 25일
• 신청자: 도스코 영업부 송구현(song92) 주임
• 전문가: SSA 성대중 책임 컨설턴트

 

요청사항

1. 애플리케이션 성능 점검 - 프로그램 오류인지 DB 부하 문제인지 검토(DB 문제일 경우 권고안 제시)

2. 데이터베이스 유지관리 - DB 점검 및 관리 항목과 방법 제안 

<전문가> 영업부 주임이신데, 어떻게 SQL 서버 관리를 맡고 있나? 

<신청자> 원래 개발자가 있었는데, 담당자가 2006년에 사내 입출고 관리 프로그램을 개발한 후에 장기간 해외 근무를 하고 있다. 몇 가지 가이드라인이나 조언을 주긴 하는데, DBA나 전산 담당자 없이 영업부 소속의 비전문가가 관리하다 보니 어려움이 크다.

<전문가> 구체적으로는 주로 어떤 문제점이 발생했나?

<신청자> 사실 가장 큰 문제점은 지금 제대로 운영되고 있는지 조차 불안하다는 것이다. 이 업무 시스템이 월말 마감 외에는 오전에 1, 2시간만 주로 사용되는, 사실 그렇게 사용량이 많은 편이 아니다. 그런데도 HDD 용량을 미처 체크하지 못해서 다운된 적이 있었고, 또 백업을 잘 안 해놓은 상태여서 복구하는데 어려움이 있었다.

<전문가> 문제가 발생했을 때 이제까지는 주로 어디를 통해 정보를 얻었었나?

<신청자> 인터넷이다. 네이버카페를 주로 이용했고, 최근에 DBGuide.net을 알게 됐다. 하지만 이 정보들이 지금 우리 시스템 환경과 잘 맞지 않고, 비전문가이다 보니 적용 또는 응용하는 것도 쉽지 않다.

 

1. 시스템 진단

1-1. 서비스팩/패치 점검

<전문가> 우선 SQL Server 운영 상황에 대한 진단을 하고, 요청했던 애플리케이션 성능 점검과 데이터베이스 유지 관리 방안을 전달하는 것이 좋을 것 같다. 사전에 모니터링 툴을 전달해서, 그에 대한 결과 값을 받았으나 서비스팩과 누적핫픽스가 적용되지 않은 이전 버전(2000.80.194RTM)이어서 모니터링 툴이 제대로 값을 산출하지 못했다. 어차피 DB 관리의 기본 작업이 ‘진단’에 있기 때문에, 우선 진단 과정부터 함께 천천히 살펴보는 것이 좋겠다.

<신청자> 성능에 문제가 있어서 얼마 전에 윈도우 환경을 포맷한 뒤, 패치와 서비스팩 업데이트를 잊고 있었다. 안 그래도 어제 패치를 업데이트하고, 서비스팩도 SP4(2039) 버전으로 업그레이드했다.

개선 사항>> 최근 Rollup 패키지인 2187 버전의 HotFix를 추가로 설치해야 하는 상태이며, 아래의 주소를 참조해 다운로드 받아서 설치할 수 있다.

http://www.microsoft.com/downloads/details.aspx?displaylang=ko&FamilyID=9c9ab140-bdee-44df-b7a3-e6849297754a

--확인방법 select @@version --실행결과 Microsoft SQL Server 2000 - 8.00.2039(Intel X86)  May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack )

*<주의> 서비스팩의 적용 및 HotFix의 적용은 기존 애플리케이션의 동작에 영향을 미칠 수 있기 때문에 운영환경에 적용하기 전에 반드시 테스트 환경에서 테스트되어야 한다.

 

1-2. 백업 및 시스템 현황 점검

<전문가> 그럼 같이 상황 점검을 해보자. 백업은 어떻게 하고 있는지. 유지관리 메뉴에 보면, 전체 백업과 트랜잭션 로그 백업이라는 2가지 옵션이 있는데 어떻게 사용하고 있나.

<신청자> 테이블 백업을 텍스트로 하고 있다. 개발자가 원하는 사항이다. 그리고 정확한 주기는 설정한지 오래 되어서, 기억이 잘 안 나지만 전체 백업과 트랜잭션 로그 백업 모두 시행하고 있다.

<전문가> 그럼 복구 모델은 전체 복구 모델을 사용하고 있는 상태이고, 사용 환경은 하이퍼쓰레딩이 가능한 CPU 2개로, 논리적으로는 4개로 볼 수 있고, 메모리는 1GB. 여유공간이 몇백MB가 있는 것으로 봐서 메모리가 부족한 상태는 아니다.

개선사항>> 향후 시스템의 리소스가 부족하게 되면 메모리는 추가할 필요가 있는 상태라고 판단된다. 참고로, 현재 SQL Server 2000 Standard Edition을 사용하고 있기 때문에 SQL Server에 최대 2GB까지 메모리를 할당할 수 있다.

--확인방법 Exec master..xp_msver --실행결과 1               ProductName          NULL       Microsoft SQL Server 2               ProductVersion        524288    8.00.2039 3               Language                 1042        한국어 4               Platform  NULL       NT INTEL X86 5               Comments               NULL       NT INTEL X86 6               CompanyName      NULL       Microsoft Corporation 7               FileDescription        NULL       SQL Server Windows NT 8               FileVersion               NULL       2000.080.2039.00 9               InternalName          NULL       SQLSERVR 10             LegalCopyright       NULL       ⓒ 1988-2004 Microsoft Corp. All rights reserved. 11             LegalTrademarks   NULL       Microsoft?? is a registered trademark of Microsoft Corporation. 12             OriginalFilename    NULL       SQLSERVR.EXE 13             PrivateBuild             NULL       NULL 14             SpecialBuild             133627904              NULL 15             WindowsVersion     248381957              5.2 (3790) 16             ProcessorCount      4               4 17             ProcessorActiveMask              15             0000000f 18             ProcessorType        586           PROCESSOR_INTEL_PENTIUM 19             PhysicalMemory     1023        1023 (1073164288) 20             Product ID                NULL       NULL

 

1-3. 디스크 운영 환경 점검

<전문가> 그러면 하드디스크는 어떻게 사용하고 있는지.

<신청자> 디스크 서브시스템은 현재 RAID5로 구성된 C,D 드라이브와, 최근에 공간 부족 문제를 해결하기 위해 추가한 E,F 드라이브로 구성되어 있다. 현재 C 드라이브에 데이터베이스 파일 및 로그 파일이 저장되어 있으며, D 드라이브에는 스크립트 기반으로 테이블을 텍스트 파일로 내보내기 한 파일을 저장하고, E 드라이브에는 백업 파일을, F 드라이브에는 기타 관련 파일을 저장하고 있다

<전문가> 기본적으로 디스크는 OS를 위한 공간, 데이터베이스 파일을 위한 공간, 로그 파일을 위한 공간, 백업 파일을 위한 공간으로 분리해서 사용하는 것이 일반적인 권고사항이다. 가능하다면, 물리적으로 분리된 디스크 상에 위치하는 것이 바람직하며, 특히 데이터베이스 파일 및 로그 파일과 백업파일은 별도의 디스크에 위치시키는 것이 좋다. 왜냐하면, 디스크 장애발생시에 데이터베이스 관련 파일과 백업 파일이 같은 디스크상에 존재하면, 복구할 수 없기 때문이다. 현재로서는 사용량이 많지 않아서 괜찮을 수 있지만, 이후 점검 후에 디스크 용량 조정이 필요하다면 조치를 취해야 할 것 같다.

개선사항>> 현재로서는 사용량이 많지 않기 때문에, 특별한 조치가 필요한 상태는 아니다. 하지만 현재 시스템 볼륨(운영체제가 설치된 볼륨)인 C 드라이브에 데이터베이스 파일과 로그 파일이 함께 저장되어 있다. 이를 별도의 드라이브로 분산시키면 디스크의 병목현상을 해결할 수 있다. 또한 가능하다면, 데이터베이스 파일과 로그 파일도 분리하는 것이 바람직하다.

--확인방법 exec master..xp_fixeddrives --실행결과 C               20266 - Data/Log D               26685 E               17623 - Backup F               4570

2. SQL 서버 설정 점검

2-1. 데이터베이스 관리 기능 점검

<전문가> 애플리케이션 성능 이슈를 제기했는데, 스펙으로 봐선 시스템 상의 문제는 아닌 것 같다. DB D&A 신청서에는 미처 적지 못한, 성능상의 구체적인 문제점이 무엇이었나.

<신청자> 입출고 관리 프로그램에서 수행되는 테이블 목록 중에서 수불과 마감 프로세스가 느리다. 수불에서 데이터를 가져와서 마감을 하는 프로세스인데, 이때 속도가 많이 느려졌다. 또 테이블에서 데이터 수집할 때도 느렸었는데 이 부분은 하드디스크 증설과 윈도우 재설치 후에 좋아졌다.   

<전문가> 그럼 실제 SQL 서버에 접속해서 설정이 제대로 되어 있는지, 쿼리의 문제가 있는 부분이 있는지 같이 하나씩 살펴보자.

<신청자> 데이터베이스는 comm과 sale이 있으며, comm은 시스템의 기준정보를 저장하기 위한 목적으로 사용되고, 실제 트랜잭션 정보는 sale 데이터베이스에 저장되어 있다. Sale 데이터베이스는 약 500MB 크기이며, 전체 복구 모델을 사용하고 있다.

<전문가> 보니까 전체 DB 사이즈가 400MB(데이터베이스 파일 375MB, 로그파일 40MB)이고, 디스크 C에 저장하고 있다. 자동축소 기능을 쓰고 있는데, 이 기능은 해제해야 한다. 자동 축소 기능은, 인터벌 타임마다 용량을 체크해서 여유 공간이 나오면 자동으로 줄여 버린다. 이 상태에서 다시 데이터가 늘어나면 자동증가 하게 되고 다시 자동증가한 크기가 자동축소 임계값 기준으로 여유공간이 남게 되면 자동축소 되는 과정이 반복되는 문제 발생의 원인이 될 수 있다. 또한 자동축소나 자동증가가 일어나는 동안에는 해당 영역에 대해서는 잠금이 설정되기 때문에 I/O 병목현상의 원인이 될 수 있다. 실행 DB 환경에는 이 기능은 해제하고 사용하는 것이 좋다. 이 기능은 바로 해제하도록 하겠다.

<전문가> 그리고 계정은 sa 계정을 사용하고 있는데, 보안 때문에 별도 관리 계정을 만드는 것이 좋다. 파일 증가를 400MB로 제한하도록 설정했는데, 특별한 이유가 있는지.

<신청자> 특별한 이유가 있는 것은 아니고, 인터넷에서 기본 설정 가이드라인이 그렇게 되어 있는 것을 봤다.

<전문가> 데이터는 늘어나기 마련이기 때문에 파일 증가는 ‘무제한’으로 두는 것이 바람직하다. 현재 시스템환경으로 봤을 때, 데이터 파일의 경우 500MB로, 로그 파일은 200MB 정도로 늘려주는 것이 좋다. 단, 백업할 때 그 파일 크기를 기억하고 있다가 복원할 때, 디스크상에 백업할 당시 공간이 남아 있어야 복원이 가능하다는 것에는 유념해야 한다. 그리고 백업의 경우엔 데이터와 로그 백업을 F로 받아서, 데이터 파일이나 로그 원본 파일과는 분리해서 저장하는 게 좋을 것 같다.

<신청자> 인터넷으로 보기로는 로그 파일은 트랜잭션 백업을 하면 비워진다고 해서, 작게 잡았다.

<전문가> 작게 잡은 것이 문제가 되는 것은 아니다. 하지만 저장 공간이 많이 여유로운 상황이 아니기 때문에 좀더 탄력적으로 운영할 필요가 있다. 또 성능 개선을 위해 인덱스 최적화 등을 권고할 예정이어서 여유 공간이 좀더 필요하기도 하다. 실제 월말 마감 작업을 할 경우에는 그 전날 정도 로그 파일 백업 용량을 줄여줬다가 마감 후에 늘리는 식으로 탄력적으로 활용하면 도움이 될 것이다.  

개선사항 1>> sale 데이터베이스에 자동축소(IsAutoShrink) 옵션이 설정되어 있다. 자동축소가 설정되어 있으면, 주기적으로 데이터베이스의 여유공간을 체크하여 설정한 임계값보다 많은 여유공간이 남은 경우, 데이터베이스를 자동으로 축소하게 된다. 이는 데이터가 계속 증가하는 운영 데이터베이스에서는 권장되지 않는 옵션이기 때문에 설정 해제할 것을 권고한다.   

개선사항 2>> 계속 증가하는 데이터베이스이기 때문에 자동 증가가 업무시간에 발생하지 않도록 하기 위해 데이터베이스 파일 크기를 500MB, 로그파일의 크기를 200MB 정도로 충분히 늘려주는 것을 고려해야 한다.

개선사항 3>> 최대 파일 크기를 제한하는 것보다는 무제한으로 설정하고, 파일 크기를 주기적으로 모니터링하여 디스크 공간부족이 발생하는지 여부를 체크하는 것이 바람직하다.

--확인방법 exec sp_helpdb sale --실행결과 sale              404.88 MB        sa             7 04 16 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=Korean_Wansung_CI_AS, SQLSortOrder=0, IsAutoShrink, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics       80   1  C:\Data\MSSQL\Data\sale_Data.MDF    PRIMARY    375168KB     716800KB    102400KB     data only 2  C:\Data\MSSQL\Data\sale_Log.LDF    NULL     39424KB     410624KB     102400KB     log only

 

2-2. 파일 사이즈 점검

<전문가> 현재 파일 사이즈를 보면, 366MB가 총 할당된 데이터 파일 사이즈이고, 이 중 306MB가 실제 사용되고 있는 것을 확인할 수 있다.

<신청자> 그럼 지금 너무 많이 사용되고 있는 건가?

<전문가> 할당된 영역 중 90% 정도 사용되고 있는 상태니까, 여유 공간이 부족하다고 볼 수 있다. 이런 경우엔 100MB 자동 증가하게 되어 있으니까, 총 데이터 파일 사이즈를 500MB 정도까지 미리 늘려놓으면 충분할 것이다.

--확인방법 USE SALE GO DBCC SHOWFILESTATS GO --실행결과 Fileid        FileGroup                     TotalExtents         UsedExtents          Name            1               1                   5862                  4909                  sale              --계산방법(1 Extent = 8 Pages = 64 KB) select 5862*8*8/1024. as Reserved      , 4909*8*8/1024. as Used

, (4909*8*8/1024.)/(5862*8*8/1024.)*100.   Reserved                  Used         Usage(%) 366.375000              306.812500     83.7(%)

 

2-3. 테이블 및 컬럼 점검

<전문가> 사이즈에 대해선 이 정도 체크를 하고, 테이블을 살펴보도록 하자. 100MB가 넘는 테이블이 있는지를 검색해볼 텐데, 별로 없을 것 같긴 하다. 음, tb_sfa10(제품 수불) 테이블이 검색되는데, 보면 58만 6400건의 데이터가 있고 사이즈가 500MB나 된다. 기본적으로 테이블의 전체 데이터 중 20%가 변경되어야 자동통계업데이트가 되기 때문에, 테이블에 저장된 행수가 많아지면 성능에 문제가 될 수 있다. DBCC UPDATEUSAGE 명령을 사용하면, sysindexes에 저장되어 있는 정보를 최신상태로 업데이트해 준다. 업데이트 후 결과를 보면, 270MB로 낮춰진 것을 확인할 수 있다. 좀 전에 전체 데이터 사이즈가 400MB인데, tb_sfa10 테이블만 500MB로 조회되어서 깜짝 놀랐는데, 이제 정상화가 됐다.

 Tip>> 사용자 지정 메뉴에서 자주 쓰는 명령어를 11개까지 지정할 수 있다.

<전문가> 여기서 전체 컬럼을 보면 49개나 되고, 한 행의 크기(전체 컬럼 길이의 합)이 524byte나 된다.

<신청자> 그럼 데이터페이지 하나에 몇 행 저장하지 못 할텐데?

<전문가> 물론 한 행의 크기가 커지면 데이터베이스 하나에 몇 행 저장하지 못하기 때문에, 성능에 문제를 줄 수 있다. 특히 문자열 칼럼의 길이가 실제 필요한 데이터보다 과도하게 설정되어 있는 경우가 많은 것으로 보여진다. 이에 대해서는 성능의 문제가 된다면 수정할 필요가 있다. 현재 월 3만 건 정도의 신규 데이터가 존재하고 있고. 쿼리를 보면서 다시 살펴보겠지만, 쿼리를 지원하는데 필요한 인덱스가 좀 부족하지 않나 싶다. 이 부분은 조금 후에 실제 쿼리들을 살펴보면서 인덱스 추가기 필요한 부분은 수정하는 것이 좋을 것 같다.  

<전문가> 지금 사용되고 있는 현황을 살펴보면, 앗, 근데 아무도 접속이 안 되어 있네.

<신청자> 오전에 1, 2시간만 사용하고, 오후엔 거의 사용 안 한다.

<전문가> 흠, 그럼 프로필러를 돌려보자. 프로필러는 익숙하게 사용하나?

<신청자> 지난번에 문제가 있을 때 프로필러를 알게 됐고, 요즘엔 한번씩 돌려본다. 아직 기능을 많이는 모른다.

<전문가> 기본적인 작업의 50%, 문제를 찾아내는 것의 대부분이 프로필러를 이용해서 이뤄지기 때문에, 이 툴은 좀더 익숙해지도록 노력할 필요가 있을 것 같다. 한가지 Tip으로 얘기하면, 금번 작업을 위해, 제공된 템플릿을 사용하면 기본적인 모니터링을 수행할 수 있다. 앞으로 성능 문제가 있을 때도 이를 실행해서 템플릿을 잡으면, 어떤 부분이 문제가 있는지를 알 수 있다. 그리고 데이터베이스ID에 대해서 필터를 설정하는 것이 좋다. 1-4까지는 시스템 데이터베이스이기 때문에 5 이상으로 필터조건을 설정하는 것이 효율적일 것이다.

--확인방법 USE SALE GO -- 사전에 sysindexes에 있는 정보를 최신상태로 업데이트 DBCC UPDATEUSAGE(0) GO select object_name(id),name, rows,  rowcnt, rowmodctr, reserved, used, dpages,* from sysindexes where id > 100000 and name not like '_WA_Sys%' order by reserved desc   -- tb_sfa10 테이블 정보 테이블명   행수(행)      할당량(KB)   사용량(KB)   인덱스(KB)   잔량(KB) tb_sfa10     586,489      270,408        268,704       1,672           32   인덱스 tb_sfa10_pk     clustered, unique, primary key located on PRIMARY     CLS_DT,  ITEM_CD,   UNT_QTY   칼럼길이합 select sum(length) from syscolumns where id = object_id('tb_sfa10') 524 byte   데이터확인 select CLS_DT, count(*) from tb_sfa10 group by CLS_DT 월별로 평균 3만 건의 데이터 저장됨

 

3. 운영 진단

<전문가> 이제 유지관리와 관련해서 살펴보자. 백업이 어떻게 되고 있는지 로그를 잠깐 살펴보면, 전체 백업을 2시간마다 수행하고, 1주일간의 백업 데이터를 유지하고 있고, 로그 백업은 10분 단위로 하고 있고, 2주일간의 백업 데이터를 유지하고 있다.

<신청자> 인터넷에 보니까, 전체 백업을 1시간에 한번, 10분에 한번씩 한다는 사람들이 많았다. 그래서 이렇게 자주해야 할까 하면서도, 간격을 줄였다. 또 개발자가 요청해서, 각 테이블의 데이터를 텍스트 파일 형태로 내보내기 하고 있다.

<전문가> 지금 설정된 것은, 너무 빈번하게 백업을 하고 있는 것 같다 또한, 백업할 때마다 DBCC CHECKDB 명령이 자동 실행되도록 설정되어 있는 상태이다. 이는 사용자 쿼리에 비해, 유지보수 작업을 위한 DBCC CHECKDB 명령을 위한 작업 부하가 오히려 더 많은, 배보다 배꼽이 더 큰 상황이 된 것 같다.

<신청자> 처음엔 1시간에 1번씩 백업을 받았었다. 그러다 보니까 백업 용량이 디스크를 꽉 채워서, 그나마 2시간으로 늘린 것이다. 

<전문가> 불필요하게 자주 백업을 받고 있다. 전체백업을 1회/1일, 로그백업을 1회/1시간으로 조정해도 충분할 것이다. 백업과 인덱스 재생성 작업을 점심시간에 하는 것은 이유가 있는 것인가. 보통은 야간에 작업을 하는데.

<신청자> 야간엔 사용자가 없기 때문에, 서버를 중단(Shutdown)한다. 오전에만 잠깐 사용자가 몰리기 때문에, 보통 점심 시간에 사용자가 없어서 1시간 정도씩 진행을 한다.  

개선사항 1>> 불필요하게 자주 백업을 진행하고 있으며, 백업을 수행할 때마다 DBCC CHECKDB를 통해 시스템의 일관성 검사를 반복 수행하고 있어서, 현재 데이터베이스에 수행되는 쿼리 중 가장 많은 부하를 발생시키는 쿼리가 되고 있다. 이를 개선하기 위해 전체 백업은 1일 1회, 로그 백업은 1시간 간격으로 조정할 것을 권고한다.

개선사항 2>> 또한 DBCC CHECKDB를 백업시마다 수행할 필요는 없다고 판단돼, 데이터베이스 유지관리 마법사의 해당 옵션은 해제할 것을 권고한다. 또한, 현재 백업본의 관리기간이 서로 다르기 때문에 1주로 통일하면 백업본을 관리하기 위한 디스크 공간을 절약할 수 있다.

개선사항 3>> 인덱스 재생성 작업은 사용자의 접근을 제한하기 때문에, 향후 데이터베이스의 크기가 커지거나 사용자가 늘어나면 별도의 유지관리 시간을 야간 시간으로 조정하여 수행할 것을 권고한다. 또한 tb_sfa10 테이블과 같은 대용량 테이블에 대해서는 수동 통계 업데이트 정책도 적용할 필요가 있다.

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회(마감 전/후)는 사전 모니터링을 하면, 원인을 찾기 힘든 문제는 발생하지 않을 것이다.