1 데이터 사용량과 사용자들의 업무 프로세스를 분석한다.

데이터 사용량 분석
논리적 모델링이 끝난후 물리적 모델링으로 넘어 갔을때 관련된 테이블의 스키마의 구성이 끝난후
생성된 테이블들의 하루에 입력되는 데이터 건수와 조회되는 데이터 건수가 얼마가 되는지등을 분석하는것

사용자들의 업무 프로세스 분석
데이터를 조회할때 어떤 컬럼을 기준으로 조회/정렬 하는지
다른 테이블을 연관해서 데이터 조회 여부,
데이터 입력후 다른 테이블을 업데이트 하는것인지 등등
그 과정과 그와 관련된 프로세스를 분석하는것

2 클러스터드 인덱스의 사이즈는 적을수록 좋다.

클러스터드 인덱스가 존재하면 모든 넌클러스터드 인덱스의
리프레벨은 클러스터드 인덱스의 키값을 포인터 정보로 갖는다.

클러스터드 인덱스의 사이즈는 넌클러스터드 인덱스의 사이즈에 영향을
끼치기 때문에 클러스터드 인덱스의 사이즈는 적을수록 좋다.

예) 클러스터드 인덱스의 사이즈가 10Byte 이면 모든 넌클러스터드 인덱스의 리프레벨은
    10Byte의 클러스터드 인덱스의 키값을 포인트 정보를 갖게 된다.

※ 모든 인덱스의 사이즈는 작으면 작을수록 좋다!

3 한 테이블에 컬럼의 숫자는 많아야 10개 내외가 될 수 있도록 디자인 하자.

컬럼의 숫자가 많아지면 ROW의 길이기 길어지기 때문에 데이터 입력시
여러개의 페이지가 사용되어지기 때문에 물리적 I/O 성능이 떨어지게 된다.

그리고 컬럼의 숫자가 많아지면 하나의 테이블에 많은 데이터가 존재하기 때문에
관련된 프로세스를 처리하다보면 서로 연관성이 있는 컬럼을 엑세스하면서 프로세스가 집중될수 있다.
프로세스가 집중되다 보면 DEAD LOCK이 발생할 가능성이 높아진다.

4 복합 인덱스(Composite Index)의 경우 컬럼의 순서를 고려하자.

복합 인덱스일때 인덱스의 첫번째 컬럼으로 데이터가 정렬된다.
INDEX(컬럼A, 컬럼B)와 INDEX(컬럼B, 컬럼A)는 전혀 다른 인덱스이다.
INDEX(컬럼A, 컬럼B)는 컬럼A를 기준으로 정렬되며 INDEX(컬럼B, 컬럼A)는 컬럼A를 기준으로 정렬된다.
복합 인덱스를 생성하는 기준은 데이터 조회시 어떤 기준으로 조회하는지를 확인 후 결정한다.

복합 인덱스에서 첫번째 컬럼의 길이가 짧을수록 검색 성능은 좋아진다.

5 커버드 쿼리(Covered Query)를 적용하자.

조회 조건과 조회의 대상이되는 컬럼이 모두 인덱스로 구성되어 있는 쿼리를 커버드 쿼리라고 한다.

예) 회원 테이블에서 회원인증을 할때

SELECT 회원ID FROM 회원 WHERE 회원ID = 회원ID AND 비밀번호 = 비밀번호

CREATE INDEX 회원인증_IDX ON DBO.회원(회원ID, 비밀번호)로 인덱스 생성를 생성하면
WHERE 조건과 조회 대상이 되는 컬럼이 인덱스로 구성되어 있기 때문에
데이터 조회시 인덱스의 리프레벨에서 관련된 프로세스를 모두 처리할 수 있기 때문에
처리 속도가 빠르다.

※ 회원 테이블에서 회원인증을 할때 회원정보다 같이 가져와야 한다면
   2005의 INCLUDE 기능을 사용하는것이 좋다. 이경우도 커버드 퀴리에 속한다.

   SELECT 회원ID, 이름, 회원등급 FROM 회원 WHERE 회원ID = 회원ID AND 비밀번호 = 비밀번호

   CREATE INDEX 회원인증_IDX ON DBO.회원(회원ID)
   INCLUDE (비밀번호, 이름, 회원등급)

6 파일 그룹(File Group)을 활용하자.

성능상의 이슈와 관리상의 이슈를 해결하기 위해서 파일 그룹을 사용한다.
사용자가 원하는 테이블이나 인덱스(데이터 베이스내에서 사이즈를 가지고 있는 테이블이나 인덱스)를
사용자가 원하는 파일 그룹에 위치시켜 관리할수 있고 파일 그룹단위로 백업/복원을 할수 있기 때문에
대용량 데이터베이스를 관리하는 솔루션이 될수 있다.

파티션 테이블, 파티션 인덱스를 구성할 수 있다.

파티션 테이블
하나의 테이블을 여러개의 파일 그룹으로 구성하면 데이터가 각각의 파일 그룹으로 분산되기 때문에
테이블에 걸리는 로드가 각각의 파일그룹으로 분산되어진다.

파티션 인덱스
파티션 테이블에 인덱스를 생성하는 경우
하나의 큰 인덱스를 생성하게되면 인덱스의 뎁스(단계)가 높아지지만
파티션 인덱스는 인덱스의 뎁스가 낮고 인덱스 검색시 속도가 빠르다.

7 인덱스를 잘 활용할 수 있도록 쿼리를 작성하자.

- 인덱스가 정의된 컬럼을 가공하면 안된다.

   CREATE INDEX 판매_IDX ON DBO.판매(판매량) 으로 인덱스를 생성했을때

   Query 1) SELECT 회원ID FROM 판매 WHERE 판매량 = 50

   Query 2) SELECT 회원ID FROM 판매 WHERE 판매량 + 5 = 45

   두쿼리의 결과는 같지만 Query 1)은 인덱스를 타지만 Query 2)는 인덱스를 타지 못한다.

- LIKE 검색시 검색단어앞의 %는 사용하지 않는다.

  CREATE INDEX 회원인증_IDX ON DBO.회원(회원ID) 으로 인덱스를 생성했을때

  Query 1) SELECT 회원ID FROM 회원 WHERE 회원ID LIKE '회원ID%'

  Query 2) SELECT 회원ID FROM 회원 WHERE 회원ID LIKE '%회원ID%'

  위의 두 쿼리도 Query 1)은 인덱스를 타지만 Query 2)는 인덱스를 타지 못한다.

8 클러스터드 인덱스를 생성할때는 주의 해야한다.

  ※ 순차적으로 입력되는 컬럼에 클러스터드 인덱스를 만든다.

   판매 기록 테이블이 있을때

   상품코드, 구매자번호, 판매일자 컬럼중 클러스터드 인덱스를 생성할 컬럼을 선택한다면?

   답은 판매일자!

   - 상품코드 컬럼에 클러스터드 인덱스를 생성
  
     판매 기록 테이블의 모든 데이터가 상품코드 컬럼을 기준으로 정렬되어 진다.
 
     상품코드A
     상품코드B
     상품코드C
     상품코드D
     상품코드E
 
     이렇게 정렬되어 있는 상황에서 상품코드B의 판매 기록이 입력되면 상품코드 컬럼을 기준으로
     데이터를 다시 정렬해야 하기 때문에 부하가 크다.

   - 구매자번호 컬럼에 클러스터드 인덱스 생성

     판매 기록 테이블의 모든 데이터가 구매자번호 컬럼을 기준으로 정렬되어진다.

     구매자1
     구매자2
     구매자3
     구매자4
     구매자5

     이렇게 정렬되어 있는 상황에서 구매자2가 판매 기록이 입력되면 구매자번호 컬럼을 기준으로
     데이터를 다시 정렬해야 하기 때문에 부하가 크다.

   - 판매일자 컬럼에 클러스터드 인덱스 생성

     판매일자는 그날그날 판매되는 날짜이기 때문에 갑자기 하루나 몇일전의 판매 기록이 입력되는 일이 없다면
     데이터를 다시 정렬할 상황이 없다.

  
  ※ 클러스터드 인덱스를 만들만한 컬럼이 없다면 일련번호 컬럼(자동 증가열)에
     클러스터드 인덱스를 만드는것도 좋은 방법이다.
 

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1 기본키(Primary Key)제약 조건

기본키 제약조건을 정의하게 되면 기본키 컬럼에 유니크(Unique)한 클러스터드 인덱스가 기본적으로 만들어진다.
기본키를 생성하면서 만들어진 인덱스는 개별적으로 삭제할 수 없으며 기본키 제약조건을 해제하면 기본키 컬럼에 인덱스도 함께 제거된다.

※ 기본키 컬럼보다 더 많은 범위 검색이 이루어지는 컬럼이 있다면
    기본키를 생성할때 넌클러스터드 인덱스로 생성하고 범위 검색이 자주 이루어지는 컬럼에
    클러스터드 인덱스를 생성하는것도 좋은 방법이다.


2 포린키(Foreign Key)제약 조건

포린키 컬럼은 기본적으로 조인의 조건이나 참조 무결성을 구현하는 과정에서 자주 액세스되지만 기본적으로 인덱스가 만들어지지 않는다.

※ 조인의 성능 항샹을 위해서 포린키 컬럼에 적절한 인덱스를 생성해 주어야 한다.
    두 컬럼의 차수가 1:1 이면 포인트 쿼리이기 때문에 포린키에 넌클러스터드 인덱스를 생성하는것이 좋다
    두 컬럼의 차수가 1:N 이면 범위 쿼리이기 때문에 포린키에 클러스터드 인덱스를 생성하는것이 좋다.

3 유니크(Unique)제약 조건

유니크 제약조건을 정의하게 되면 해당 컬럼에 유니크(Unique)한 넌 클러스터드 인덱스가 기본적으로 만들어진다.

4 인덱스 페이지의 채우기 비율

- FillFactor : 리프 레벨의 채우기 비율을 정의
- PadIndex : 넌 리프 레벨의 채우기 비율을 정의

※ 일반적으로 85%를 기준으로 데이터 입출력 비율을 감안하여 설정한다.
    실제로 성능에 영향을 끼치는것은 FillFactor


5 조각 모음과 재생성

인덱스의 채우기 비율을 잘잡아도 데이터의 입/출력이 있다보면 조각화가 발생하여 조각 모임 또는 재생성 해줘야한다.

※ 조각화의 상태가 심각하다면 재생성을 조각화의 상태가 덜하다면 조각모음을 권장한다.

- sys.dm_db_index_physical_stats : 인덱스의 동적인 상태정보 확인
- 인덱스 재 생성 : ALTER INDEX ALL ON TEST03 REBUILD WITH (FILLFACTOR = 80)
- 인덱스 조각모음 : ALTER INDEX ALL ON TEST03 REORGANIZE


6 인덱스의 크기와 데이터 입력시의 성능 비교

인덱스의 크기
넌클러스터드 인덱스 > 클러스터드 인덱스

데이터 입력시 성능
넌클러스터드 인덱스 > 클러스터드 인덱스

데이터 조회시 성능
클러스터드 인덱스 > 넌클러스터드 인덱스

7 데이터의 밀도가 높은 경우 인덱스를 만들지 않는다.

예) 성별의 경우 남/녀의 경우만 존재하기 때문에 인덱스를 만들지 않는다.
     이런경우는 인덱스를 생성해도 성능차이가 없다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

* 인덱스를 사용하는 이유
  데이터의 검색속도를 향상시키기 위해서

* 인덱스를 통해서 데이터를 빠르게 검색할 수 있는 이유
  인덱스는 항상 정렬된 상태를 유지하고 있기 때문


1 클러스터드 인덱스와 넌클러스터드 인덱스 소개

* 클러스터드 인덱스 (Clustred Index)
- Leaf Level은 데이터페이지이다.
- 클러스터드 인덱스가 생성되는 컬럼를 기준으로
  데이터 페이지에 저장된 데이터를 물리적으로 정렬시킨다.
- 기본적으로 넌클러스터드 인덱스보다 검색 속도가 빠르며
  특히 범위 조회(Range Query)에 빠른 속도를 나타낸다.
  (클러스터드 인덱스는 인덱스의 리프 레벨이 데이터 페이지기 때문에
   넌클러스터드 인덱스보다 검색 속도가 빠르다.
   클러스터드 인덱스는 데이터가 정렬되어 있기 때문에 범위 조회에서 속도가 빠르다.)
- 한 테이블에 하나의 클러스터드 인덱스만 만들 수 있다.
- 기본키 제약조건(Primary Key)을 정의하게 되면
  기본적으로 기본키 컬럼에 Unique한 클러스터드 인덱스가 만들어진다.

* 넌 클러스터드 인덱스 (Non Clustered Index)
- 인덱스 생성시 물리적으로 데이터를 정렬시키지 않고 있는 그대로의 위치 정보로 인덱스를 구성한다.
- 데이터 페이지 위에 인덱스 페이지가 위치하게 되며
  기본적으로 클러스터드 인덱스보다 검색속도가 느리며
  범위 조회(Range Query)를 할 경우 거의 인덱스의 도움을 받을 수 없다.
  (인덱스를 안탄다. Table Scan을 하게 된다.)
- 포인트 쿼리에서 좋은 성능을 발휘한다.
- 한 테이블에 249개까지의 넌클러스터드 인덱스를 만들수 있다.



일반적인 책 맨 끝의 인덱스는? 넌클러스터드 인덱스
클러스트드 인덱스에 해당하는것은? 사전

※ 밀도, 선택도
밀도 높다. -> 선택도가 낮다.
선택도 높다 -> 밀도가 낮다.

넌클러스터드 인덱스 : Point Query 에서 좋음, 선택도가 높다.

선택도가 높다, 낮다라는 판단은 전체의 데이터중 몇건의 데이터를 리턴하느냐에 따라서 결정된다.



2 인덱스의 생성

* 넌클러스터드 인덱스





* 클러스터드 인덱스





3 페이지 분할 현상





위의 그림을 보면 데이터가 입력될때는 클러스터드 인덱스가 더 많은 영향을 받는다는것을 알수 있다.
클러스터드 인덱스의 경우 인덱스의 리프레벨에 레코드가 끼어들게 되고
넌 클러스터드 인덱스의 경우 데이터는 데이터 페이지에 입력되고 인덱스 정보만
갱신 되기 때문에 데이터가 입력될때는 넌클러스터드 인덱스가 성능상의 영향을 더 적게 받는다.

데이터가 입력될때마다 페이지 분할 현상이 발생하게 되면 입력할때도
많은 오버헤드가 발생하게되고 조각화가 심해진다.
조각화가 심해지면 같은양의 정보를 저장하는데 있어서 더 많은 페이지가 사용되고
인덱스의 뎁스도 더 높아지며 데이터를 조회할때 성능도 보장하지 못한다.

4 넌클러스터드 인덱스가 있는 상황에서 클러스터드 인덱스를 생성할때

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

* 인덱스를 사용하는 이유?
  데이터의 검색 속도를 향상 시키기 위해

* 인덱스를 통해 데이터를 빨리 찾을 수 있는 이유?
  인덱스는 항상 정렬된 상태를 유지하기 때문



1 인덱스의 구조
  인덱스는 계층적인(B-Tree) 구조를 갖는다.

  ※ 사전의 인덱스와 달리 컴퓨터에서의 인덱스의 구조가 계층적인 이유
  사람의 경우 사전처럼 A~Z로 정렬이 되어 있는 구조에서 원하는 단어를 찾기 위해서
  임의의 지점으로 접근하여 원하는 단어를 찾을수 있지만
  컴퓨터는 Start라는 단어를 찾기 위해서 A,B,C,D~~ 순으로 인덱스를 처음부터 검색하여
  Start라는 단어가 나올때까지 검색을 하기 때문에 계층적으로 되어 있다.
  (왜 계층적으로 되어 있는지 자세한 설명은 저~~ 밑에서)

  Leaf Level
  인덱스의 가장 하의 영역
  일반적인 인덱스의 영역

  Non-Leaf Level
  Root Level, Intermediate Level 을 통틀어 Non-Leaf Level이라고 한다.
  하위 페이지(Leaf Lavel)에 대한 정보를 가지고 있다.



2 데이터 검색 방법

Table Scan
인덱스가 없거나 인덱스가 있더라도 인덱스가 없는 컬럼을 조회하여 테이블의 데이터가 저장된 Data Page를 순차적으로 조회하는 방법

Index Seek
인덱스가 있거나 인덱스가 정의된 컬럼을 조회해서 인덱스로 데이터를 찾아 들어가는 방법


3 데이터의 검색 유형

* 포인트 쿼리 (Point Query)
  조회를 통해 얻어진 결과값이 하나 혹은 없는 경우의 질의 (최대 결과값은 1개)

* 범위 쿼리 (Range Query)
  조희를 통해 얻어진 결과값이 여러개가 리턴될 수 있는 경우의 질의

※ 포인트 쿼리 범위 쿼리냐를 구분하는것은 SELECT 구문이 어떻게 되어있느냐가 아니라
   그 테이블의 데이터가 어떻게 저장되어 있느냐에 따라서 같은 SELECT문이라도
   포인트 쿼리, 범위 쿼리가 될수 있다.

   예) SELECT * FROM 테이블 WHERE 필드A = 'A'
    테이블의 필드A에 'A' 데이터가 1개가 저장되어 있으면 포인트 쿼리가 되지만
    'A' 데이터가 1개이상 저장되어 있으면 범위 쿼리가 된다.

* 커버드 쿼리 (Covered Query)
  조회의 조건과 조회의 대상이 되는 컬럼이 모두 인덱스로 구성된 질의

 

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1) DBCC HELP
공식적인 DBCC 명령문의 구문 정보를 반환
비공식적인 DBCC 명령문은 인터넷에서 검색하면 찾을수 있다.


2) DBCC TRACEON/TRACEOFF/TRACESTATUS
TRACEON과 TRACEOFF는 추적 플래그를 설정/해제

추적 플래그
 - 일사적으로 서버의 일부 특성을 설정하거나 특정 동작을 중지시키는데 사용하는 플래그
 - 3604 : 추적 결과를 화면으로 출력
 - 3605 : 추적 결괄ㄹ 로그에 기록
 - 1204 : 교착상태(deadlock)를 추적
 - 2528 : DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE에 의한 개체 병렬 처리를 금지
 - 3205 : 테이프 드라이버의 하드웨어 압축을 금지시킨다.


TRACESTATUS : 현재의 추적 플래그 설정 상태를 출력


3) DBCC SQLPERF(LOGSPACE/IOSTATS/LRUSTATS/NETSTATS)
LOGSPACE : 트랜잭션 로그 공간의 사용에 관한 통계 출력
IOSTATS : 입출력 통계 출력
LRUSTATS : 데이터 캐시(버퍼)의 통계 출력
NETSTATS : 네트워크 관련 통계 출력


4) DBCC OPENTRAN
현재 데이터베이스 또는 지정한 데이터베이스에서 가장 오래된 활성 트랜잭션에 대한 정보를 출력


5) DBCC CHECKDB
지정한 데이터베이스에서 모든 개체의 할당과 구조적 무결성을 검사


6) DBCC INPUTBUFFER/OUTPUTBUFFER
클라이언트로부터 마지막으로 받은 명령문과 클라이언트에게 마지막으로 보낸 결과 집합을 출력


7) DBCC PROCCACHE
프로시저 캐시에 대한 정보를 보여준다.


8) DBCC SHOWCONTIG
지정한 테이블의 데이터와 인덱스에 대한 조각화 정보를 출력


9) DBCC SHOW_STATISTICS
지정한 테이블에서 특정 대상의 현재 배포 통계를 출력


10) DBCC USEROPTIONS
현재 연결에서 설정된 옵션들을 출력


11) DBCC dllname(FREE)
지정한 (확정 저장프로시저) 동적 연결 라이브러리(DLL)를 메모리에서 언로드


12) DBCC INDEXDEFRAG
지정한 테이블의 인덱스 조작을 모운다.


13) DBCC PINTABLE/UNPINTABLE
지정한 테이블의 페이지를 데이터 캐시에 고정시킨다.
※ 전체 성능을 저하시키는 부작용이 있으므로, 특별한 이유가 없는 한 쓰지말것


14) DBCC DROPCLEANBUFFERS
데이터 캐시(버퍼)를 완전히 비움
데이터를 하드디스크에서 읽어오도록 초기화하여 성능테스트등을 할때 사용


15) DBCC FREEPROCCACHE
프로시저 캐시를 완전히 비움
저장 프로시저등을 모두 새로 컴파일하도록 초기하여 성능테스트등을 할때 사용

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. 시스템 프로시저

1) sp_who, sp_who2
sp_who는 SPID별 상태, 로그인, 사용 데이터베이스 및 명령어, 블로킹 정보 등을 보여주는 시스템 저장 프로시저이며,  sp_who2는 sp_who보다 더 많은 벙보를 보여준다.
자신의 SPID에 대해서만 결과를 보려면 sp_who2 @@SPID를 실행한다.


2) sp_lock
잠금과 관련된 정보를 보여주는 시스템 저장 프로시저
자신의 잠금만을 보려면 sp_lock @@SPID를 실행한다.
연관된 두개의 SPID에 대한 잠금을 보려면 sp_lock spid1, spid2 를 샐항한다.


3) sp_monitor
SQL Server의 서버 작업량 통계를 보여주는 시스템 저장 프로시저

last_run : sp_monitor가 마지막으로 실행된 시간
current_run : sp_monitor가 이번에 실행된 시간
seconds : last_run, current_run 의 시간차
cpu_busy : 서버 컴퓨터의 CPU가 SQL Server 작업을 수행한 시간(초)
io_busy : SQL Server에서 입력 및 출력 작업을 수행하는 데 걸린 시간(초)
idle : SQL Server가 유휴 상태에 있던 시간(초)
packets_received : SQL Server에서 읽은 입력 패킷 수
packets_sent : SQL Server에서 쓰여진 출력 패킷 수
packets_errors : 패킷을 읽고 쓰면서 SQL Server에서 발생한 오류 수
total_read : SQL Server에서 이루어진 읽기 작업 수
total_write : SQL Server에서 이루어진 쓰기 작업 수
total_errors : 읽고 쓰는 중에 SQL Server에서 발생한 오류 수
connections : 로그인 또는 SQL Server에 시도한 로그인 수

출력값 보는 방법 예) 4250(125)-68% 일경우
4250은 SQL Server가 마지막으로 시작된 후 경과한 초의 수(4250초 동안 사용되었다.
125는 sp_monitor가 마지막으로 실행된후 초과된 초의 수(125초 동안 사용되었다.)
68%는 sp_monitor가 마지막으로 실행된 후 사용시간이 총시간의 69%를 차지 한다는 의미


4) sp_spaceused
데이터베이스 또는 테이블의 각종 공간 사용 정보를 보여주는 시스템 저장 프로시저


5) sp_statistics
테이블에 관한 인덱스 및 통계 목록을 보여주는 시스템 저장 프로시저


6) sp_helpdb
데이터베이스에 관한 정보를 보여주는 시스템 저장 프로시저



2. 시스템 함수

1) @@spid
현재 사용자 프로세스의 서버 프로세스 식별자(ID)를 반환


2) @@cpu_busy
SQL Server가 마지막으로 실행된 후의 CPU작업 시간을 밀리 초 단위로 반환


3) @@connections
SQL Server가 마지막으로 실행된 후의 사용자가 연결을 시도한 횟수(연결된 횟수 포함)를 반환


4) @@error
최근에 실행된 T-SQL 문의 오류 개수를 반환(오류가 없으면 0을 반환)
※ T-SQL문을 실행할 때마다 @@error가 반환되는 값은 초기화 된다.
    @error의 반환 값을 로컬 변수에 저장한 후 이를 활용해야 한다.


5) @@procid
현재 저장 프로시저의 식별(ID)를 반환
일반적으로 저장 프로서저 안에서 실행한다.
저장 프로시저 밖에서 실행할 때는 0을 반환



3. T-SQL

1) set statistics io
T-SQL문이 생성한 디스크 입출력 작업량에 대한 정보를 출력
※ set statistics io on 으로 설정해서 출력되는 결과에서 논리적 읽기 수를 우선을 둔다.


2) set statstics time
명령문을 구문 분석, 컴파일 및 실행하는데 소용된 시간을 출력


3) set showplan_text
명령문을 실행하는 대신 텍스트 형식의 실행 계획을 출력
※ set showplan_text on 으로 설정후 명령문을 실행해도 명령문을 실행하지 않는다.


4) set showplan_all
set showplan_text 보다 더 자세한 내용을 출력
※ set showplan_all on 으로 설정후 명령문을 실행해도 명령문을 실행하지 않는다.


5) set statistics profile
set showplan_all 와 다른 점은 명령문을 실제로 실행한다는 것과, Row, Executes 열이 추가된다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

프로파일러 파일 DB입력
select convert(varchar(8000), TextData) as TextData
    , duration, cpu, reads, writes, databaseId
    , hostname, ApplicationName, loginname
    , spid, StartTime
into tblTrace
from ::fn_trace_gettable('프로파일러 파일', default)
go

주요 컬럼 인덱스 생성
create index idx_tblTrace_duration on tblTrace(duration)
create index idx_tblTrace_cpu on tblTrace(cpu)
create index idx_tblTrace_reads on tblTrace(reads)
go

각기준에 맞는 부하가 큰 부분확인
duration
select top 20 *
from tblTrace
order by duration desc

cpu
select top 20 *
from tblTrace
order by cpu desc

reads
select top 20 *
from tblTrace
order by reads desc


간단한 통계로 확인
select left(TextData,40), count(*) as counts
    , avg(duration) as duration_avg, avg(cpu) as cpu_avg, avg(reads) as reads_avg, avg(writes) as writes_avg
    , max(duration) as duration_max, max(cpu) as cpu_max, max(reads) as reads_max, max(writes) as writes_max
    , min(duration) as duration_min, min(cpu) as cpu_min, min(reads) as reads_min, min(writes) as writes_min
from tblTrace
group by left(TextData,40)
order by 2 desc

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. 이벤트 뷰어

시작 -> 프로그램 -> 관리도구 -> 이벤트 뷰어에 위치하는 Windows 도구로서, 시스템, 보안 및 응용 프로그램 로그에 각종 오류, 경고, 정보 등을 기록해 준다.
시스템이나 SQL Server에 문제가 있을때 제일 먼저 이벤트 뷰어를 확인해 볼 필요가 있다.
이벤트 내의 오류번호로 마이크로소프트 지식 베이스에서 관련된 설명을 확인해 볼수 있다.


2. 성능 모니터

1) 시스템 모니터

시작 -> 프로그램 -> 관리도구 -> 성능(2008 Server에서는 안정성 및 성능 모니터)을 실행하면 시스템 모니터가 실행된다.
시스템 모니터는 기본적으로 현재의 시스템 동작을 보여준다. 그래프, 히스토그램, 보고서 세종류로 보여주며 기본값은 그래프이다.
그래프 보기는 배율이 서로 다른 카운터들을 함께 모니터하는 것이 힘들기 때문에 이런 경우에는 보고서 보기로 보는것이 좀 더 낮다. 그러나 그래프, 히스토그램, 보고서 세가지다 현재 값만 볼수 있고 최대값, 최소값, 평균값 등은 확인할 수 없기 때문에 카운터 로그를 만드는것을 더 권장한다.

2) 카운터 로그

시스템 모니터보다 카운터 로그를 권장한다.

1. 카운터 로그에서 새 로그 설정을 선택한다.

2. 카운터 로그의 이름을 입력한다.

3. 로그 속성창에서 카운터 추가 버튼을 클릭하여 카운터를 추가한다.
   카운터가 추가 되면 될수록 부하가 커지기 때문에 불필요한 카운터는 추가하지 않는다.

4. 데이터 샘플 간격을 조절한다.
   기본 설정은 15초이지만 베이스라인이 없을 경우에는 15초가 아니라 15분 1시간등오로 증가시켜야 한다.

5. 카운터에 따라서 실행 권한을 설정해야 되는 경우도 있다.
    이런 경우에는 권한을 가진 계정을 입력하고 암호 설정을 해야한다.

6. 파일 로그 탭에서는 로그 파일의 이름을 설정할수 있다.


7. 일정 탭에서는 로그 시작/중지 일정을 설정할 수 있다.

8. 저장된 로그 파일은 C:\PrefLogs 폴더에 저장된다.

9. 저장된 로그 파일을 확인하는 방법은 시스템 모니터, HTML이 있다.

10. 시스템 모니터로 로그 확인
     시스템 모니터에 등록되어 있는 카운터들을 모두 삭제하고 3. 로그 속성상에서 추가한 카운터를 추가하고 원통형의 로그데이터보기 버튼을 클릭해서 데이터원본 -> 로그 파일 -> 추가 버튼을 클릭하여 저장된 로그 파일을 선택한다.

11. HTML 로 로그 내용 저장하기
     카운터 로그에서 마우스 오 른쪽 버튼을 클릭하고 다름이름으로 설정 저장을 클릭하면 html 파일 형식으로 저장할수 있다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. 개요

서버 및 데이터베이스의 동작을 모니터링하고 튜닝하는 도구로서 교착상태, 치명적인 오류, 저장 프로시저와 Transact-SQL문, 로그인 동작, 일괄 처리, 트랜잭션 시작과 같은 엔진 프로세스 이벤트등을 추적하여 데이터를 테이블이나 파일로 저장할 수 있다.


2. 템플릿

어떤 이벤트의 어떤 항목들을 추적할것인지 미리 정의해 둔것으로서 기본 템플릿을 그대로 사용하면 과부하가 걸릴수 있다.


3. 저장

추적한 데이터는 테이블, 파일로 저장할 수 있다.

추적한 데이터가 많을때는 기본 설정(5MB)가 부족함으로 필요한 만큼 늘려줘야 한다.
데이터를 파일로 저장시 파일 롤오버 옵션을 사용하면 저장되는 파일의 확장자가 tr1, tr2, trc3... 형식으로 하드가 꽉 찰때까지 저장되기 때문에 주의해야한다. 
추적 중지 시간 설정을 권장한다.

데이터를 테이블로 저장시 서버에 이중으로 부하가 발생함으로 파일로 저장하는것을 권장한다. 파일로 저장된 데이터를 fn_trace_gettable로 테이블에 저장할수 있다.

EX)
SELECT IDENTITY(int, 1, 1) AS RowNumber, *
             INTO #temp
             FROM ::fn_trace_gettable('trc 파일', DEFAULT);


파일, 테이블에 저장하지 않고 추적을 할경우 메모리상에 추적 데이터가 저장되며 메모리가 꽉차면 추적을 중지한다.


4. 이벤트

Standard(기본값)을 선택시 Audit Login, Audit Logout, ExistingConnection, RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting가 선택된다.

Audit Login, Audit Logout : SQL Server에 로그인/로그아웃시 발생, 거의 추적안함
ExistingConnection : 블로킹등 특수한 경우외에 사용안함
RPC:Completed : 클라언트에서 SQL Server로 호출한 프로시저를 종료할 때 발생
SQL:BatchCompleted : 일괄처리 종료시 발생
SP:StmtCompleted : 저장 프로시저안의 명령문들을 문장단위로 추적, 특별한 경우를 제외하고 사용안함


5. 오류 및 경고

Attention, ErrorLog, EventLog, Exception, Execution Warnings, Hash Warring, Missing Column Statistics, Missing Join Predicate, OLEDB Errors, Sort Warnings 이벤트 클래스가 있다.

Attention : 클라이언트 인터럽트 요청, 클라이언트 연결 끊어짐의 주의를 요하는 이벤트
Execution Warnings : 명령문이나 저장 프로시저 실행 중에 발생하는 경고 이벤트
Exceptoin : 가장 흔히 발생하는 각종 오류로 인해 발생하는 이벤트


6. 잠금

Lock:acquired, Lock:Cancel, Lock:Deadlock, Lock:Deadlock Chain, Lock:Escalation, Lock:Released, Lock:Timeout 이벤트 클래스가 있다.

Lock:Deadlock Chain : 교착상태가 어떤 개체와 관련되어 있는지 확인하는 이벤트
Lock:Deadlock : 잠금을 얻을려고 시도하는 동안 교착상태가 발생할하여 잠금을 얻을려고 하는 시도가 취소될때 발생하는 이벤트
Lock:Timeout : SET LOCK_TIMEOUT문으로 설정해놓은 잠금 시간 만료가 되었을때 발생하는 이벤트

위의 세가지 이외의 이벤트 클래스들은 특별한 경우가 아닌 한 사용하지 않는것이 좋다.

※ 실제 서버에서 프로필러로 교착을 추적하기 보다는 추적 플래그 1204를 이용하여 추적하는것을 권장한다.


7. 트랜잭션

DTCTransaction, SQLTransaction, TransactionLog 이벤트 클래스가 있다.

SQLTransaction : 트랜잭션의 시작, 커밋, 롤백, 저장등과 관련된 이벤트


8. 데이터 열

자주 사용하는 데이터 열
EventClass : 캡쳐된 이벤트 클래스를 보여주며, 이 데이터열은 제거할 수 없다.
CPU : 이벤트에 의해 소요된 CPU 사용 시간(밀리 초 단위)
Duration : 이벤트에 의해 소요된 경과 시간(밀리 초 단위)
Reads : 이벤트에 의해 서버에서 수행한 논리적 디스크 읽기수(페이지 단위)
Writes : 이벤트에 의해 서버에서 수행한 문리적 디스크 쓰기수(페이지 단위)
TextData : 캡처된 이벤트 클래스에서 보내진 텍스트 값으로, 실행되는 T-SQL문, 저장 프로시저 또는 일괄 처리의 텍스트를 보여준다.
Binary Data : 캡처된 이벤트 클래스에서 보내진 바이너리 값으로, 해당 이벤트 클래스에서 데이터 열로 요구할때만 추가하면 된다.
ClientProcessID : 호스트 컴퓨터가 클라이언트 응용 프로그램이 실행되고 있는 프로세스에 대해 할당한 ID
SPID : SQL Server가 클라이언트와 관련된 프로세스에 대해 할당한 고유한 ID
          50이하는 시스템, 51이상은 사용자가 사용
Application Name : SQL Server 인스턴스와 연결을 설정한 클라이언트 응용 프로그램의 이름
Host Name : 클라이언트를 실행중인 컴퓨터의 이름
Database ID : 주어진 연결에 대한 현재 데이터베이스의 ID

추적 분석을 할 때 Duration, CPU, Reads, Writes, SPID 또는 ClientProcessID 그룹화하면 좋다.


9. 필터

추적할 데이터 열에 조건을 부여하여 추적 대상을 필터링할 수 있도록 해준다.


10. 사용자 정의 템플릿 만들기

1) 프로파일러에서 파일 -> 새로 만들기 -> 추적 템플릿

2) 추적 템플릿 속성 대화상자의 이벤트 페이지에서 다음과 같은 이벤트 클래스를 선택
    * 저장 프로시저 - RPC:Completed
    * TSQL - SQL:BatchCompleted

3) 데이터 열 페이지에서 다음과 같은 데이터 열들을 선택한다.
    * Event Class
    * Duration
    * CPU
    * Reads
    * Writes
    * TextData,
    * ApplicationName
    * DatabaseID
    * ClientProcessID
    * HostName
    * SPID
    * StartTime

4) 필터 페이지에서 다음과 같이 추적 이벤트 조건을 설정한다.
   * ApplicationName -> 유사하지 않음 -> SQL Profiler

5) 일반 페이지에서 다른 이름으로 저장 버튼을 누른다.

※ 2000 기준이면 2005는 약간 틀리다.(많이 귀찮다..)


11. 추적 결과 분석

추적 결과 데이터를 테이블에 입력해서 추적 결과를 분석할 수 있다.

1) 인덱스 관련 문제 찾기
    Reads, Duration 값이 많이 클경우 인덱스와 관련된 문제일 가능성이 크다.
    Reads > 10000 AND Duration > 10000 ORDER BY Reads DESC

2) 블로킹 찾기
    Reads나 CPU값은 작으면서 Duration 값이 많이 크다면 블로킹일 가능성이 크다.
    Duration > 10000 AND reads < 1000 ORDER BY Duration DESC


12. 추적 재생

추적한 결과를 재실행함으로써 오류나 버그의 재현, 디버깅 등을 가능하게 해줍니다.

추적 재생을 하기 위한 최초 추적을 하려면 프로필러 템필릇으로는 반드시 SQLProfilerTSQL_Replay를 사용하여야 한다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

테스트용 데이터베이스 생성
CREATE DATABASE test
GO

데이터베이스 크기 확인
USE test
EXEC SP_HELPDB test
GO

로그 크기 확인!
DBCC SQLPERF(LOGSPACE)

결과
Database Name   Log Size (MB)   Log Space Used (%)   Status
test                    0.5390625          39.58333                      0

테스트용 테이블 생성
CREATE TABLE t1
(
 A VARCHAR(1000)
)
GO

테스트 데이터 입력
INSERT INTO t1 VALUES (REPLICATE('A',1000))
GO

테스트를 위해서 같은 데이터 반복입력
DECLARE @i INT
SET @i = 0
WHILE @i < 10
BEGIN
 INSERT INTO t1 SELECT * FROM t1
 SET @i = @i + 1
END
GO

데이터베이스 크기 확인
EXEC SP_HELPDB test
GO

백업전 로그 크기 확인
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)    Log Space Used (%)   Status
test                    1.304688             48.35329                      0

정확한 테스트를 위해서 CHECKPOINT 실행
CHECKPOINT

데이터베이스 백업
BACKUP DATABASE test TO DISK = 'D:\SQL2005DB\Backup\test.bak'
GO

데이터베이스 삭제/복원
USE master
GO
DROP DATABASE test
GO
RESTORE DATABASE test FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
GO

데이터베이스 크기 확인
EXEC SP_HELPDB test
GO

USE test
GO

복원후 로그 크기 확인
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)     Log Space Used (%)   Status
test                    1.304688              34.61826                     0

※ 복원 전후의 데이터베이스/로그 파일의 크기는 같지만 로그 크기에서  Log Space Used (%)가 틀리다.
   복원 전:48.35329, 후:34.61826 이걸 보면 백업이 로그를 전부 다 백업하지 않는 다는 것을 알수있다.
   자세한것은 더 찾아봐야 할것 같다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

USE master
GO

데이터베이스 생성
CREATE DATABASE test
ON PRIMARY (
 NAME = 'test',
 FILENAME = 'D:\SQL2005DB\Data\test.mdf'
),
FILEGROUP test_group (
 NAME = 'test_group',
 FILENAME = 'D:\SQL2005DB\Data\test.ndf'
)
LOG ON(
 NAME = 'test_log',
 FILENAME ='D:\SQL2005DB\Log\test.ldf'
)
GO

복구모델을 전체로 변경
※ 복구모델이 싱글이면 파일 그룹 백업이 안된다.
ALTER DATABASE test SET RECOVERY FULL

테스트 테이블 t1, t2 생성
USE test
GO

CREATE TABLE t1
(
 id int,
 name char(3)
)
GO

t2 테이블은 test_group 파일그룹에 생성
CREATE TABLE t2 
(
 id int,
 name char(3)
)
ON test_group
GO

전체백업
BACKUP DATABASE test TO DISK = 'D:\SQL2005DB\Backup\full.bak'


데이터 입력
INSERT t1 VALUES(1,'aaa') 

primary 파일 그룹 백업
BACKUP DATABASE test FILE = 'test' TO DISK = 'D:\SQL2005DB\Backup\test.bak'

데이터 입력
INSERT t1 VALUES(2,'bbb')

test_group 파일 그룹 백업
BACKUP DATABASE test FILEGROUP = 'test_group' TO DISK = 'D:\SQL2005DB\Backup\test.bak'

데이터 입력
INSERT t2 VALUES(22,'ccc')

로그 백업
BACKUP LOG test TO DISK = 'D:\SQL2005DB\Backup\test.log'

데이터 입력
INSERT t2 VALUES(33,'ddd')

테스트를 위해 SQL Server 서비스를 중지하고
D:\SQL2005DB\Data\test.ndf 파일을 삭제한후
다시 SQL Server 서비스 시작

test_group 파일 그룹 복원 시도
USE master
GO

백업파일 확인
RESTORE HEADERONLY FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
GO

test_group 파일그룹 복원 시도
RESTORE DATABASE test FILEGROUP = 'test_group' FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 2, NORECOVERY
GO

에러발생
메시지 3159, 수준 16, 상태 1, 줄 1
데이터베이스 "test"의 비상 로그 백업이 수행되지 않았습니다. 로그에 포함된 작업이 손실되지 않도록 하려면 BACKUP LOG WITH NORECOVERY를 사용하여 로그를 백업하십시오. 로그 내용을 덮어쓰려면 RESTORE 문에 WITH REPLACE나 WITH STOPAT 절을 사용하십시오.
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE DATABASE이(가) 비정상적으로 종료됩니다.

로그 백업
BACKUP LOG test TO DISK = 'D:\SQL2005DB\Backup\test.log'
WITH NO_TRUNCATE

test_group 파일 그룹 복원
RESTORE DATABASE test FILEGROUP = 'test_group' FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 2, NORECOVERY
GO

로그 백업파일 확인
RESTORE HEADERONLY FROM DISK = 'D:\SQL2005DB\Backup\test.log'
GO

로그 복원
RESTORE LOG test FROM DISK = 'D:\SQL2005DB\Backup\test.log'
WITH FILE = 1, NORECOVERY
GO
RESTORE LOG test FROM DISK = 'D:\SQL2005DB\Backup\test.log'
WITH FILE = 2, RECOVERY
GO

복원이 잘되었는지 확인
USE test
GO

SELECT * FROM t1
SELECT * FROM t2
GO

결과
id          name
----------- ----------
1           aaa      
2           bbb      
(2개 행 적용됨)

id          name
----------- ----------
22          ccc      
33          ddd      
(2개 행 적용됨)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

로그 비우는것과 파일을 줄이는것은 가끔식 헷갈리때가 있다.
(나만 그런가? ㅋㅋ)


CREATE DATABASE test
GO

USE test
GO

데이터 가져오기
SELECT * INTO t1
FROM Northwind..[Order Details]
GO

로그 채우기(데이터 입력)
DECLARE @i int
SET @i=1
WHILE @i <20
BEGIN
 INSERT INTO t1
 SELECT *
 FROM Northwind..[Order Details]
 SET @i=@i+1
END
GO

전체 백업전 로그 크기 확인
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)   Log Space Used (%)   Status
test                    1.2421875           56.761005                    0


전체 백업
BACKUP DATABASE test TO DISK='D:\SQL2005DB\Backup\test.bak'
GO

전체 백업후 로그 크기 비교
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)   Log Space Used (%)   Status
test                    1.2421875           42.806602                   0

로그 백업
backup log test to  disk='D:\SQL2005DB\Backup\test.bak'
GO

로그 백업 후 로그 크기 비교
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)   Log Space Used (%)   Status
test                    1.2421875           42.806602                   0

다시 데이터 입력해서 로그 채우기
DECLARE @i int    
SET @i=1
WHILE @i <20
BEGIN
 INSERT INTO t1
 SELECT *
 FROM Northwind..[Order Details]
 SET @i=@i+1
END
GO

로그 크기 확인
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)   Log Space Used (%)   Status
test                    5.0546875           84.804871                    0

로그 비우기(로그만 비운다)
BACKUP LOG test WITH NO_LOG
GO

로그 크기 확인
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)   Log Space Used (%)   Status
test                    5.0546875           30.187403                   0

로그 파일 줄이기
DBCC SHRINKFILE(TEST_LOG,2)

로그 크기 확인
DBCC SQLPERF(LOGSPACE)
GO

결과
Database Name   Log Size (MB)   Log Space Used (%)   Status
test                    1.9921875           30.187403                    0

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

데이터베이스 생성
CREATE DATABASE test
GO

USE test
GO

테이블 생성
CREATE TABLE t1(id int, name char(3))
GO

INSERT INTO t1 VALUES(1,'aaa')
GO

전체백업
BACKUP DATABASE test TO DISK = 'D:\SQL2005DB\Backup\test.bak'
GO

데이터 입력
INSERT INTO t1 VALUES(2,'bbb')
GO

차등백업1
BACKUP DATABASE test TO DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH DIFFERENTIAL
GO

데이터 입력
INSERT INTO t1 VALUES(3,'ccc')
GO

차등백업2
BACKUP DATABASE test TO DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH DIFFERENTIAL
GO

데이터 입력
INSERT INTO t1 VALUES(4,'ddd')
GO

로그백업1
BACKUP LOG test TO DISK = 'D:\SQL2005DB\Backup\test.bak'
GO

데이터 입력
INSERT INTO t1 VALUES(5,'eee')
GO

로그백업2
BACKUP LOG test TO DISK = 'D:\SQL2005DB\Backup\test.bak'
GO

백업 디바이스 헤더 검사
RESTORE HEADERONLY FROM  DISK = 'D:\SQL2005DB\Backup\test.bak'
GO

복원테스트

USE master
GO

원본 데이터베이스 삭제
DROP DATABASE test
GO

전체 백업으로 복원
RESTORE DATABASE test
FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 1, NORECOVERY
GO

가장 마지막 차등 백업 복원(차등백업2)
RESTORE DATABASE test
FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 3, RECOVERY
GO

USE test
GO

SELECT * FROM t1

데이터 확인
id          name
----------- ----
1           aaa
2           bbb
3           ccc
(3개 행 적용됨)

로그 백업 복원 테스트
USE master
GO

원본 데이터베이스 삭제
DROP DATABASE test
GO

전체 백업으로 복원
RESTORE DATABASE test
FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 1, NORECOVERY
GO

가장 마지막 차등 백업 복원(차등백업2)
RESTORE DATABASE test
FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 3, NORECOVERY
GO

차등 백업 이후의 로그 백업 복원(로그백업1)
RESTORE LOG test FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 4, NORECOVERY
GO

차등 백업 이후의 로그 백업 복원(로그백업2)
RESTORE LOG test FROM DISK = 'D:\SQL2005DB\Backup\test.bak'
WITH FILE = 5, RECOVERY
GO

데이터 확인
USE test
GO

SELECT * FROM t1

id          name
----------- ----
1           aaa
2           bbb
3           ccc
4           ddd
5           eee
(5개 행 적용됨)

백업은 항상 시간대별로 파일을 따로 받아서 그런지 한개의 파일로 백업을 받아서 처리한건 약간 생소하다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

백업 디바이스 생성
exec sp_addumpdevice 'disk','Adven1','D:\SQL2005DB\Backup\Adven1.bak'
exec sp_addumpdevice 'disk','Adven2','D:\SQL2005DB\Backup\Adven2.bak'

※ 백업장치 폴더가 없어도 백업 디바이스는 생성됨.
exec sp_addumpdevice 'disk','AdvenError','D:\SQL2005DB\AdvenError\AdvenError.bak'


백업 테스트

정상 수행
BACKUP DATABASE AdventureWorks to Adven1,Adven2

AdvenError 폴더가 없어서 에러 발생 
BACKUP DATABASE AdventureWorks to AdvenError

메시지 3201, 수준 16, 상태 1, 줄 1
백업 장치 'AdvenError(D:\SQL2005DB\AdvenError\AdvenError.bak)'을(를) 열 수 없습니다. 운영 체제 오류 3(지정된 경로를 찾을 수 없습니다.)입니다.
메시지 3013, 수준 16, 상태 1, 줄 1
BACKUP DATABASE이(가) 비정상적으로 종료됩니다.

백업 디바이스를 하나 더 생성
exec sp_addumpdevice 'disk','Adven3','D:\SQL2005DB\Backup\Adven3.bak'
GO

새로만든 백업장치에 AdventureWorks DB 말고 test DB를 백업한다.
BACKUP DATABASE test to Adven3
GO

AdventureWorks 백업할때 format  옵션이 없으면
BACKUP DATABASE AdventureWorks TO Adven1,Adven2,Adven3

메시지 3231, 수준 16, 상태 1, 줄 1
"Adven1(D:\SQL2005DB\Backup\Adven1.bak)"에 로드된 미디어를 2 미디어 패밀리를 지원하도록 포맷했으나 백업 장치의 사양에 따라 3 미디어 패밀리가 필요합니다.
메시지 3259, 수준 16, 상태 1, 줄 1
장치 'Adven3(D:\SQL2005DB\Backup\Adven3.bak)'의 볼륨은 여러 패밀리 미디어 세트의 일부가 아닙니다. BACKUP WITH FORMAT을 사용하여 새 미디어 세트를 생성할 수 있습니다.
메시지 3013, 수준 16, 상태 1, 줄 1
BACKUP DATABASE이(가) 비정상적으로 종료됩니다.

FORMAT 옵션을 사용
BACKUP DATABASE AdventureWorks TO Adven1,Adven2,Adven3
WITH FORMAT

※ FORMAT 옵션은 백업 작업에서 해당 작업에 사용된 모든 미디어 볼륨의 새 미디어 헤더를 기록합니다. 이때 볼륨의 기존 내용은 기존의 모든 미디어 헤더와 백업 세트가 덮어쓰이므로 사용할 수 없게 됩니다.



복원 테스트
DROP DATABASE AdventureWorks
GO

RESTORE AdventureWorks FROM Adven1,Adven2,Adven3
WITH RECOVERY
GO

※ 백업을 할때 여러개의 백업장치에 백업을 하면 백업 속도가 빨리지고 백업장치별로 데이터가 분할 저장된다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

RESTORE FILELISTONLY FROM DISK = 'D:\SQL2005DB\test.bak'
GO
RESTORE HEADERONLY FROM DISK = 'D:\SQL2005DB\test.bak'
GO
RESTORE LABELONLY FROM DISK = 'D:\SQL2005DB\test.bak'
GO
RESTORE VERIFYONLY FROM DISK = 'D:\SQL2005DB\test.bak'
GO

각각의 상세한 정보는 온라인도움말!

그냥 실행결과만 봐도 이게 뭐구나 하고 감이옴

감잡았어~ ㅋㅋ

감이 안온다면 심각하게 고민해봐야...

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

※ 대량 로그 복구모델에서 최소 로깅작업이 있인후 stopat를 사용하는데는 제약이 따른다.
    최소로깅작업후 다른 데이터의 변경후의 시간으로만 stopat를 사용할수 있다.

test 데이터베이스 생성
CREATE DATABASE test
ON PRIMARY(
    NAME = 'test',
    FILENAME = 'D:\SQL2005DB\Data\test.mdf'
)
LOG ON(
    NAME = 'test_log',
    FILENAME = 'D:\SQL2005DB\Log\test_log.ldf'
)
GO

대량 로그 복구 모델로 변경
ALTER DATABASE test SET RECOVERY BULK_LOGGED
GO

복구모델 변경확인
EXEC sp_helpdb test
GO

테스트 테이블 생성
USE test
GO

CREATE TABLE t1 (id INT)
go

데이터베이스 백업
BACKUP DATABASE test TO DISK = 'D:\SQL2005DB\test.bak'
go

1 입력후 현재시각 확인 2008-06-12 10:50:43.793
INSERT INTO t1 VALUES (1)
SELECT GETDATE()        
GO

최소 로깅 작업후 현재시각 확인 2008-06-12 10:50:57.780
SELECT * INTO t2 FROM t1
SELECT GETDATE()        
GO

2 입력후 현재시각 확인 2008-06-12 10:51:24.810
INSERT INTO t1 VALUES (2)
SELECT GETDATE()        
GO

로그 백업을 수행
BACKUP LOG test TO DISK = 'D:\SQL2005DB\test.log'
GO


데이터를 t1 테이블에 1을 입력(2008-06-12 10:50:43.793)하고
최소 로깅작업(2008-06-12 10:50:57.780)을 하기 전인 2008-06-12 10:50:44.000 으로
복구해서 1만 살려냄
USE master
GO
RESTORE DATABASE test FROM DISK = 'D:\SQL2005DB\test.bak' WITH NORECOVERY
GO

RESTORE LOG test FROM DISK = 'D:\SQL2005DB\test.log' WITH RECOVERY, STOPAT = '2008-06-12 10:50:44.000'
GO

에러 발생
메시지 4341, 수준 16, 상태 1, 줄 1
이 로그 백업은 대량 로그된 변경 내용을 포함하고 있습니다. 이 로그 백업은 임의의 지정 시간에서 중지하는 데 사용할 수 없습니다.
메시지 4338, 수준 16, 상태 1, 줄 1
STOPAT 절에서 지정한 지점이 너무 일러서 이 백업 세트를 복원할 수 없습니다. 다른 중지 지점을 선택하거나 RESTORE DATABASE WITH RECOVERY를 사용하여 현재 지점에서 복구하십시오.
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE LOG이(가) 비정상적으로 종료됩니다.


최소 로깅작업(2008-06-12 10:50:57.780)을 하고 2를 입력(2008-06-12 10:51:24.810)하기전인
2008-06-12 10:51:00.000으로 복원시도
RESTORE LOG test FROM DISK = 'D:\SQL2005DB\test.log' WITH RECOVERY, STOPAT = '2008-06-12 10:51:00.000'
GO

에러발생
메시지 4341, 수준 16, 상태 1, 줄 1
이 로그 백업은 대량 로그된 변경 내용을 포함하고 있습니다. 이 로그 백업은 임의의 지정 시간에서 중지하는 데 사용할 수 없습니다.
메시지 4338, 수준 16, 상태 1, 줄 1
STOPAT 절에서 지정한 지점이 너무 일러서 이 백업 세트를 복원할 수 없습니다. 다른 중지 지점을 선택하거나 RESTORE DATABASE WITH RECOVERY를 사용하여 현재 지점에서 복구하십시오.
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE LOG이(가) 비정상적으로 종료됩니다.


2를 입력(2008-06-12 10:51:24.810)한 후로 복원한다면?
2008-06-12 10:52:00.000으로 복원시도
RESTORE LOG test FROM DISK = 'D:\SQL2005DB\test.log' WITH RECOVERY, STOPAT = '2008-06-12 10:52:00.000'
GO

문제 없이 잘된다.
파일 1에서 데이터베이스 'test', 파일 'test'에 대해 24개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'test', 파일 'test_log'에 대해 6개의 페이지를 처리했습니다 .
이 백업 세트에는 지정한 시간 전에 기록된 레코드가 있습니다. 데이터베이스는 더 많은 롤포워드를 수행할 수 있도록 복구 중인 상태로 남습니다.
RESTORE LOG이(가) 30개의 페이지를 0.023초 동안 처리했습니다(10.507MB/초).


DB가 복원 중인 상태기 때문에 복원 완료시킨다.
RESTORE DATABASE test WITH RECOVERY

USE test
GO

t1, t2 테이블 내용확인
select * from t1
select * from t2

결과
id
-----------
1
2
(2개 행 적용됨)

id
-----------
1
(1개 행 적용됨)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

※ 로그백업은 데이터베이스 백업을 받지 않은 상태에서 백업이 불가능하다

test 데이터베이스 생성
CREATE DATABASE test
ON PRIMARY(
 NAME = 'test',
 FILENAME = 'D:\SQL2005DB\Data\test.mdf'
),
FILEGROUP group1(
 NAME = 'test_group1',
 FILENAME = 'D:\SQL2005DB\Data\test.ndf'
)
LOG ON(
 NAME = 'test_log',
 FILENAME = 'D:\SQL2005DB\Log\test_log.ldf'
)
GO


대량 로그 복구 모델로 변경
ALTER DATABASE test SET RECOVERY BULK_LOGGED
GO

확인
EXEC sp_helpdb test
GO

테이블 생성
USE test
GO

CREATE TABLE t1 (id INT)
GO

INSERT INTO t1 VALUES(1)
GO

최소 로깅 작업
SELECT * INTO t2 FROM t1
GO


테스트 삼아 로그 백업을 수행한다.
BACKUP LOG test TO DISK = 'D:\SQL2005DB\test.log' WITH NO_TRUNCATE

메시지 4214, 수준 16, 상태 1, 줄 1
현재 데이터베이스 백업이 없으므로 BACKUP LOG를 수행할 수 없습니다.
메시지 3013, 수준 16, 상태 1, 줄 1
BACKUP LOG이(가) 비정상적으로 종료됩니다.

DB가 깨진걸로 테스트를 하기 위해 서비스를 중지하고 ndf 파일의 이름을 변경하고 서비스 를 다시 시작한다.
로그 백업을 수행한다.
BACKUP LOG test TO DISK = 'D:\SQL2005DB\test.log' WITH NO_TRUNCATE

메시지 4202, 수준 16, 상태 1, 줄 1
데이터베이스에 대량 로그된 변경 내용이 있고 사용할 수 없는 파일 그룹이 하나 이상 있으므로 BACKUP LOG를 사용할 수 없습니다.
메시지 3013, 수준 16, 상태 1, 줄 1
BACKUP LOG이(가) 비정상적으로 종료됩니다.

※ 장애 발생시 일단 BACKUP LOG ... WITH NO_TRUNCATE 로 로그 백업을 받는다.
그러나 로그 백업은 한번이라도 데이터베이스 백업을 받아야지 할수있다. 기본적인걸수도 있지만.. 사고의 원인은 기본적인 경우가 더 많다 ㅋㅋ

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

--YYYY/MM/DD
Select Convert(varchar(10),Getdate(),111)

--YYYYMMDD
Select Convert(varchar(10),Getdate(),112)

--HH:MM:SS
Select Convert(varchar(8),Getdate(),108)

--HH:MM:SS:mmm
Select Convert(varchar(12),Getdate(),114)

--HHMMSS
Select Replace(Convert(varchar(8),Getdate(),108),':','')

--HHMMSSmmm
Select Replace(Convert(varchar(12),Getdate(),114),':','')

--YYYY/MM/DD HH:MM:SS
Select Replace(Convert(varchar(30),Getdate(),120),'-','/')

--YYYY/MM/DD HH:MM:SS
Select Replace(Convert(varchar(30),Getdate(),121),'-','/')

--YYYY/MM/DD HH:MM:SS
Select Convert(varchar(10),Getdate(),111) + Space(1) + Convert(varchar(8),Getdate(),108)

--YYYYMMDDHHMMSS
Select Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')

자주 사용하지만 따로 정리해보는건 첨이군요 ㅋㅋ

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

MS-SQL 2000의 DB를 2005/2008로 업그레이드 한경우 다이어그램을 만들려고 하면


이런 에러가 발생한다 이런경우

※ DB의 호환성수준 맞추기 ( MS-SQL 2005 : 90, MS-SQL 2008 : 100 )
 EXEC sp_dbcmptlevel 'DATABASE_NAME', '100';


※ 유효한 로그인 설정
ALTER AUTHORIZATION ON DATABASE::DATABASE_NAME TO USER_NAME

을 해주면 된다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

select object_name(id) from syscomments
where CHARINDEX('SMS.', [text]) > 0

'SMS' 이부분을 찾을 문자열로 변경

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

select
 A.TableName,
 case
  when B.KeyType = 'PK' Then
   'PK'
  when B.KeyType = 'FK' Then
   'FK'
  else
   ''
 End as KeyType,
 A.ColnumnName, A.Type, A.nullType,
 case
  when isnull(B.defaultValue,'') = '' Then
   ''
  else
   'default'+B.defaultValue
 End AS defaultValue,
 case
  when B.KeyType = 'PK' then
   B.ConstName + ' ( PRIMARY KEY ' +
    case
    when (B.indexType & 16)=16 Then
     'clustered '
    else
     'non-clustered '
   End
   + isnull(A.ColnumnName,'')
   + ' )'
  when B.KeyType = 'UQ' then
   B.ConstName + ' ( UNIQUE ' +
    case
    when (B.indexType & 16)=16 Then
     'clustered '
    else
     'non-clustered '
   End
   + isnull(A.ColnumnName,'')
   + ' )'
  when B.KeyType = 'D ' then
   B.ConstName + ' ' + B.defaultValue
  Else
    isnull(B.ConstName,'')
 End AS ConstName
From
( select
  A.name AS TableName,
   B.name AS ColnumnName,
   C.name +' (' + cast(B.length as varchar) + ')' as Type,
  case
   when B.isnullable = 1 then 'O' else 'X' end As nullType,
  colorder
 from
  dbo.sysobjects A,
  dbo.syscolumns B,
  dbo.systypes C
 where (A.xtype in ('U') AND A.id = B.id) AND (B.xtype = C.xtype AND B.xusertype = C.xusertype)
) A
left join
( select
  object_name(SubA.parent_obj) AS TableName, SubA.xtype AS KeyType,
  case
   when SubA.xtype = 'PK' then
    (select index_col(object_name(SubA.parent_obj), indid, 1)
    from sysindexes
    where name = object_name(SubA.id)
    and id = SubA.parent_obj)
   when SubA.xtype = 'UQ' then
    (select index_col(object_name(SubA.parent_obj), indid, 1)
    from sysindexes
    where name = object_name(indid)
    and id = SubA.parent_obj)
   when SubA.xtype = 'D ' then
    (select name from syscolumns where cdefault = SubA.id)
  End As KeyName,
  SubA.name AS ConstName,
  case
   when SubA.xtype = 'D ' then
    (select text from syscomments where id = object_id(SubA.name))
   else
    ''
  End AS defaultValue,
  ( select status
    from sysindexes
    where name = object_name(SubA.id)
    and id = SubA.parent_obj ) AS indexType
 from sysobjects SubA
 where SubA.xtype in ('C ','PK','UQ', 'D ')
) B
on A.TableName = B.TableName AND A.ColnumnName = B.KeyName
order by A.TableName ASC, A.colorder ASC

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

세미나에서 몇가지 장애 현상과 대응방안이 소개 되었으나 3가지만 정리 했습니다.

1. File Autogrow

   MDF/LDF 파일이 확장되면서 시스템의 부하가 오는 경우
   SQL 2000에서는 MDF파일 확장시 MDF파일을 초기화(포멧)을 하면서 증가해서 부하가 발생하여 문제가 되었으나 SQL 2005에서는 MDF파일 확장시 MDF파일을 단순하게 초기화 하여 파일증가시 부하가 크지 않아 문제가 되지 않으나 LDF파일은 초기화를 하기 때문에 부하가 발생합니다.


2. 서버 재시작시 DB가 올라오지 않는경우

   서버 재시작후 리커버리를 실행하면서 죽지전에 Commit 되지 않았던 트랜젝션의 롤백을 처리하는데 여기서 문제가 발생한 경우입니다.
   
   SSMS에서 로그파일에서 리커버리 완료 %를 확인해서 어느정도 복구가 되었는지 확인 합니다.

   VLF(버츄얼 로그 파일) : 하나의 LDF파일가 있으면 그안에 여러개의 VLF가 있씁니다.
                           VLF가 많으면 DB가 올라올때 VLF을 확인하기 때문에 문제가 될수도 있습니다.


3. 백신 실행시

   SQL Data 부분은 제외하고 검새하는것이 좋습니다. 문제가 발생의 요지가 있습니다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. MPSReport

   사용자 컴퓨터에 대한 중요 시스템, 진단 및 구성 정보를 캡처하는 데 사용할 수 있는 소프트웨어 패키지
   (각 부분별로 나누어져 있습니다., SQL, Network, cluster ...)
 
   1) Server의 구성 정보 수집 항목
     - System/Application Event Logs
     - SQLDiag Log
     - SQL Setup Logs
     - System Information
       Registry, dll/exe files, drivers...

   2) 출력물 (구성 정보 수집 파일)
     - %SYSTEMROOT%\MPSReports\SQLServer\rpt\cab 디렉토리에 4MB 단위로 파일이 생성됩니다.
       ( %SYSTEMROOT% = C:\WINDOWS )


2. PSSDiag/SQLDiag

   1) PSSDiag ( SQL 2000에서 사용, 별도로 설치 해야함 )
 
   2) SQLDiag ( SQL 2005에서 사용, SQL 2005와 같이 설치됨 )
     - %Program Files%\Microsoft SQL Server\90\Tools\Binn\sqldiag.exe
   
   3) 수집 정보
     - Windows Performansce Logs
     - Windows Event Logs
     - SQL Server Profiler Traces
     - SQL Server Blocking Information
     - SQL Server Configuration Information

    ※ 초기에 설치하면 위의 모든 정보가 수집되지 않는다.
       sqldiag.xml 파을을 열어서 수집할 정보를 설정 해주어야 한다.


3. ReadTrace

   ※ 수집된 Profiler Trace 분석 Tool로서 실제 서비스 환경에서는 실행하지 마십시오.

   1) 사용방법
      Read80Trace -|"trc 파일" -o"분석데이터 출력위치"
   
   2) 출력물
     - Rollup by Duration
     - Rollup by CPU
     - Rollup by Read/Write

   ※ 통계 데이터를 만들기 위해 DB를 생성하고 데이터를 출력하기 때문에 한개의 통계를 생성하고
      다른로그 분석을 하게되면 통계데이터가 변경됩니다.



4. Performance Dashboard

   ※ SQL 2005 SP2 부터 사용가능
       서버가 재시작 되면 초기화 되어 추세 이력등을 남기기 위해서는 DMVStats를 사용하여 보완 하는것이 좋습니다.

   1) 다운로드
      http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

   2) 설치 & 설정
      다운로드 받은 설치 파일을 설치한 후 %Program Files%\Microsoft SQL Server\90\Tools\PerformanceDashboard 디렉토리의
      setup.sql 파일을 SQL Server Management Studio 에서 실행합니다. (대상 서버에 연결된 상태에서)

      데이터 베이스의 보고서 -> 사용자 지정보고서를 추가


     %Program Files%\Microsoft SQL Server\90\Tools\PerformanceDashboard\performance_dashboard_main.rdl 파일을 사용자 지정 보고서로 추가합니다.




     실행화면



5. DMV (Dynamic Management View)

   동적관리뷰(DMV)는 SQL Server 2005에서 새롭게 등장한 모니터링에 대한 시스템 뷰입니다.

   ※ 서버가 재시작되면 모든 정보가 초기화 됩니다.
      현재 시점(현재까지의 누적)의 성능정보만 제공됩니다.

   1) Server Level
     - sys.dm_exec_* : Execution of user code and associated connections
     - sys.dm_os_* : Memory, locking & scheduling
     - sys.dm_tran_* : Transactions & isolation
     - sys.dm_io_* : I/O on network and disks
     - sys.dm_db_* : Databases and database objects
 
   2) Commponent Level
     - sys.dm_repl_* : Replication
     - sys.dm_broker_* : SQL Server Broker
     - sys.dm_fts_* : Full Text Search
     - sys.dm_qn_* : Query Notifications
     - sys.dm_clr_* : Common Language Runtime



6. DMVStats

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. DETECT
  문제 발생에서 부터 해결까지의 단계를 DETECT라고 합니다.

  - Discover the problem : 문제 발생
  - Expoler the boundaries : 원인 확인
  - Track the possible approaches : 문제 해결을 위해 가능한 모든 방법 확인
  - Execute an approach : 어떤 방법으로 문제를 해결한것인지 결정 ( 경우의 수를 정한다. )
  - Check for Sucess : 정상적으로 문제가 해결 되었는지 확인
  - Tie up looseends : 문제 해결후 모니터링 기간을 두고 각 단계에 대한 문서 작업후 보고


2. BaseLine
 
  문제(장애) 발생시 평소에 서버의 상태가 어떤 상태였는지(어느정도 성능을 가지고 있고 어느정도의 시스템 자원 사용이 있었는지)를 기준으로 장애 상태와 해결 방안을 결정하는데 기준이 되는 데이터

  * 판단 기준이 되는 시점(특정 일자 혹은 특정 기간동안)의 시스템의 성능 데이터를 기준으로 평균 성능 지표로 구성
  * 추세(Trend), 이력관리 등 여러 시점의 베이스라인 필요(기준, 결산)

  1) 적용절차
     * 얼마나 느려졌나? 원래는 얼마 정도였나?
     * 베이스라인과 달라진 것이 무엇이 있나?
     * 문제의 원인은 무엇이고, 그에 대한 대응방안은 무엇인가?
     * 문제해결을 위해 소요예측시간은 얼마인가?
     * 문제해결 후 보고를 위한 데이터는 이력관리되는가

  2) 성능 모니터 (PERFMON.EXE)
     전반적인 성능 정보를 제공해 주는 종합검진 도구

    2-1) 모니터링 대상 성능카운터(시스템)
         시스템 리소스에 대한 성능 정보를 수집

         CPU
          - PROCESSOR:%Processor Time
          - PROCESS:%Processor Time(sqlservr)
          - PROCESSOR:%User Time, Privileged Time
          - SYSTEM:Processor Queue Length
          - SYSTEM:Context Swithes/sec

         MEMORY(vs. SQL Server Buffer Manager)
          - Available MBytes
          - Pages/sec, Page Faults/sec

         DISK
          - Avg. sec/Read, Avg. sec/Write
          - Avg. Disk Queue Length


    2-2) 모니터링 대상 성능카운터(SQLServer)
         SQL Server 관련 리소스에 대한 성능 정보를 수집

         Workload
          - SQLStatistics:Batch Requests/sec
          - SQLStatistics:SQL Compilations/sec
          - SQLStatistics:SQL Re-compilations/sec
          - General Statistics:User Connections
          - Databases:Transactions/sec

         Lock & Latch
          - Locks:Average Lock Wait Time(ms)
          - Locks:Lock Requests/sec
          - Locks:Lock Waits/sec
          - Latches:Average Latch Wait Time(ms)
          - Latches:Latch Waits /sec

         Access Methods
          - Access Methods:Index Searches/sec
          - Access Methods:Full Scans/sec
          - Access Methods:Forward Records/sec
          - Access Methods:Page Splits/sec

         Buffer Mgr. & Plan Cache & Memory Mgr.
          - Buffer Manager:Buffer Hit Ratio
          - Buffer Manager:Page Life Expectancy
          - Buffer Manager:Checkpoint/sec
          - Buffer Manager:Lazy writers/sec
          - Plan Cache:Cache Hit Ratio
          - Memory Mgr:Memory Grant Pending

    2-3) 성능카운터간의 상호연관성 분석
         성능카운터를 상호비교함으로써 새로운 성능정보 제공

         CPU 이슈
           Kernal Mode vs. User Mode
           Processor:%Processor Time vs. Process :%Processor Time(sqlservr)

         컴파일 및 재컴파일 이슈
           Batch Requests/sec vs. SQL Compilations/sec
           SQL Compilations/sec vs. SQL Re-compilations/sec

         디스크 이슈
           Disk Queue Length vs. Disk sec/Transfer

         메모리 이슈
           Page Life Expectancy, Checkpoints/sec, LazyWrites/sec

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

SQL 2000 에서 테이블/프로시져 등을 스크립트 파일로 만들때
아래의 그림처럼 SQL 스크립트 생성에서 옵션을 주면된다.



2005 에서는 SQL 스크립트 생성이 테이블 스크립팅, 저장프로시저 스크립팅 등의 스크립팅으로 바뀌었다.

단순히 이름만 스크립팅으로 바뀐것은 아니라 CREATE, DROP, ALTER, SELECT, INSERT, UPDATE 등의 SQL문을 만들수 있는 기능도 추가 되었다.

문제는 테이블, 저장 프로시져를 CREATE 스크립팅을 해도 권한, 인덱스등의 정보가 나오지 않는다.

SQL 2000과 달리 2005에서는 스크립팅 옵션을  메뉴 -> 도구 -> 옵션 에서 지정해 줘야한다. 단지 메뉴의 위치만 이동한것 뿐인데.. 의외로 찾기 힘들었다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. 디스크의 read/write의 속도

2. 내결함성 ( 하나의 요소가 실패가 됐을때 다른 잉여요소가 그 역활을 대신할수 있는것 RAID )

3. 데이터베이스 파일과 로그 파일의 저장 위치
   (파일, 로그를 별도의 디스크에 저장하는것을 권장)

4. 파일구릎을 사용할때 각 파일의 저장위치
   (각각의 파일을 별도의 디스크에 저장하는것을 권장)

5. tempdb가 db에서 기본으로 사용되는 db이가 때문에 빠른 디스크에 만들어 주는것도 고려해 볼 만함

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

데이터베이스를 운영하다 보면 데이터베이스의 크기보다 실제 데이터가 작은경우가 있다

예를 들어서 데이터베이스의 크기난 10메가인데 실제로 사용하는 부분은 2메가 인경우
10메가는 용량이 작다고 치지만..
데이터베이스의 크기가 10기가인데 사용하는 용량은 1기가라면..
디스크 용량이 널널하다면 모르겠지만 디스크 공간 확보를 위해서 데이터베이스 크기를
줄여야하는 경우가 있다.

DBCC SHRINKDATABASE( DB이름, 여유공간% )
여유공간% : 데이터베이스의 남길 여유공간%
처음 데이터베이스를 생성했을때의 크기보다 작게 줄일수는 없다.

DBCC SHRINKFILE( DB이름, 줄일 사이즈 )
처음 데이터베이스를 생성했을때의 크기보다 작게 줄일수 있다.

유연성면에서 DBCC SHRINKFILE 가 더 좋다.

자세한 부분은 온라인 설명서를 참고!

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

최대 : 모든 트랜젝션과 관련된 로그를 다 기록하기 때문에
         문제 발생시 모든 데이터를 원하는 시점으로 복구 할수 있다.
대량 로그 : 문제 발생 직전까지는 복구할 수 있지만 '최대' 처럼 특정 시점으로는 복구 할수 없다.
단순 : 문제 발생시 백업받았던 시점까지만 복원할 수 있다.

최대를 권장한다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

트랜젝션 로그는 데이터의 수정이 발생될때 기록이 된다.

1. 데이터의 수정된 내용이 DB서버 로 전달

2. 수정하고 적용될 데이터 페이지가 캐쉬상에 없을경우
   데이터를 읽어서 버퍼 캐쉬라는 메모리에 로딩

3. 변경된 로그를 데이터 페이지에 저장하기 전에 트랜젝션 로그에 저장

4. 정기적으로 체크포인트가 발생하면 로그에 기록된 내용을 데이터와 로그 파일에 기록

※ 트랜젝션 로그를 저장한 후 저장된 로그에 해당되는 내역을 데이터에 기록한다.

DATA 저장 프로세스

check point : 주기적으로 발생하며 dirty page의 내용을 데이터와 로그 파일에 기록
lazy writer : 체크포인트와 기능이 동일 하며 다만 캐시가 가득찬 경우에만 동작함
log writer : 트랜젝션이 완료되면 자동으로 커밋된 로그를 로그 파일에 기록

dirty page : 하드디스크의 내용과 메모리 상에 있는내용이 일치하지 않은 정보

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

MS-SQL이 저장되는 단위는 페이지

페이지의 크기는 8KB ( 1024 * 8 = 8192 btyes ) 이지만

페이지에서 데이터를 저장할 수 있는 maximum row size 는 8090 bytes 다.

페이지는 크게 3가지 부분으로 나누어진다.

header 부분 (96 bytes) :이전페이지와 다음페이지의 정보 저장

Data rows 부분 (8090 bytes) : 실제 데이터 저장부분

offset 부분 : 각 행의 첫번째 바이트가 페이지의 시작부분에서 얼마나 멀리 떨어져 있는지를 저장

페이지가 연속적으로 8개가 모이면 Extent라는 저장공간으로 활용된다.

Extent : 테이블, 인덱스가 저장되는 기본단위

1MB당 16개의 extent 가 존재할 수 있다.

single extent (단일) : 테이블면 테이블, 인덱스면 인덱스 하나의 단일한 데이터가 저장된 상태
mix extent (혼합) : 테이블과 인덱스등의 하나이상의 데이터베이스의 오브젝트들이 같이 저장된 상태

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.