• http://sqler.pe.kr/web_board/view_list.asp?id=1068&read=2992&pagec=&found=is&part=myboard7&ser=yes
  http://blogs.msdn.com/b/psssql/archive/2013/10/29/cumulative-update-2-to-the-rml-utilities-for-microsoft-sql-server-released.aspx
• RML 툴을 다운로드 한다.

-- #######################################################
-- ReadTrace 를 위한 프로시져 만들기
-- #######################################################
use master
go

if object_id ('usp_Profiler_Baseline_Start') is not null
drop proc usp_Profiler_Baseline_Start
go

create   PROC dbo.usp_Profiler_Baseline_Start
 @DateTime datetime   -- 기한
, @maxfilesize bigint   -- 최대 용량
, @folder nvarchar(245)
, @time datetime    -- 추적시작시각
AS
 -- 반드시 필요한 항목을 수정 적용할 것, 파일이 존재하면 오류가 발생(오류: 12)
 -- Create a Queue
 declare @rc int
 declare @TraceID int
 
 IF @DateTime IS NULL
  SET @DateTime = getdate() + 1  -- 디폴트 내일
 IF @maxfilesize IS NULL
  SET @maxfilesize = 5  -- 디폴트: 5 MB

 -- 추가 내용, 파일명에 .trc 가 자동으로 붙는다
 declare @file nvarchar(245)
 set @file = @folder
       + 'ProfilerBaseline'
        + replace(@@SERVERNAME, '\', '$') 
        + convert(char(8),getdate(),112)

 -- 중요. 특정 시각까지 대기 후 시작
 WAITFOR TIME @time
 PRINT '추적 시작'

 -- rollover 안함: 0

 exec @rc = sp_trace_create @TraceID output, 2, @file, @maxfilesize, @Datetime

 if (@rc != 0) goto error
 
 -- Client side File and Table cannot be scripted
 
 -- Set the events
 declare @on bit
 set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 15, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 13, 17, @on
exec sp_trace_setevent @TraceID, 13, 18, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 15, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 16, 1, @on
exec sp_trace_setevent @TraceID, 16, 3, @on
exec sp_trace_setevent @TraceID, 16, 8, @on
exec sp_trace_setevent @TraceID, 16, 10, @on
exec sp_trace_setevent @TraceID, 16, 11, @on
exec sp_trace_setevent @TraceID, 16, 12, @on
exec sp_trace_setevent @TraceID, 16, 13, @on
exec sp_trace_setevent @TraceID, 16, 14, @on
exec sp_trace_setevent @TraceID, 16, 16, @on
exec sp_trace_setevent @TraceID, 16, 17, @on
exec sp_trace_setevent @TraceID, 16, 18, @on
exec sp_trace_setevent @TraceID, 21, 1, @on
exec sp_trace_setevent @TraceID, 21, 3, @on
exec sp_trace_setevent @TraceID, 21, 8, @on
exec sp_trace_setevent @TraceID, 21, 10, @on
exec sp_trace_setevent @TraceID, 21, 11, @on
exec sp_trace_setevent @TraceID, 21, 12, @on
exec sp_trace_setevent @TraceID, 21, 13, @on
exec sp_trace_setevent @TraceID, 21, 14, @on
exec sp_trace_setevent @TraceID, 21, 16, @on
exec sp_trace_setevent @TraceID, 21, 17, @on
exec sp_trace_setevent @TraceID, 21, 18, @on
exec sp_trace_setevent @TraceID, 22, 1, @on
exec sp_trace_setevent @TraceID, 22, 3, @on
exec sp_trace_setevent @TraceID, 22, 8, @on
exec sp_trace_setevent @TraceID, 22, 10, @on
exec sp_trace_setevent @TraceID, 22, 11, @on
exec sp_trace_setevent @TraceID, 22, 12, @on
exec sp_trace_setevent @TraceID, 22, 13, @on
exec sp_trace_setevent @TraceID, 22, 14, @on
exec sp_trace_setevent @TraceID, 22, 16, @on
exec sp_trace_setevent @TraceID, 22, 17, @on
exec sp_trace_setevent @TraceID, 22, 18, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 3, @on
exec sp_trace_setevent @TraceID, 25, 8, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 16, @on
exec sp_trace_setevent @TraceID, 25, 17, @on
exec sp_trace_setevent @TraceID, 25, 18, @on
exec sp_trace_setevent @TraceID, 33, 1, @on
exec sp_trace_setevent @TraceID, 33, 3, @on
exec sp_trace_setevent @TraceID, 33, 8, @on
exec sp_trace_setevent @TraceID, 33, 10, @on
exec sp_trace_setevent @TraceID, 33, 11, @on
exec sp_trace_setevent @TraceID, 33, 12, @on
exec sp_trace_setevent @TraceID, 33, 13, @on
exec sp_trace_setevent @TraceID, 33, 14, @on
exec sp_trace_setevent @TraceID, 33, 16, @on
exec sp_trace_setevent @TraceID, 33, 17, @on
exec sp_trace_setevent @TraceID, 33, 18, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 3, @on
exec sp_trace_setevent @TraceID, 37, 8, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 13, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 16, @on
exec sp_trace_setevent @TraceID, 37, 17, @on
exec sp_trace_setevent @TraceID, 37, 18, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 13, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 15, @on
exec sp_trace_setevent @TraceID, 40, 16, @on
exec sp_trace_setevent @TraceID, 40, 17, @on
exec sp_trace_setevent @TraceID, 40, 18, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 13, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 15, @on
exec sp_trace_setevent @TraceID, 42, 16, @on
exec sp_trace_setevent @TraceID, 42, 17, @on
exec sp_trace_setevent @TraceID, 42, 18, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 16, @on
exec sp_trace_setevent @TraceID, 43, 17, @on
exec sp_trace_setevent @TraceID, 43, 18, @on
exec sp_trace_setevent @TraceID, 53, 1, @on
exec sp_trace_setevent @TraceID, 53, 3, @on
exec sp_trace_setevent @TraceID, 53, 8, @on
exec sp_trace_setevent @TraceID, 53, 10, @on
exec sp_trace_setevent @TraceID, 53, 11, @on
exec sp_trace_setevent @TraceID, 53, 12, @on
exec sp_trace_setevent @TraceID, 53, 13, @on
exec sp_trace_setevent @TraceID, 53, 14, @on
exec sp_trace_setevent @TraceID, 53, 16, @on
exec sp_trace_setevent @TraceID, 53, 17, @on
exec sp_trace_setevent @TraceID, 53, 18, @on
exec sp_trace_setevent @TraceID, 55, 1, @on
exec sp_trace_setevent @TraceID, 55, 3, @on
exec sp_trace_setevent @TraceID, 55, 8, @on
exec sp_trace_setevent @TraceID, 55, 10, @on
exec sp_trace_setevent @TraceID, 55, 11, @on
exec sp_trace_setevent @TraceID, 55, 12, @on
exec sp_trace_setevent @TraceID, 55, 13, @on
exec sp_trace_setevent @TraceID, 55, 14, @on
exec sp_trace_setevent @TraceID, 55, 16, @on
exec sp_trace_setevent @TraceID, 55, 17, @on
exec sp_trace_setevent @TraceID, 55, 18, @on
exec sp_trace_setevent @TraceID, 61, 1, @on
exec sp_trace_setevent @TraceID, 61, 3, @on
exec sp_trace_setevent @TraceID, 61, 8, @on
exec sp_trace_setevent @TraceID, 61, 10, @on
exec sp_trace_setevent @TraceID, 61, 11, @on
exec sp_trace_setevent @TraceID, 61, 12, @on
exec sp_trace_setevent @TraceID, 61, 13, @on
exec sp_trace_setevent @TraceID, 61, 14, @on
exec sp_trace_setevent @TraceID, 61, 16, @on
exec sp_trace_setevent @TraceID, 61, 17, @on
exec sp_trace_setevent @TraceID, 61, 18, @on
exec sp_trace_setevent @TraceID, 67, 1, @on
exec sp_trace_setevent @TraceID, 67, 3, @on
exec sp_trace_setevent @TraceID, 67, 8, @on
exec sp_trace_setevent @TraceID, 67, 10, @on
exec sp_trace_setevent @TraceID, 67, 11, @on
exec sp_trace_setevent @TraceID, 67, 12, @on
exec sp_trace_setevent @TraceID, 67, 13, @on
exec sp_trace_setevent @TraceID, 67, 14, @on
exec sp_trace_setevent @TraceID, 67, 16, @on
exec sp_trace_setevent @TraceID, 67, 17, @on
exec sp_trace_setevent @TraceID, 67, 18, @on
exec sp_trace_setevent @TraceID, 68, 1, @on
exec sp_trace_setevent @TraceID, 68, 3, @on
exec sp_trace_setevent @TraceID, 68, 8, @on
exec sp_trace_setevent @TraceID, 68, 10, @on
exec sp_trace_setevent @TraceID, 68, 11, @on
exec sp_trace_setevent @TraceID, 68, 12, @on
exec sp_trace_setevent @TraceID, 68, 13, @on
exec sp_trace_setevent @TraceID, 68, 14, @on
exec sp_trace_setevent @TraceID, 68, 15, @on
exec sp_trace_setevent @TraceID, 68, 16, @on
exec sp_trace_setevent @TraceID, 68, 17, @on
exec sp_trace_setevent @TraceID, 68, 18, @on
exec sp_trace_setevent @TraceID, 69, 1, @on
exec sp_trace_setevent @TraceID, 69, 3, @on
exec sp_trace_setevent @TraceID, 69, 8, @on
exec sp_trace_setevent @TraceID, 69, 10, @on
exec sp_trace_setevent @TraceID, 69, 11, @on
exec sp_trace_setevent @TraceID, 69, 12, @on
exec sp_trace_setevent @TraceID, 69, 13, @on
exec sp_trace_setevent @TraceID, 69, 14, @on
exec sp_trace_setevent @TraceID, 69, 16, @on
exec sp_trace_setevent @TraceID, 69, 17, @on
exec sp_trace_setevent @TraceID, 69, 18, @on
exec sp_trace_setevent @TraceID, 71, 1, @on
exec sp_trace_setevent @TraceID, 71, 3, @on
exec sp_trace_setevent @TraceID, 71, 8, @on
exec sp_trace_setevent @TraceID, 71, 10, @on
exec sp_trace_setevent @TraceID, 71, 11, @on
exec sp_trace_setevent @TraceID, 71, 12, @on
exec sp_trace_setevent @TraceID, 71, 13, @on
exec sp_trace_setevent @TraceID, 71, 14, @on
exec sp_trace_setevent @TraceID, 71, 16, @on
exec sp_trace_setevent @TraceID, 71, 17, @on
exec sp_trace_setevent @TraceID, 71, 18, @on
exec sp_trace_setevent @TraceID, 72, 1, @on
exec sp_trace_setevent @TraceID, 72, 3, @on
exec sp_trace_setevent @TraceID, 72, 8, @on
exec sp_trace_setevent @TraceID, 72, 10, @on
exec sp_trace_setevent @TraceID, 72, 11, @on
exec sp_trace_setevent @TraceID, 72, 12, @on
exec sp_trace_setevent @TraceID, 72, 13, @on
exec sp_trace_setevent @TraceID, 72, 14, @on
exec sp_trace_setevent @TraceID, 72, 16, @on
exec sp_trace_setevent @TraceID, 72, 17, @on
exec sp_trace_setevent @TraceID, 72, 18, @on
exec sp_trace_setevent @TraceID, 74, 1, @on
exec sp_trace_setevent @TraceID, 74, 3, @on
exec sp_trace_setevent @TraceID, 74, 8, @on
exec sp_trace_setevent @TraceID, 74, 10, @on
exec sp_trace_setevent @TraceID, 74, 11, @on
exec sp_trace_setevent @TraceID, 74, 12, @on
exec sp_trace_setevent @TraceID, 74, 13, @on
exec sp_trace_setevent @TraceID, 74, 14, @on
exec sp_trace_setevent @TraceID, 74, 16, @on
exec sp_trace_setevent @TraceID, 74, 17, @on
exec sp_trace_setevent @TraceID, 74, 18, @on
exec sp_trace_setevent @TraceID, 76, 1, @on
exec sp_trace_setevent @TraceID, 76, 3, @on
exec sp_trace_setevent @TraceID, 76, 8, @on
exec sp_trace_setevent @TraceID, 76, 10, @on
exec sp_trace_setevent @TraceID, 76, 11, @on
exec sp_trace_setevent @TraceID, 76, 12, @on
exec sp_trace_setevent @TraceID, 76, 13, @on
exec sp_trace_setevent @TraceID, 76, 14, @on
exec sp_trace_setevent @TraceID, 76, 16, @on
exec sp_trace_setevent @TraceID, 76, 17, @on
exec sp_trace_setevent @TraceID, 76, 18, @on
exec sp_trace_setevent @TraceID, 79, 1, @on
exec sp_trace_setevent @TraceID, 79, 3, @on
exec sp_trace_setevent @TraceID, 79, 8, @on
exec sp_trace_setevent @TraceID, 79, 10, @on
exec sp_trace_setevent @TraceID, 79, 11, @on
exec sp_trace_setevent @TraceID, 79, 12, @on
exec sp_trace_setevent @TraceID, 79, 13, @on
exec sp_trace_setevent @TraceID, 79, 14, @on
exec sp_trace_setevent @TraceID, 79, 16, @on
exec sp_trace_setevent @TraceID, 79, 17, @on
exec sp_trace_setevent @TraceID, 79, 18, @on
exec sp_trace_setevent @TraceID, 80, 1, @on
exec sp_trace_setevent @TraceID, 80, 3, @on
exec sp_trace_setevent @TraceID, 80, 8, @on
exec sp_trace_setevent @TraceID, 80, 10, @on
exec sp_trace_setevent @TraceID, 80, 11, @on
exec sp_trace_setevent @TraceID, 80, 12, @on
exec sp_trace_setevent @TraceID, 80, 13, @on
exec sp_trace_setevent @TraceID, 80, 14, @on
exec sp_trace_setevent @TraceID, 80, 16, @on
exec sp_trace_setevent @TraceID, 80, 17, @on
exec sp_trace_setevent @TraceID, 80, 18, @on
 
 
-- Set the Filters
 declare @intfilter int
 declare @bigintfilter bigint
-- 필터 조건
 exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL 프로필러'
 exec sp_trace_setfilter @TraceID, 10, 0, 7, N'MS SQLEM'

--  exec sp_trace_setfilter @TraceID, 3, 0,0,5  -- 특정데이터 베이스만 잡을때

 -- Set the trace status to start
 exec sp_trace_setstatus @TraceID, 1
 
 -- display trace id for future references
 select '추적번호: ', TraceID=@TraceID
 goto finish
 
 error:
 select '오류:', ErrorCode=@rc
 
 finish:
go

RAID 구성

데이타 파일 : RAID 0(패리티 없는 디스크 스트라이핑은 읽기 /쓰기 성능 좋음) + 1(디스크 미러링)
   디스크 볼륨 4장 필요

OS System, 로그 파일 : RAID 1
   디스크 볼륨 2장 필요
RAID 0(디스크 스트라이핑)
   두개의 볼륨에다가 나눠서 쓰기 한다. 쓰기 속도가 빠르다.

RAID 1(디스크 미러링)
   모든 데이타는 양쪽 드라이브에 모두 쓰여져야 한다, 그래서 쓰기는 느리지만, 읽기 성능은 빠르다. 왜냐하면  데이터 검색이 양쪽 디스크로 나눠 지기 때문이다. 
    OS System이용(드라이브를 완벽하게 이중화하기 때문)
    트랜잭션 로그는 순차적으로 쓰기 작업을 하고 롤백을 하는 경우만 읽기 동작을 하므로 트랜잭션 로그를 담는 드라이브 역시 권장

RAID 5(패리티 있는 디스크 스트라이핑)
   다중 디스크 스트라이핑과 쓰여지는 데이터의 패리티 정보를 저장함으로써 내결합성을 제공

• SQL 2005 에디션

• 메모리 사용 형태

  • 대 부분 권장 사항 최소 용량에 2배 정도되는 메모리 요구
  • SQL 2005와 기본적으로 시작되는 Windows 서비스 모두 약 256MB의 메모리 사용 
  • Analysis Service, Reporting Services, Notification Services와 같은 추가적인 데이타베이스 기능을 사용하게 되면 기본 메모리는 더 높이 올라가게 된다.  
  • Management Studio 을 사용하기 위해서 약 50MB ~ 60MB 추가 요구 
  • 하나의 사용자 연결을 위해서는 24KB의 메모리 소모  

이 글은 스프링노트에서 작성되었습니다.

이 글은 스프링노트에서 작성되었습니다.