[SQL Server] OSTRESS 유틸리티 사용법 및 예

/*****************************************************************************************
-- Title : [SQL Server] OSTRESS 유틸리티 사용법 및 예(2k5, 2k8)
-- Reference : cafe.naver.com/sqlist 현달프님 (회사에서 QCSS 설치로 고생해 주심^^;;)
-- Key word : rml utility ostress stress test
*****************************************************************************************/

1. 주의
   부하줄 sql문을 수집하고 그 수집한 쿼리들의 반복횟수의 비율 만큼 반복 수행한다.

   ㅇ 추적파일을 잡아서 그 추적 파일내의 반복 회수만큼을 적정한 비율 만큼 나눈다.
   ㅇ 매개변수의 랜덤화 : sql 문에 들어갈 변수들은 같은 값으로 들어가게 되면 정확한
                                   값을 예상할 수 없기때문에 랜덤하게 선택되도록 한다.
   ㅇ 네트워크 환경       : 수행을 할때 한 컴퓨터에서 많은 동시접속자 수를 만들면 컴퓨터
                                   자체의 네트워크 카드의 한계로 접속 자체가 거부 될 수 있.
                                   실제로 수행할 때 주변의 컴퓨터에서 같은 스크립트를 가지고
                                   접근을 해야 한다.

2. 다운로드
   ㅇ http://support.microsoft.com/
   ㅇ http://dbrang.tistory.com/295
   ㅇ SQL 2005까지만 해도 RML Utility만 깔면 됐는데, 2008 나온 이후로 ReportViewer를
       먼저 설치해 줘야 하더라굽쇼!!

3. 설치 확인
   ㅇ C:\Program Files\Rml                                          -- SQL2005까진 여기에
   ㅇ C:\Program Files\Microsoft Corporation\RMLUtils -- SQL2008에선 요기 생깁돠
   
4. 사용
   ㅇ Windows 인증
      -S : Servername
      -E : Windows 인증
      -d : 사용DB
      -Q : 쿼리문
      -n : 컨넥션 오픈수
      -r : 반복횟수
      ex> ostress -Smapbakdba -E -dadventureworks
                  -Q"select * from Person.Address" -n2 -r2

   ㅇ SQL 인증
      -U : SQL login ID
      -P : Password
      ex> ostress -Smapbakdba -Usa -P1234567 -dadventureworks
                  -Q"select * from Person.Address" -n1 -r1

   ㅇ 외부 파일 사용
      -i : 외부 스크립트 파일명
      ex> ostress -Smapbakdba -E -dadventureworks -ibatch.sql -n1 -r1

   ㅇ 타임 아웃
      -t : 타임 아웃 설정(초)
           이 것은 사용자가 쿼리를 취소하거나 쿼리 타임아웃으로 인해 SQL서버에
           고아가 된 트랜잭션이 남아 있는 경우의 시나리오 테스트를 하는데 유용.
      ex> ostress -Smapbakdba -E -dadventureworks -ibatch.sql -n10 -r5 -t1
 
   ㅇ 환경 설정 파일
      -c : 환경설정 파일명
           .\Samples\Samples\sample.ini 참조(samples.cab 푸삼)
      ex> CanclePct = 10.00으로 하면 수행횟수의 10%에 대해서 타임아웃 발생하도록 설정.
            ostress -Smapbakdba -E -dadventureworks -ibatch.sql -n10 -r5 -csample.ini

INI File Section	Value	Description
[Connection Options]	LoginTimeout	Login timeout (seconds).  Value of 0 (default) is infinite
	QuotedIdentifier	Issues a SET QUOTED_IDENTIFIER with the specified value (default is OFF)
	AutocommitMode	Sets the SQL_AUTOCOMMIT mode of ODBC.  Sets the IMPLICIT_TRANSACTIONS option to the opposite setting of what is here (default is on)
	DisconnectPct	Randomly disconnect from the server between iterations of the specified input file or query the specified percentage of time.  A value of 0.0 (default) is never.  If the input file contains a single command, or the input is an individual query, setting this option to 100%will simulate applications that use make-break connections.
	MaxThreadErrors	Maximum number of errors per connection before exiting.  Value of 0 (default) is infinite
[Query Options]	NoSQLBindCol	Set Off to disable calls to SQLBindCol for improved performance.  All rows are still fetched, but the values for the row aren’s copied into any application variables.  Use this option when you really don't care about the results returned from the query.  Defaults to On in quiet mode, Off otherwise.
	NoResultDisplay	Doesn't print results from the query. Implicitly set ON if NoSQLBindCol is also ON.  Defaults to ON for quiet mode, OFF otherwise
	PrepareExecute	Use ODBC prepared statements (default is Off)
	ExecuteAsync	Execute the query asynchronously, allowing for cancels.  Defaults setting is Off.  In replay mode, all queries are submitted asynchronously and this setting is ignored.
	RollbackOnCancel	Issues a rollback after each cancel operation. Defaults to Off
	QueryTimeout	Sets a query timeout for each query, in seconds.  Default is 0
	QueryDelay	Interjects a delay (milliseconds) before running each query.  Default is 0
	MaxRetries	Maximum retries for deadlocks & timeouts, with 0 retrying forever.  In stress mode, default value is zero.  In replay mode, this setting is ignored an no retries are done.
	CancelPct	Percentage of time to attempt query cancellation. Must have ExecuteAsync=On.  Default to zero
	CancelDelay	Delay (milliseconds) after issuing query before canceling.  If negative, a random delay between CancelDelayMin and CancelDelay is calculated and used.  Defaults to 0
	CancelDelayMin	Minimum delay (milliseconds) after issuing query before cancelling.  Should be 0 or a positive value
	CursorType	Request the following cursor type for any non-sp_cursor* queries.  Available options are forwardonly (default), keyset, dynamic and static.
	CursorConcurrency	Request the following cursor concurrency of any non-sp_cursor* queries.  Available options are readonly (default), lockcc, optcc and optccval.
	RowFetchDelay	Delay this amount of time (milliseconds) between each request to fetch rows (i.e. SQLFetch).  If negative, a random delay between zero and ABS(RowFetchDelay) will be calculated and used.  This can be used to simulate client apps that don’t process results, or don’t process them in a timely fashion.
	BatchDisconnectPct	After submitting the request to the server, close the connection for this percentage of batches.  A negative number will calculate a random value.
[Replay Options]	Sequencing Options	Comma separated list of options to use in replay mode.  Valid options are global sequence, delta, dtc replay.
	DTC Timeout	DTC transaction timeout (seconds).  Default is 60
	DTC Machine	The server running the MSDTC service to be used as the commit coordinator for DTC transactions.  Default is local server
	Playback Coordinator	For a multi-OSTRESS replay scenario, location where ORCA will run.  Default is local server.
	DefaultPassword	The password to use for any connection that is using SQL authentication but does not have a <PWD> tag in its <CONNECT> node.  If this key is missing a password of 뱑eplay?is used as a default. A user is assumed to be using Windows authentication if the username contains a backslash (\)character.  See the section 밅redentials used for Login?for more information related to this area.
	TimeoutFactor	OSTRESS automatically uses a query timeout to cancel queries that don뭪 run in approximately the same amount of time that they did when the trace was captured.  This is used to re-synchronize the replay in cases where something may have happened during capture (such as a deadlock) that didn뭪 happen at replay.  If you are using a slower machine or otherwise know that the queries may be much slower at replay, this can be used to weight how OSTRESS calculates the timeout.  The value is a float and initially defaults to 1.1, meaning that OSTRESS allows the queries to run approximately 110 percent of the time they did at capture.  A value of 2.0 would allow the queries to run twice as long, etc.  OSTRESS automatically increases this value (up to an upper limit of about 3) when it detects a lot of timeouts occurring during the replay.
	StartSeqNum	Specifies the sequence number of the lowest event number to submit during a replay attempt.  By specifying a StartSeqNum/StopSeqNum, a "window" of events from a larger set of trace files can be replayed.  Another alternative for limiting the scope of the replay is to use the start or end time parameters when running Read80Trace.  Default is zero.
	StopSeqNum	Specifies the highest sequence number to replay.  Default is largest 64 bit integer.