MSSQL 기본튜닝

분류명 튜닝 구분 튜닝 – 1

작성자 김현종 날짜 2005-06-20
제목 튜닝 방법 1
간략 내용 제가 사용하는 튜닝 방법에 대해서 정리를 해봤습니다.
프로그램 튜닝 입니다.


DB에서 튜닝을 해야 할 항목은 여러 가지 입니다.
그 중에서 가장 많이 하고 자주하게 되는 DB 프로그램에 대한 튜닝을 하는 부분입니다.
튜닝을 하는 방법 자체 보다는 튜닝을 하기 위해서 문제가 되는 스크립트를 찾는 방법에 대해서
정리를 해봤습니다.
 스크립트로 이벤트 로그 파일 생성
 파일로 생성된 이벤트 로그 파일을 DB Insert
 Insert 된 내용 분석

1. 같이 첨부한 파일에 보시면 UP_StartTrace.sql 와 UP_StopTrace.sql 파일이 같이 들어 있을 것입니다.
이 두 개의 파일을 DB server에서 실행을 하도록 합니다.
그러면 두 개의 SP가 설치 될 것입니다.

두 개의 파일의 내용은 SQL 프로필러에서 보고 저장 할 수 있는 내용을 스크립트 형태로 만들어 실행 할 수 있게 해주는 스크립트입니다.
제가 남기는 스크립트 이외에 스크립트는 이벤트를 찾아보시면 원하시는 내용을 스크립트 파일로 남기 실 수 있을 것입니다.
그리고 실제로 프로필러를 띄우는 것보다는 시스템의 부하가 적게 든다고 합니다.




2. SP를 생성했다면 이제 이벤트 로그가 기본적으로 생성될 위치를 지정합니다.
[실행]
ALTER PROCEDURE up_StartTrace (@TraceFileName NVARCHAR(245) = N'C:\WTT\Trace',
@MaxFileSize BIGINT = 200,
@Duration BIGINT = NULL,
@Reads BIGINT = NULL,
@CPU BIGINT = NULL,
@TextData NVARCHAR(245) = NULL,
@ApplicationName NVARCHAR(245) = NULL
)
붉은 색으로 된 부분이 기본적으로 이벤트 로그가 저장될 위치 입니다.
이 위치를 각자 편리한 위치에 저장 하시면 될 것 같습니다.
전 기본적으로 c:\wtt\trace에 저장 하도록 하겠습니다.

3. 기본 위치 지정까지 끝났다면 이제 UP_StartTrace를 실행합니다.
SP를 실행하면 TraceID가 나오게 되는데 이것을 기억해 두시기 바랍니다.
UP_StopTrace 시 TraceID를 입력해야 하니까여
UP_StartTrace를 계속 실행 시키면 여러 개의 SP가 실행되게 되고 Trace 파일 또한 여러 개 생성하게 됩니다.
아무튼 여러 개를 실행할 수 있기 때문에 어느 SP를 정지할지를 알아야 하기 때문에 TraceID를 넣도록 되어 있으니 기억을 하시기 바랍니다.


4. 이제 UP_StartTrace를 실행 하시고 UP_StopTrace를 사용하여 종료를 하시면
2번에서 지정한 디렉토리 밑에 file이 생긴걸 보실 겁니다.
Trace.trc 파일을 이제 DB에 넣도록 하겠습니다.
[실행]
select *
into pubs.dbo.trace_table_1
from ::fn_trace_gettable(‘c:\wtt\trace\trace.trc’,default)
Trace.trc 파일을 pubs.dbo.trace_table_1의 Table로 저장을 하도록 하겠습니다.
만약 용량이 여러 커서 파일이 여러개 생성이 되셨다면 각각을 이런 방법으로 DB table에 저장을 하셔야합니다. (문의를 해본 결과 방법이 없다고 합니다. 혹시 방법을 아시는 분은 연락을 좀 주시길..)
그리고 실 SERVER 보다는 테스트 SERVER에 같은 장비에 INSERT 하시기 바랍니다.

5. table로 저장을 한 다음 저장된 내용을 분석을 해야 하는데
제가 하는 분석방법은 duration, read, cpu 등의 값을 이용해서 분석을 합니다.
일단 분석이 되려면 입력된 데이터를 결과를 확인 하도록 해야 하니
textdata column의 TYPE을 TEXT에서 VARCHAR(2000) 자리 정도로 변화를 시키고
GROUP BY를 이용하여 조건을 정렬 하도록 합니다.
[실행]
select left(textdata,50), count(*) as ‘실행수’,
avg(duration) '평균 실행', max(duration) '최대 실행' , min(duration) '최소 실행'
from trace_table_1
group by left(textdata,50)
having max(duration) > 2000
order by count(*) desc
이런 방식으로 데이터를 정렬 합니다.
TEXTDATA 를 50자리로 잘라내서 평균 duration과 max duration, min duration에 대해서 조사를 합니다.
이런 방식으로 read와 cpu 등도 하도록 합니다.

6. 이렇게 정렬된 데이터를 가지고 하는데 일단 기준이 있어야 할 것입니다.
A. 평균 duration이 높으면서 실행수가 많은 것
i. 이런 부분만 개선을 하더라도 DB 시스템이 훨씬 좋아지신걸 느끼실수 있을 것입니다.
B. 최소 duration과 최대 duration이 차이가 많이 나는 것
i. 갑자기 문제가 생긴 부분입니다. 이런 부분은 튜닝으로 해결도 좋지만 문제의
원인 자체를 찾아 내도록 합니다.
C. 이런 식으로 해서 having 절아 duration을 줄여 가도록 합니다.
2초, 1초 이런식으로여


지금까지 제가 프로그램을 튜닝 하는 방법에 대해서 간략하게 정리를 해봤습니다.
이렇게 하시는 분도 있고 다른 방법을 이용하시는 분도 있겠지만
이런 방법을 사용하시지 않는 분이라면
한번 이용해 보시기 바랍니다.