강의 컨설팅 트레이닝 무료진단 무료책자 마케팅편지 마케팅정보공유 다이어리 서비스제휴 고객센터

MSSQL Server DBA 가이드-5
작성자 : M 최고의하루
등록날짜 : 2008.12.26 10:50
1,796

성능 모니터링

성능 모니터링 하기

1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.

guide_img12.gif

2. 상단 그래픽 메뉴에서 [+]를 클릭하여 카운터 추가화면이 나타나면, 원하는 모니터 카운터를 추가합니다.

3. [로컬 컴퓨터 카운터 사용] 또는 [다른 컴퓨터에서 카운터 선택]을 선택합니다.

4. [성능개체]를 선택합니다.

5. [모든 카운터]를 선택하거나, [다음 목록에서 카운터 선택]를 선택한 후, 원하는 카운터를 선택합니다.

6. [모든 인스턴스]를 선택하거나, [다음 목록에서 인스턴스 선택]을 선택한 후, 원하는 인스턴스를 선택합니다.

7. [추가]를 클릭합니다.

8. 카운터 추가를 완료 한 후, [닫기]를 클릭합니다.

guide_img13.gif

9. 선택한 카운터가 하단에 나타납니다.

10. 카운터를 제거할 경우에는, 제거하기를 원하는 카운터를 선택한 후, 상단 그래픽 메뉴에서, [x] (삭제키)를 클릭합니다.


성능 로그 생성하기

[따라하기]

1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.

2. [성능 로그 및 경고]의 더하기 기호(+)를 클릭합니다.

3. 카운터 로그를 선택합니다.

4. 오른쪽 창에 마우스를 대고 오른쪽 버튼을 클릭하여, [새 로그 설정]을 선택합니다.

guide_img14.gif

5. [새 로그 설정]에 원하는 로그 이름을 입력하고, [확인]을 클릭합니다.

guide_img15.gif

6. Counter_Log화면에 사용 정보와 파일 포맷을 설정합니다.

guide_img16.gif

7. [개체추가]를 선택하여, 원하는 개체를 추가합니다.

8. [카운터추가]를 선택하여, 원하는 카운터를 추가합니다.

9. [데이터 샘플 간격]의 [간격]과 [단위]를 선택합니다.

10. [로그파일]탭을 선택합니다.

11. [로그 파일 종류]를 선택합니다.

12. 구성을 클릭하면, 로그 파일 구성 화면이 나타납니다.

guide_img17.gif

13. 구성을 클릭하면, 로그 파일 구성 화면이 나타납니다.

guide_img18.gif

14. [찾아보기]를 클릭하여 로그 파일을 저장할 위치를 선택합니다.

15. 파일이름을 입력합니다.

16. 로그 파일 크기를 선택합니다. [다음으로 제한]할 경우, 제한 파일 크기를 입력합니다.

17. [확인]을 클릭합니다.

18. 파일명의 마지막 부분을 어떻게 설정할 것인지를 선택합니다.

19. 파일명의 마지막 부분을 일련 번호로 할 경우, 시작번호를 설정합니다.

20. 로그 파일의 설명을 입력합니다.

21. [일정]탭을 선택합니다.

guide_img19.gif

22. 로그 시작 시간을 설정합니다.

23. 로그 중지 시간을 설정합니다.

24. 로그 파일을 닫을 때 실행할 명령이 있다면 선택합니다.

25. [확인]을 클릭합니다.

26. 오른쪽 창에 추가한 로그 파일의 목록이 나타납니다.

27. 새로 추가한 성능 로그의 이름위에서 마우스의 오른쪽 버튼을 클릭한 후 [다른 이름으로 설정 저장]으로 설정을 저장해 놓으면, 설정 파일을 재사용할 수 있습니다.

[참고]
  1. 성능 카운터에서 서버의 많은 정보를 얻을 수 있습니다. 문제를 확인할 수 있도록, 충분한 시간 동안 필요한 카운터를 수집합니다.
  2. 로그 파일 종류를 csv로 선택하여 수집하면, 분석 또는 집계하기에 편리합니다.
  3. 수집 시간을 고려하여 데이터 샘플 간격을 설정합니다. 샘플 간격이 커질수록, 그래프의 정확도는 떨어지고, 샘플 간격이 작다면, 데이터의 크기가 커집니다.
    수집 시간 샘플 간격
    2시간 4 초
    1일 30 초
    5일 180 초
    1일 15초
  4. SQL Server를 모니터링하기 위하여 어떤 서버를 사용할지 결정합니다. 원격으로 모니터링할 수 있으나 장기간 동안 네트워크를 연결하여 카운터를 사용하는 것은 네트워크 트래픽을 가중시킵니다. 만일 SQL Server에 성능 모니터링 로그를 위한 공간이 있다면, 성능 로그 정보를 로컬로 기록합니다.
  5. 수집 파일 크기는 적정한 값으로 제한합니다. 수집 파일이 너무 커지면, 파일이 열리지 않는 경우가 있습니다.
  6. 기존의 설정 파일(HTM)이 있는 경우에는 [기존 설정에서 새 로그 설정]을 사용하면 쉽게 구성이 가능합니다.

성능 로그의 재생

[따라하기]

1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.

guide_img20.gif

2. [로그 데이터 보기] 버튼을 클릭하면, [시스템모니터 등록 정보] 창이 나타납니다.

guide_img21.gif

3. 로그 파일을 선택하고, 추가 버튼을 클릭하여, 원하는 파일을 추가합니다.

4. [시간 범위]를 클릭하여, 원하는 시간대를 조절합니다.

5. 데이터 탭을 클릭합니다.

guide_img22.gif

6. [추가]를 클릭하여, 원하는 개체를 추가합니다.

7. [확인]을 클릭합니다.



프로필러

성능 문제의 디버깅은 문제의 원인을 알아내는 것으로 시작합니다. 많은 경우, 성능 문제는 비효율적인 SQL 문에서 기인합니다. 비효율적인 SQL 문이 문제의 원인이라고 의심될 때, SQL 프로필러는 특정 SQL 문을 찾는 것에 도움을 줄 수 있습니다. 문제의 원인이 되는 SQL 문을 찾아, 튜닝하여 성능에 많은 도움을 줄 수 있습니다.

추적 수행하기 - GUI 사용

[따라하기]

1. 다음 방법 중 하나를 이용하여 프로필러를 실행합니다.
[시작] → [프로그램] → [Microsoft SQL Server] → [프로필러] 또는 엔터프라이즈 관리자의 상단 메뉴에서 [도구] → [SQL 프로필러]를 선택합니다.

2. [파일] → [새로 만들기] → [추적]을 선택합니다.

guide_img23.gif

3. 원하는 SQL 서버에 연결하면, [추적 속성]창이 나타납니다.

guide_img24.gif

4. 추적이름을 입력합니다.

5. 추적할 SQL Server를 선택합니다.

6. 템플렛을 사용할 경우에 템플렛 이름을 선택합니다.

7. 파일에 저장하려면, [파일에 저장]을 선택하고, 저장할 위치와 파일명을 입력합니다.

8. 최대 파일 크기 설정을 합니다.

9. 이벤트 탭을 선택한 후, 원하는 이벤트를 추가하거나, 제거합니다.

guide_img25.gif

10. 데이터 열 탭을 선택한 후, 수집할 데이터 열을 선택합니다.

guide_img26.gif

11. 필터를 이용하고 싶다면, 필터 탭을 선택하여, 원하는 필터를 정의합니다. 예를 들어, LoginName이 Test인 것만 수집하고 싶다면 다음과 같이 설정합니다.

guide_img27.gif

12. [실행]을 클릭하면, 수집이 시작됩니다.

guide_img28.gif

13. 수집 중지를 위해서, 중지 버튼(붉은 네모)을 클릭합니다.


추적 수행하기 - SP 사용

[파일] → [추적 스크립팅]을 이용하면, 원하는 확장 프로시저를 생성할 수 있습니다.
[추적 스크립팅]을 이용하여 작성한 저장 프로시저를 첨부합니다.

[따라하기]

  1. 오래 실행되는 SQL 문 찾기
    오래 실행되는 쿼리는 잘못 튜닝된 시스템, 잘못 작성된 응용 프로그램, 또는 단순히 많은 동작을 수행하는 작업등을 의미할 수 있습니다. 어떠한 경우건, 이러한 오래 실행되는 SQL 문을 찾아서 튜닝하는 것은 그 작업의 성능은 물론 전반적인 시스템 성능까지도 향상시킬 수 있습니다.
    권장되는 추적 이벤트 : TSQL, SQL:BatchCompleted
    정렬 기준 컬럼 : Duration
  2. 과도한 자원 사용자 찾기
    과도한 자원을 사용하는 응용 프로그램이나 사용자를 찾는 추적은 DBA에게 유용한 도구가 될 수 있습니다. 이러한 추적 유형은 CPU와 I/O 자원 모두를 많이 사용하는 SQL 문을 살펴야 합니다. 프로세스나 사용자를 식별하여, 응용 프로그램을 튜닝할 수 있습니다.
    권장되는 추적 이벤트 : TSQL, SQL:BatchCompleted
    정렬 기준 컬럼 : CPU, Reads, 및 Writes
  3. 교착 상태 알아내기
    사용자의 작업에 따라 교착상태는 시스템에서 문제가 될 수도 있고 또 그렇지 않을 수도 있습니다. 많은 경우에 있어 교착 상태는 심각한 문제일 수 있는데, 이 경우 원인을 알아내는 것은 성능을 향상시키는데 핵심이 됩니다. 그러나 이러한 이벤트를 프로파일 하는 것은 자원을 많이 사용하게 되므로 주의해야 합니다.
    권장되는 추적 이벤트
    TSQL, SQL:BatchStarting 동작하는 SQL 일괄 처리(batch)
    Locks, Lock:Deadlock 교착 상태 자체의 이벤트
    Locks, Lock:Deadlock Chain 교착 상태에 이르는 이벤트 순서

[참고]

  1. 불필요한 이벤트의 추가는 삼가합니다. 너무 많은 이벤트의 추가는 서버의 성능에 영향을 줄 수 있습니다.
  2. 일반적인 경우라면, 모든 컬럼들을 포함시킵니다. 어떤 이벤트들은 보조적인 항목을 반환하는 어떤 항목들에 의존합니다. 적어도 다음 컬럼들은 포함하는 것이 좋습니다.
    - BinaryData
    - ClientProcessID
    - CPU
    - Duration
    - EndTime
    - EventClass
    - EventSubClass
    - HostName
    - IntegerData
    - LoginName
    - NTUserName
    - Reads
    - SPID
    - StartTime
    - TextData
    - Writes
  3. 테이블에 추적을 직접 저장하는 것은 좋지 않습니다. 추적 파일을 생성한 후, fn_trace_gettable 함수를 사용하면 테이블에 저장할 수 있습니다.

추적에 관련된 저장 프로시저의 예제 스크립트

다음은 추적 스크립팅을 저장 프로시저화한 예제 스크립트입니다. 시스템의 환경에 적합하도록 수정 보완하여 활용하기 바랍니다.

추적을 시작하는 저장 프로시저의 예제 스크립트 : sp_trace_start
USE master
GO
CREATE PROCEDURE sp_trace_start @TraceFileName sysname=NULL,
@TraceName sysname='trace',
@Options int=2, -- TRACE_FILE_ROLLOVER
@MaxFileSize bigint=5,
@StopTime datetime=NULL,
@Events varchar(300)=
'10,12',
-- 10 - RPC:Completed
-- 12 - SQL:BatchCompleted
@Cols varchar(300)=
'1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,',
-- 모든 컬럼
@IncludeFilter sysname=NULL,
@ExcludeFilter sysname=NULL
AS
SET NOCOUNT ON
-- 변수 선언
DECLARE @TraceId int
DECLARE @On bit
DECLARE @rc int

SET @On=1

-- 이벤트와 컬럼을 확인한다.
IF @Events IS NULL or @Cols IS NULL BEGIN
PRINT 'No Events or Coloumns.'
RETURN -1
END

-- 파일경로와 파일명을 설정한다.
IF @TraceFileName IS NULL
SELECT @TraceFileName = 'c:\Trace\trace_' + CONVERT(CHAR(8),getdate(),112)

-- 추적 큐를 만든다
EXEC @rc =sp_trace_create @TraceId OUT, @Options, @TraceFileName, @MaxFileSize, @StopTime
IF @rc<>0 BEGIN
PRINT 'Trace not started.'
RETURN @rc
END
PRINT 'Trace started.'
PRINT 'The trace file name is '+@TraceFileName+'.'

-- 추적할 이벤트 클래스들과 컬럼들을 지정한다
DECLARE @i int, @j int, @Event int, @Col int, @Colstring varchar(300)

IF RIGHT(@Events,1)<>',' SET @Events=@Events+','
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @Colstring=@Cols
IF RIGHT(@Colstring,1)<>',' SET @Colstring=@Colstring+','
SET @j=CHARINDEX(',',@Colstring)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@Colstring,@j-1) AS int)
EXEC sp_trace_setevent @TraceId, @Event, @Col, @On
SET @Colstring=SUBSTRING(@Colstring,@j+1 ,300)
SET @j=CHARINDEX(',',@Colstring)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END

-- 필터를 설정한다
EXEC sp_trace_setfilter @TraceId, 10, 0, 7, N'SQL Profiler'
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, N'EXEC% sp_%trace%'

IF @IncludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 6, @IncludeFilter

IF @ExcludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, @ExcludeFilter

-- 추적을 활성화한다
EXEC sp_trace_setstatus @TraceId, 1

-- 추적을 기록한다. (테이블 사용)
IF OBJECT_ID('tempdb..TraceQueueList') IS NULL BEGIN
CREATE TABLE tempdb..TraceQueueList (TraceID int, TraceName varchar(20), TraceFile sysname)
END

IF EXISTS(SELECT * FROM tempdb..TraceQueueList WHERE TraceName = @TraceName) BEGIN
UPDATE tempdb..TraceQueueList
SET TraceID = @TraceId, TraceFile = @TraceFileName
WHERE TraceName = @TraceName
END
ELSE BEGIN
INSERT tempdb..TraceQueueList
VALUES(@TraceId, @TraceName, @TraceFileName)
END

RETURN 0
GO

/* 실행 하기 */
EXEC sp_trace_Start
[참고]
  • output 파일을 지정하지 않으면, "c:\Trace"밑에 추적 파일이 생성됩니다. 이 스크립트를 직접 실행하려면, "c:\Trace"를 생성합니다.
  • 추적 파일이 커질 수도 있으므로, output 파일이 생성되는 곳의 공간을 충분히 확보합니다.
  • 원하는 이벤트와 컬럼은 번호로 설정합니다. 이벤트와 컬럼 번호는 BOL을 참조하십시오.
추적을 중지하는 저장 프로시저의 예제 스크립트 : sp_trace_stop <PRE>USE masterGOCREATE PROCEDURE sp_trace_stop @TraceName sysname='trace'ASSET NOCOUNT ON-- 변수를 선언한다DECLARE @TraceId int DECLARE @TraceFileName sysname -- 추적 목록을 확인하여, 추적을 중지한다IF OBJECT_ID('tempdb..TraceQueueList') IS NOT NULL BEGIN SELECT @TraceId = TraceID, @TraceFileName=TraceFile FROM tempdb..TraceQueueList WHERE TraceName = @TraceName IF @@ROWCOUNT<>0 BEGIN EXEC sp_trace_setstatus @TraceId, 0 EXEC sp_trace_setstatus @TraceId, 2 DELETE tempdb..TraceQueueList WHERE TraceName = @TraceName PRINT 'Trace is stopped. ' + 'The trace output file name is '+@TraceFileName END ELSE PRINT 'No active traces.'ENDELSE PRINT 'No active traces.'RETURN 0GO/* 실행하기 */EXEC sp_trace_stop</PRE>

[참고] sp_trace_stop은 sp_trace_start로 실행한 추적(Trace)를 중지하는 저장 프로시저입니다.


추적 재생하기

1. 프로필러를 시작하고, [파일] → [열기] → [추적 파일]을 선택합니다.

guide_img29.gif

2. 원하는 파일을 선택합니다.

유용한 유틸리티

  • PSSDIAG

    PSSDIAG는 성능 분석에 필요한 여러가지 로그와 데이터를 수집할 수 있는 유틸리티입니다. 이 유틸리티를 사용하면 프로필러에 비해 부하를 덜 주면서 추적 데이터를 수집할 수 있으므로, 대용량 시스템의 경우에는 프로필러 대신 이 툴을 사용할 것을 권고합니다. 자세한 내용은 다음 url을 참조하십시오.
    http://support.microsoft.com/?kbid=830232  toUS.gif 

  • Read80Trace

    수집한 추적 정보를 분석하는데 매우 유용한 유틸리티입니다. 추적 데이터를 분석하여 로컬 데이터베이스에 분석한 정보를 저장해 주고, htm 파일 형태로 리소스를 많이 사용한 쿼리 또는 저장 프로시저, duration이 긴 쿼리 또는 저장 프로시저 등에 대한 분석 결과를 제공합니다. 자세한 내용은 다음을 참조하십시오.
    http://support.microsoft.com/default.aspx?scid=kb;en-us;887057  toUS.gif 



문제 점검 및 해결

사용자 데이터베이스가 suspect로 표시된 경우에 문제 해결하기

Northwind 데이터베이스의 status 컬럼이 suspect로 설정된 경우를 예를 들어 설명합니다.

[주의] sp_resetstatus SP는 아래와 같은 문제 해결을 위해서만 사용해야 하며, 사용 시 주의를 요합니다.

[참고] SQL Server Errorlog 파일에 "Bypassing recovery for database 'Northwind' because it is marked SUSPECT." 와 같은 메시지가 기록됩니다.

[따라하기]

1. sp_resetstatus가 없으면 생성합니다. <PRE>USE masterGOEXEC sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE GO CREATE PROCEDURE sp_resetstatus @dbname varchar(30) ASDECLARE @msg varchar(80)IF @@trancount > 0BEGIN PRINT 'Can''t run sp_resetstatus from within a transaction.' RETURN (1)ENDIF suser_id() != 1BEGIN SELECT @msg = 'You must be the System Administrator (SA)' SELECT @msg = @msg + ' to execute this procedure.' RETURN (1)ENDIF (SELECT COUNT(*) FROM master..sysdatabases WHERE name = @dbname) != 1BEGIN SELECT @msg = 'Database ' + @dbname + ' does not exist!' PRINT @msg RETURN (1)ENDIF (SELECT COUNT(*) FROM master..sysdatabases WHERE name = @dbname AND status & 256 = 256) != 1BEGIN PRINT 'sp_resetstatus can only be run on suspect databases.' RETURN (1)ENDBEGIN TRAN UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = @dbname IF @@error != 0 OR @@rowcount != 1 ROLLBACK TRAN ELSE BEGIN COMMIT TRAN SELECT @msg = 'Database ' + @dbname + ' status reset!' PRINT @msg PRINT '' PRINT 'WARNING: You must reboot SQL Server prior to ' PRINT ' accessing this database!' PRINT '' ENDGOEXEC sp_configure 'allow updates', 0RECONFIGURE WITH OVERRIDE GO</PRE>2. Suspect 상태가 된 데이터베이스에 대하여 sp_resetstatus를 실행합니다. <PRE>EXEC sp_resetstatus NorthwindGO</PRE>

3. ALTER DATABASE를 사용하여 Northwind 데이터베이스에 파일을 추가하여 여유 공간을 확보해 줍니다.

4. SQL Server를 중지하고 다시 시작합니다.

Tempdb가 suspect 상태가 된 경우의 문제 해결하기

Tempdb가 suspect 상태가 된 경우에 문제를 해결하는 방법을 설명합니다. 참고로 tempdb가 suspect 상태가 되면 SQL Server 서비스 시작이 실패할 수도 있습니다.

[참고] SQL Server Errorlog 파일에 "Database 'tempdb' cannot be opened. It has been marked SUSPECT by recovery." 와 같은 메시지가 기록됩니다.

[따라하기]

1. tempdb.mdf 파일과 tempdb.ldf 파일이 있는지 확인하고, 만약 있으면 파일들의 이름을 변경합니다.

2. 다음과 같은 명령어를 사용하여 명령 프롬프트 상에서 SQL Server를 시작합니다. 명명된 인스턴스인 경우에는 -s 매개변수를 지정합니다.

<PRE>sqlservr -c -f -T3608 -T4022</PRE>

[주의] 명령 프롬프트 창이 열린 채로 두어야 합니다. 명령 프롬프트 창을 닫으면 SQL Server 프로세스가 중지됩니다.

3. 쿼리 분석기에서 sp_resetstatus를 수행하여 tempdb의 suspect 상태를 해제합니다.

<PRE>EXEC master..sp_resetstatus Tempdb</PRE>

4. 명령 프롬프트 찾에서 키를 눌러 SQL Server 서비스를 중지합니다.

5. SQL Server 서비스를 시작합니다. 이렇게 작업하면 Tempdb 데이터베이스 파일들이 새로 생성되며 tempdb가 정상적으로 복구됩니다.

손상된 데이터베이스 복구하기 (DBCC CHECKDB를 사용하여 오류 복구하기)

DBCC CHECKDB 명령어를 사용하면 특정 데이터베이스의 일관성(consistency)를 점검할 수 있습니다. DBCC CHECKDB 명령어는 데이터베이스 손상을 점검하는 주요 수단이며, 다음과 같은 사항들을 점검합니다.

- 인덱스 페이지와 데이터 페이지들이 제대로 연결되어 있는가
- 인덱스가 최신 상태이고, 제대로 정렬되어 있는가
- 포인트들이 일관성이 있는가 (Consistent)
- 각 페이지 상의 데이터가 최신 상태인가
- 페이지 오프셋이 최신 상태인가

[구문] <PRE>DBCC CHECKDB ( 'database_name' [ , NOINDEX | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [ WITH { [ ALL_ERRORMSGS ] [ , [ NO_INFOMSGS ] ] [ , [ TABLOCK ] ] [ , [ ESTIMATEONLY ] ] [ , [ PHYSICAL_ONLY ] ] } ] </PRE>

[사전지식]
DBCC CHECKDB나 DBCC CHECKTABLE에 REPAIR 옵션을 지정하여 수행하고자 하는 경우에는 SQL Server를 단일 사용자 모드로 시작해서는 안되고 해당 데이터베이스를 단일 사용자 모드(single user mode)로 설정해야 합니다.

[참고] 데이터베이스를 단일 사용자 모드로 설정하는 방법 세 가지
  • 엔터프라이즈 관리자에서 설정하기
    1. 해당 데이터베이스에서 마우스의 오른쪽 버튼을 클릭한 다음에 [속성]을 선택합니다.
    2. 속성 창에서 [옵션] 탭을 선택합니다.
    3. "액세스 제한" checkbox를 선택한 다음에 "단일 사용자"를 선택하고 [확인] 버튼을 클릭합니다.
  • 쿼리 분석기에서 sp_dboption을 사용하여 설정하기 <PRE>USE masterGOEXEC sp_dboption db_name, single, trueGO</PRE>
  • 쿼리 분석기에서 ALTER DATABASE를 사용하여 설정하기 (작업단계)
    1. 지정한 시간이 경과한 후에 완료되지 않은 트랜잭션들을 롤백하고 단일 사용자 모드로 변경하고자 하는 경우
    2. 완료되지 않은 트랜잭션들을 즉시 롤백하고 단일 사용자 모드로 변경하고자 하는 경우
[참고] 복구 옵션에 대하여 이해하기
  • REPAIR_FAST 옵션 : 사소한 손상을 복구하는 작업을 수행하며 수행 시간되 빠르고 데이터 유실도 유발하지 않습니다.
  • REPAIR_REBUILD 옵션 : comprehensive error checking and correction 을 수행하며, 소요 시간이 길고 데이터 유실도 발생하지 않습니다.
  • REPAIR_ALLOW_DATA_LOSS 옵션 : REPAIR_REBUILD가 수행하는 모든 작업들을 동일하게 수행하며, 데이터 유실이 발생할 수 있는 작업을 추가로 수행합니다. 구조적인 문제와 페이지 오류를 정정하고 손상된 텍스트 오브젝트를 삭제하는 작업을 수행하기 때문에 데이터 유실이 발생할 수도 있습니다.

[참고] 복구 작업 단계

  1. 해당 데이터베이스를 단일 사용자 모드로 변경합니다.
  2. REPAIR 옵션을 지정하여 DBCC CHECKDB를 수행합니다.
    2-1. 먼저 REPAIR_FAST 나 REPAIR_REBUILD 옵션을 지정하여 문제 해결을 시도합니다.
    2-2. REPAIR_FAST 나 REPAIR_REBUILD 옵션으로 문제가 해결되지 않으면 REPAIR_ALLOW_DATA_LOSS 옵션을 사용합니다.
    [주의] REPAIR_ALLOW_DATA_LOSS 옵션을 사용하면 데이터의 유실이 발생할 수 있다는 점을 명심하기 바랍니다.
    [권고사항] REPAIR_ALLOW_DATA_LOSS 옵션을 사용하는 경우에는 명령어 수행 후에 다시 원래 상태로 복구할 수 있도록 하기 위해서 트랜잭션 내부에서 DBCC 명령어를 수행할 것을 권고합니다. 이와 같이 작업하면 복구 작업을 수행하고 결과를 확인한 다음에 필요한 경우에 롤백이 가능해집니다.
  3. 복구가 완료되면 데이터베이스를 백업합니다.
[따라하기] <PRE>SELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')GO/* 결과:MULTI_USER*/ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK AFTER 10 --10초 후에 완료되지 않은 트랜잭션들을 롤백GOSELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')GO/* 결과:SINGLE_USER*/DBCC CHECKDB ('Northwind', REPAIR_FAST)GOALTER DATABASE Northwind SET MULTI_USER GO</PRE>

손상된 테이블 복구하기 (DBCC CHECKTABLE을 사용하여 오류 복구하기)

개별 테이블의 문제를 복구하고자 하는 경우에는 DBCC CHECKTABLE 명령어를 사용하면 됩니다.

[구문] <PRE>DBCC CHECKTABLE( 'table_name' | 'view_name' [ , NOINDEX | index_id | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ] [ , [ TABLOCK ] ] [ , [ ESTIMATEONLY ] ] [ , [ PHYSICAL_ONLY ] ] } ] </PRE>[따라하기] <PRE>SELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')GO/* 결과:MULTI_USER*/ALTER DATABASE Northwind SET SINGLE_USER -- 10초 후에 완료되지 않은 트랜잭션들을 롤백WITH ROLLBACK AFTER 10 GOSELECT DATABASEPROPERTYEX ('Northwind', 'UserAccess')GO/* 결과:SINGLE_USER*/USE NorthwindGODBCC CHECKTABLE (Orders, REPAIR_FAST)GOALTER DATABASE Northwind SET MULTI_USER GO-- EXEC sp_dboption 'Northwind', 'single user', 'FALSE'-- GO</PRE>

단일 로그 파일로 구성된 데이터베이스의 새로운 로그 파일 생성하기

[주의] 로그 파일이 오직 하나인 데이터베이스에 대해서만 사용할 수 있습니다.

[따라하기] 단일 로그 파일을 가지는 'TestDB'의 로그 파일이 유실/손상된 경우에 새로운 로그 파일을 생성하는 방법

<PRE>EXEC sp_detach_db 'TestDB'GOEXEC sp_attach_single_file_db 'TestDB', 'E:\DBdata\TestDB_dat.mdf'GO</PRE>

DBCC REBUILD_LOG를 사용하여 새로운 로그 파일 생성하기

DBCC REBUILD_LOG는, 데이터베이스의 트랜잭션 로그 파일을 사용할 수 없는 경우에 새로운 로그를 재구축하는데 사용되는 명령어입니다.
예를 들어, 하드웨어의 장애로 인하여 로그 파일이 손상되거나 실수로 로그 파일을 삭제하여 기존의 로그 파일을 액세스할 수 없어서 데이터베이스를 사용할 수 없는 경우에 DBCC REBUILD_LOG를 사용하여 로그를 재구축할 수 있습니다. 로그 파일이 하나인 경우에는 먼저 위의 해결 방법을 적용해 본 다음에 실패하면 이 방법을 사용하기 바랍니다.
그러나, 이 명령어를 사용하면 로그에 반영되지 않은 유실된 트랜잭션들의 발생으로 데이터베이스의 일관성이 손상될 가능성이 매우 높다는 점을 유의해야 합니다. 문제가 발생하면 일단 다른 방법 (예를 들어, sp_attach_single_file_db)을 동원하여 문제 해결을 시도하고, 도저히 다른 방법으로는 데이터베이스를 복구할 수 없는 경우에 이 명령어를 사용하기 바랍니다. 이 명령어를 수행하면 로그에 반영되지 않는 트랜잭션의 발생으로 인하여 데이터 무결성이 손상될 가능성이 높기 때문입니다. 이 명령어는 문서로 제공되지 않는 명령어이며 마이크로소프트 기술 지원 서비스의 지원 하에서 사용하는 것이 원칙이지만, 기술 지원 서비스를 받을 수 없는 경우의 응급 복구 작업에 참고하시라고 알려 드립니다. 또한, 하드웨어 장애와 같은 문제가 발생한 경우에는 트랜잭션 로그 파일 뿐만 아니라 데이터까지 손상시켰을 가능성이 높으므로, DBCC REBUILD_LOG가 성공적으로 완료된 이후에 단일 사용자 모드에서 DBCC CHECKDB를 수행하여 데이터의 일관성 (Consistency) 을 확인하는 작업이 반드시 필요합니다.

<PRE>[구문] DBCC REBUILD_LOG('db_name','log_filename')</PRE>

db_name : 문제가 발생한 데이터베이스의 이름
log_filename : 새로운 로그 파일에 대한 완전한 물리적 경로

[주의] 이 방법을 사용하면 데이터 일관성이 손상될 가능성이 매우 높으므로 다른 방법으로는 도저히 데이터베이스를 복구할 수 없는 경우에 최후의 방법으로서 사용해야 하며, 매우 신중하게 작업해야 하며, 이 명령어는 문서로 제공되지 않는 명령어로서 마이크로소프트 제품 기술 지원 서비스의 지원 하에서 사용해야 합니다.

[오류 발생]
로그 파일이 유실 또는 손상된 경우에는 그 데이터베이스는 엔터프라이즈 관리자에 "주의 대상" (suspect)로 표시되며, 쿼리 분석기나 엔터프라이즈 관리자에서 주의
대상 상태가 된 데이터베이스를 액세스하려고 하면 다음과 같은 오류가 발생합니다

서버: 메시지 945, 수준 14, 상태 2, 줄 1
파일을 액세스할 수 없거나 메모리 또는 디스크 공간이 부족하여 'RebuildLogTest' 데이터베이스를 열 수 없습니다. 자세한 내용은 SQL Server 오류 로그를 참조하십시오.

그리고 SQL Server를 재시작하면 ERRORLOG 파일에 다음과 같은 오류 메시지가 기록됩니다.

2005-01-12 14:51:56.72 spid11 'RebuildLogTest' 데이터베이스를 시작하는 중입니다.
2005-01-12 14:51:57.24 spid11 장치 활성화 오류입니다. 물리적 파일 이름 'C:\Program Files\Microsoft SQL Server\MSSQL\data\RebuildLogTest_log.LDF'이(가) 잘못된 것 같습니다.

이와 같은 오류가 발생하고 데이터베이스에 연결할 수 없는 경우에 다음과 같은 작업 단계로 복구 작업을 수행하면 로그를 재구축할 수 있습니다.

[예제] 다음은 RebuildLogTest라는 데이터베이스를 복구하는 예제입니다.

  1. 설정된 옵션들을 확인합니다. <PRE>EXEC sp_dboption RebuildLogTestGO</PRE>
  2. 시스템 테이블에 대한 직접적인 업데이트가 가능하도록 변경합니다. <PRE>EXEC sp_configure 'allow updates', 1RECONFIGURE WITH OVERRIDEGO</PRE>
  3. 문제가 발생한 데이터베이스를 응급 모드(bypass recovery)로 설정합니다 <PRE>UPDATE master..sysdatabases SET status = 32768 WHERE name = 'RebuildLogTest'GO</PRE>
  4. SQL Server 서비스를 중지하고 다시 시작합니다.
  5. DBCC REBUILD_LOG를 수행합니다. 이 때 로그 파일의 이름에는 로그 파일이 저장될 경로까지 전체 이름을 기술해야 하며, 로그 파일은 기존에 존재하지 않는 이름을 지정해야 합니다.
    USE master
    GO
    DBCC REBUILD_LOG('rebuildlogtest','C:\Program Files\Microsoft SQL Server\MSSQL\data\RebuildLogTest_log.LDF')
    GO

    [참고]
    이 명령어가 정상적으로 수행되면 결과창에 다음과 같은 메시지가 반환되며, 데이터베이스는 'dbo use only' 모드가 됩니다. 이전의 status 값과 무관하게 sysdatabases 테이블의 status 값이 2048로 설정됩니다. sp_dboption이나 엔터프라이즈 관리자를 사용하여 status 값을 원하는 값으로 변경하면 됩니다.

    경고: 'RebuildLogTest' 데이터베이스에 대한 로그가 다시 작성되었습니다. 트랜잭션에 일관성이 없습니다. 물리적 일관성을 검사하려면 DBCC CHECKDB를 실행해야 합니다. 데이터베이스 옵션을 원래대로 설정하고 다른 로그 파일을 삭제해야 합니다.

    DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

    만약 이미 있는 파일 이름을 지정한 경우에는 다음과 같은 오류 메시지가 반환됩니다.

    서버: 메시지 5025, 수준 16, 상태 1, 줄 2
    'C:\Program Files\Microsoft SQL Server\MSSQL\data\RebuildLogTest_log.LDF' 파일이 이미 있습니다. 새 로그 파일을 만들려면 이 파일의 이름을 바꾸거나 삭제해야 합니다.

    DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

  6. 시스템 테이블을 직접 업데이트할 수 없도록 원래 값으로 변경합니다. <PRE>EXEC sp_configure 'allow updates', 0RECONFIGURE WITH OVERRIDEGO</PRE>
  7. 데이터베이스를 단일 사용자 모드로 변경하고 DBCC CHECKDB를 수행하여 일관성을 점검합니다. 데이터베이스를 단일 사용자 모드로 변경하는 보다 자세한 방법은 "손상된 데이터베이스 복구하기" 를 참조하기 바랍니다. <PRE>EXEC sp_dboption ' RebuildLogTest ', 'single user', 'true'DBCC CHECKDB('RebuildLogTest')GO</PRE>
  8. DBCC CHECKDB를 수행한 결과 문제가 없으면, 그 데이터베이스를 정상적으로 사용할 수 있습니다. 그러나 롤백되어야 할 트랜잭션이 롤백되지 않거나, 데이터에 반영되어야 할 수정 작업이 반영되지 않는 문제가 발생할 수 있으므로, 논리적인 데이터의 무결성은 별도의 점검이 필요합니다.
  9. 데이터베이스 옵션을 원래대로 설정하고, 로그 파일의 크기도 원래 크기로 확장합니다. 로그파일을 재구축하면, 504KB의 작은 크기의 로그 파일이 생성됩니다.

교착상태(Deadlock) 발생 시 교착상태 추적하기

추적 플래그 1204를 사용하면 교착상태(Deadlock)에 대한 내용을 확인하는 것이 가능합니다. 명령 프롬프트에서 추적 플래그를 추가하여 SQL Server 서비스를 시작할 수도 있고, 엔터프라이즈 관리자에서 SQL Server 시작 매개 변수에서 추적 플래그를 추가할 수도 있습니다.

명령 프롬프트에서 추적 플래그를 지정하는 방법

[따라하기]

  1. SQL Server 서비스를 중지해도 되는 시점에 SQL Server 서비스를 중지합니다.
  2. 다음과 같이 추적 플래그를 추가하고 SQL Server 서비스를 시작합니다.
    [주의] SQL Server 서비스를 시작한 명령 프롬프트의 창은 그대로 두어야 합니다. 명령프롬프트 창을 닫거나, 를 입력하면 SQL Server 서비스가 중지됩니다. [참고] sqlservr.exe 파일은 SQL Server 설치 폴더의 하위 폴더 중 하나인 binn에 있습니다.

    [예] 추적 플래그를 추가하여 디폴트 인스턴스 SQL Server 서비스를 시작하는 예제 (SQL Server 2000 서비스 팩3부터는 -T3605를 추가하지 않아도 ERRORLOG에 추적결과가 기록됩니다.) <PRE>sqlservr -c -T1204 -T3605</PRE>
  3. 위와 같이 작업하면 교착상태 추적 결과가 SQL Server 서비스가 시작된 콘솔 화면과 ERRORLOG 파일로 기록됩니다.

엔터프라이즈 관리자에서 추적 플래그를 지정하는 방법
  1. 엔터프라이즈 관리자에서 [속성] → [시작 매개 변수] "매개 변수"에 -T1204와 -T3605를 입력하고 [추가] 버튼을 클릭한 다음에 [확인] 버튼을 클릭합니다.
  2. SQL Server 서비스를 중지하고 재시작 합니다.
  3. 교착상태가 발생하면 교착상태 추적 결과가 ERRORLOG 파일로 기록됩니다.

[교착상태에 대한 추적결과 예]
추적 플래그 1204를 추가하고 SQL Server 서비스를 시작하면 교착상태에 대한 추적결과가 다음과 같은 형태로 반환 또는 기록됩니다.

2003-02-25 05:12:55.13 spid4
Deadlock encountered .... Printing deadlock information
2003-02-25 05:12:55.13 spid4
2003-02-25 05:12:55.13 spid4 Wait-for graph
2003-02-25 05:12:55.13 spid4
2003-02-25 05:12:55.13 spid4 Node:1
2003-02-25 05:12:55.14 spid4 RID: 2:1:15:0     CleanCnt:1 Mode: X Flags: 0x2
2003-02-25 05:12:55.14 spid4 Grant List 0::
2003-02-25 05:12:55.14 spid4 Owner:0x192e32e0 Mode: X  Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0
2003-02-25 05:12:55.15 spid4 SPID: 52 ECID: 0 Statement Type: DELETE Line #: 1
2003-02-25 05:12:55.15 spid4 Input Buf: Language Event: delete deadt2

2003-02-25 05:12:55.15 spid4 Requested By:
2003-02-25 05:12:55.15 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x19CA3500) Value:0x192e3340 Cost:(0/98)
2003-02-25 05:12:55.16 spid4
2003-02-25 05:12:55.16 spid4 Node:2
2003-02-25 05:12:55.16 spid4 RID: 2:1:28:0     CleanCnt:1 Mode: X Flags: 0x2
2003-02-25 05:12:55.17 spid4 Grant List 0::
2003-02-25 05:12:55.17 spid4 Owner:0x192e3400 Mode: X   Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0
2003-02-25 05:12:55.17 spid4 SPID: 51 ECID: 0 Statement Type: DELETE Line #: 1
2003-02-25 05:12:55.18 spid4 Input Buf: Language Event: delete deadt1

2003-02-25 05:12:55.18 spid4 Requested By:
2003-02-25 05:12:55.18 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:52 ECID:0 Ec:(0x19AB9508) Value:0x192e3300 Cost:(0/98)
2003-02-25 05:12:55.19 spid4 Victim Resource Owner:
2003-02-25 05:12:55.19 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:52 ECID:0 Ec:(0x19AB9508) Value:0x192e3300 Cost:(0/98)

블로킹 발생 시 원인 추적하기

다음에 소개하는 저장 프로시저들은 블로킹을 점검하는데 유용하게 사용할 수 있는 저장 프로시저들입니다. 다음의 저장 프로시저들은 master 데이터베이스에 생성해 두고 블로킹 발생 시에 활용하실 것을 권고합니다.

  • sp_blocker_pss80
    블로킹에 관한 전반적인 정보를 수집할 수 있는 매우 유용한 저장 프로시저입니다. Microsoft 웹사이트의 다음 아티클에 sp_blocker_pss80 저장 프로시저 생성 스크립트가 있으므로 활용하시기 바랍니다.
    http://support.microsoft.com/default.aspx?scid=kb;ko-kr;271509
    (아티클 제목 : How to monitor SQL Server 2000 blocking)
  • sp_leadblocker, sp_blockinglocks
    Inside SQL Server 책에 있는 스크립트로서, 블로킹 발생의 원인이 되는 프로세스에 대한 정보와, 블로킹에 관련되는 잠금에 대한 정보를 제공하는 저장 프로시저입니다.
[따라하기]
  1. 블로킹 추적에 유용한 저장 프로시저들을 생성합니다. (sp_blocker_pss80, sp_leadblocker, sp_blockinglocks) <PRE>USE masterGOCREATE PROCEDURE sp_leadblocker AS IF EXISTS (SELECT * FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)) SELECT spid, status, loginame=SUBSTRING(SUSER_SNAME(sid), 1, 12), hostname=substring(hostname, 1, 12), blk=CONVERT(char(3), blocked), dbname=substring(db_name(dbid),1,10),cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked=0 ELSE SELECT 'No blocking processes found!'GOCREATE PROCEDURE sp_blockinglocks ASSET NOCOUNT ON SELECT DISTINCT CONVERT (SMALLINT, L1.req_spid) AS SPID, L1.rsc_dbid AS DBID, L1.rsc_objid AS OBJID, L1.rsc_indid AS INDID, SUBSTRING (V.name, 1, 4) AS TYPE, SUBSTRING (L1.rsc_text, 1, 16) AS RESOURCE, SUBSTRING (U.name, 1, 8) AS MODE, SUBSTRING (X.name, 1, 5) AS STATUS FROM master.dbo.syslockinfo L1, master.dbo.syslockinfo L2, master.dbo.spt_values V, master.dbo.spt_values X, master.dbo.spt_values U WHERE L1.rsc_type = V.number AND V.type = 'LR' AND L1.req_status = X.number AND X.type = 'LS' AND L1.req_mode + 1 = U.number AND U.type = 'L' AND L1.rsc_type <>2 /* 2 : DB LOCK */ AND L1.rsc_dbid = L2.rsc_dbid AND L1.rsc_bin = L2.rsc_bin AND L1.rsc_objid = L2.rsc_objid AND L1.rsc_indid = L2.rsc_indid AND L1.req_spid <> L2.req_spid AND L1.req_status <> L2.req_status --AND(L1.req_spid IN (SELECT BLOCKED FROM master..SYSPROCESSES) -- OR L2.req_spid IN (SELECT BLOCKED FROM master..SYSPROCESSES)) ORDER BY SUBSTRING (L1.rsc_text, 1, 16), SUBSTRING (X.name, 1, 5) RETURN (0) GO</PRE>
  2. 시스템 SP 및 DBCC 명령어와 작업 단계 1에서 추가한 SP를 수행하여 그 결과를 분석합니다.
    2-1. sp_blocker_pss80 활용예 <PRE>WHILE 1=1BEGIN EXEC master.dbo.sp_blocker_pss80 -- Or for fast mode -- EXEC master.dbo.sp_blocker_pss80 @fast=1 -- Or for latch mode -- EXEC master.dbo.sp_blocker_pss80 @latch=1 WAITFOR DELAY '00:00:15'ENDGO</PRE>2-2. sp_leadblocker, sp_blockinglocks 활용예 <PRE>EXEC sp_leadblockerEXEC sp_blockinglocksGO</PRE>2-3. 블로킹을 유발하는 프로세스에 대하여 sp_lock, sp_who2, sp_who 등의 시스템 SP를 수행하면 잠금과 프로세스에 대한 보다 자세한 내용을 별도로 점검할 수 있습니다. <PRE> EXEC sp_who2 53 EXEC sp_lock 53 GO</PRE>2-4. 트랜잭션을 오픈한 채로 있는 프로세스가 블로킹을 유발하는 경우에는 DBCC OPENTRAN을 사용하여 특정 데이터베이스에서 가장 오래된 활성 트랜잭션에 대한 정보를 점검할 수 있습니다. 참고로, 트랜잭션에 트랜잭션 이름을 기술하면 문제가 있는 트랜잭션을 확인하는 작업이 용이해집니다. <PRE> USE pubs DBCC OPENTRAN GO -- 또는 DBCC OPENTRAN ('pubs') GO</PRE>

대기(wait) 점검하기

[따라하기]
  1. wait 정보를 저장할 데이터베이스를 생성합니다. <PRE>USE masterGOCREATE DATABASE DBAdmin ON (NAME = DBAdmin_dat, FILENAME = 'D:\DBdata\DBAdmin_dat.mdf'SIZE = 500 MB, MAXSIZE = 1 GB, FILEGROWTH = 100 MB) LOG ON (NAME = DBAdmin_log, FILENAME = 'D:\ DBData\DBAdmin_log.ldf', SIZE = 100 MB, MAXSIZE = 500 MB, FILEGROWTH = 100 MB) GO</PRE>
  2. wait 정보를 추적할 저장 프로시저를 생성합니다.
    USE DBAdmin
    GO
    CREATE PROCEDURE get_waitstats
    AS
    -- This stored procedure is provided "AS IS" with no warranties,
    -- and confers no rights.
    -- Use of included script samples are subject to the terms specified at
    -- http://www.microsoft.com/info/cpyright.htm
    -- this proc will create waitstats report listing wait types by percentage
    -- can be run when track_waitstats is executing
    SET NOCOUNT ON

    DECLARE @now datetime, @totalwait numeric(20,1)
    ,@endtime datetime,@begintime datetime
    ,@hr int, @min int, @sec int

    SELECT @now=max(now),@begintime=min(now),@endtime=max(now)
    FROM waitstats WHERE [wait type] = 'Total'

    -- subtract waitfor, sleep, and resource_queue from Total
    SELECT @totalwait = sum([wait time]) + 1
    FROM waitstats
    WHERE [wait type] not in
    ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***')
    AND now = @now

    -- insert adjusted totals, rank by percentage descending
    DELETE waitstats WHERE [wait type] = '***total***' and now = @now
    INSERT INTO waitstats select '***total***',0,@totalwait,@totalwait,@now

    SELECT [wait type],[wait time]
    ,percentage=cast (100*[wait time]/@totalwait as numeric(20,1))
    FROM waitstats
    WHERE [wait type] not in
    ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
    AND now = @now
    ORDER BY percentage DESC
    GO

    CREATE PROCEDURE track_waitstats (@num_samples int=10,@delaynum int=1,@delaytype nvarchar(10)='minutes')
    AS
    -- T. Davidson
    -- This stored procedure is provided "AS IS" with no warranties,
    -- and confers no rights.
    -- Use of included script samples are subject to the terms specified at
    -- http://www.microsoft.com/info/cpyright.htm
    -- @num_samples is the number of times to capture waitstats,
    -- default is 10 times. default delay interval is 1 minute
    -- delaynum is the delay interval.
    -- delaytype specifies whether the delay interval is minutes or seconds
    -- create waitstats table if it doesn't exist,
    -- otherwise truncate
    SET NOCOUNT ON
    IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'waitstats')
    CREATE TABLE waitstats ([wait type] varchar(80),
    requests numeric(20,1),
    [wait time] numeric (20,1),
    [signal wait time] numeric(20,1),
    now datetime default getdate())
    ELSE TRUNCATE TABLE waitstats

    DBCC SQLPERF (waitstats,clear) -- clear out waitstats
    DECLARE @i int,@delay varchar(8),@dt varchar(3)
    , @now datetime, @totalwait numeric(20,1)
    ,@endtime datetime,@begintime datetime
    ,@hr int, @min int, @sec int
    SELECT @i = 1
    SELECT @dt = CASE lower(@delaytype)
    WHEN 'minutes' THEN 'm'
    WHEN 'minute' THEN 'm'
    WHEN 'min' THEN 'm'
    WHEN 'mm' THEN 'm'
    WHEN 'mi' THEN 'm'
    WHEN 'm' THEN 'm'
    WHEN 'seconds' THEN 's'
    WHEN 'second' THEN 's'
    WHEN 'sec' THEN 's'
    WHEN 'ss' THEN 's'
    WHEN 's' THEN 's'
    ELSE @delaytype
    END
    IF @dt not in ('s','m')
    BEGIN
    PRINT 'please supply delay type e.g. seconds or minutes'
    RETURN
    END

    IF @dt = 's'
    BEGIN
    SELECT @sec = @delaynum % 60
    SELECT @min = cast((@delaynum / 60) as int)
    SELECT @hr = cast((@min / 60) as int)
    SELECT @min = @min % 60
    END
    IF @dt = 'm'
    BEGIN
    SELECT @sec = 0
    SELECT @min = @delaynum % 60
    SELECT @hr = cast((@delaynum / 60) as int)
    END
    SELECT @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
    + right('0'+convert(varchar(2),@min),2) + ':' +
    + right('0'+convert(varchar(2),@sec),2)
    IF @hr > 23 or @min > 59 or @sec > 59
    BEGIN
    SELECT 'hh:mm:ss delay time cannot > 23:59:59'
    SELECT 'delay interval and type: ' + convert (varchar(10),@delaynum)
    + ',' + @delaytype + ' converts to ' + @delay
    RETURN
    END
    WHILE (@i <= @num_samples)
    BEGIN
    INSERT INTO waitstats ([wait type], requests, [wait time],[signal wait time])
    EXEC ('DBCC SQLPERF(WAITSTATS)')
    SELECT @i = @i + 1
    WAITFOR DELAY @delay
    END
    --- create waitstats report
    EXEC get_waitstats
    GO
  3. 수집 간격과 반복 실행 횟수를 지정하여 대기 정보를 수집합니다. <PRE>-- 2초 간격으로 10번 반복 수행 예제USE DBAdminEXEC Track_waitstats @num_samples=10,@delaynum=2,@delaytype='seconds'GOSELECT * FROM waitstatsGO-- 실행 예제 : 디폴트 ( 실행 소요 시간 : 10분 ) USE DBAdminEXEC Track_waitstatsGO</PRE>


마치면서

본 포켓 관리 가이드는 DBA가 기본적으로 알아야 할 내용을 담고 있습니다. 자세한 내용은 이를 바탕으로 더욱 정진하시기 바랍니다. 또한 지면 관계상 성능에 대한 내용은 포함시키지 못했습니다. 성능에 대해서는 포켓 가이드 시리즈인 "SQL Server 성능 향상을 위한 튜닝 가이드"를 참조하시기 바랍니다.

[출처] 웹디황용

"쇼핑몰·홈페이지·오픈마켓
블로그·페이스북·이메일 등의 각종 마케팅 글쓰기,
각종 광고, 영업, 판매, 제안서, 전단지
반응율 3배×10배 이상 높이는 마법의 8단계 공식"
자세히보기

Comments

번호 제목 글쓴이 날짜 조회
2475 MsSql 날짜 함수 99 단국강토 01.02 1559
2474 SQL Server에서 글로벌 변수 구현하기 99 단국강토 12.30 2238
2473 <ms-sql>제약 (constraint)-NOT NULL, DEFAULT, PRIMAY 99 단국강토 12.30 2514
열람중 MSSQL Server DBA 가이드-5 M 최고의하루 12.26 1797
2471 MSSQL Server DBA 가이드-4 M 최고의하루 12.26 3479
2470 MSSQL Server DBA 가이드-3 M 최고의하루 12.26 1558
2469 MSSQL Server DBA 가이드-2 M 최고의하루 12.26 1524
2468 MSSQL Server DBA 가이드-1 M 최고의하루 12.26 1698
2467 [SQL] sysobjects M 최고의하루 12.26 2259
2466 1..순위 결정 함수 M 최고의하루 12.24 1800
2465 데이터베이스의 저장 프로시저를 자동으로 생성 M 최고의하루 12.24 1466
2464 문자열에서 단어 분리 - SQL Server 2005 M 최고의하루 12.23 1895
2463 25가지 SQL작성법-3 M 최고의하루 12.23 2177
2462 25가지 SQL작성법-2 M 최고의하루 12.23 2359
2461 25가지 SQL작성법-1 M 최고의하루 12.23 1739
2460 제약조건(1) M 최고의하루 12.20 3633
2459 [MSSQL]SQL Server Management Studio Express M 최고의하루 12.20 2775
2458 Microsoft SQL Server 2005 Express Edition SP2 M 최고의하루 12.19 2122
2457 Microsoft SQL Server Management Studio Express SP2 M 최고의하루 12.19 1976
2456 [MSSQL] 데이터 백업과 복구 ( SQL SERVER ) M 최고의하루 12.18 2192
2455 [ Sybase ] Sybase 기본 명령어 M 최고의하루 12.18 2780
2454 오라클(Oracle) 10g Database 설치 M 최고의하루 12.04 2277
2453 오라클데이터베이스 복구 M 최고의하루 12.04 1890
2452 요일, 연중/월중 몇째주 구하기 13 김영철 01.23 2900
2451 다른 서버로 DB 백업 받기 13 김영철 01.23 1887
2450 MSSQL 내장 함수 목록 13 김영철 01.23 2059
2449 몇가지 sql 명령어 13 김영철 01.23 2823
2448 명령어정리 13 김영철 01.23 1613
2447 order by newid() 13 김영철 01.23 2221
2446 SQL : Categories : 프로시저(Procedure). 13 김영철 01.23 1975
마케팅
특별 마케팅자료
다운로드 마케팅자료
창업,경영
기획,카피,상품전략
동기부여,성취