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

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

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

SQL 서버 설정과 테이블 및 컬럼 점검

 

연재 순서

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

 

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 테이블과 같은 대용량 테이블에 대해서는 수동 통계 업데이트 정책도 적용할 필요가 있다.