Code Complete

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

개선 방법은 대부분 인덱스 수정 (순서 변경, 추가, 삭제)

더 좋은 해결 방법이 있다면 서버 비지니스 로직 변경 + 인덱스 & 쿼리 튜닝

Loop 쿼리는 One 쿼리로 변경 후 서버 비지니스 로직 변경 등..

내용 : 카드 삭제

문제 : 클러스터형 인덱스 파편화 심화 유발, 스레드당 최대 30번의 Execute Call 발생

개선방향 : 삭제 플래그 형식으로 변경, 삭제코드를 array로 넘긴 후 파싱 Execute Call 감소

쿼리 수정

-    삭제 대상 시퀀스를 문자열로 조합 후 쿼리에 전달 해당 쿼리는 파싱 후 처리

-    수정 후 Execute Call 은 스레드당 무조건 한 번

내용 : 경험치 증/감

문제 : 조건문 적용 순서 오류, 불필요한 SELECT, 스키마 누락

개선 방향

-    조건문을 직관적으로 변경

-    UPDATE 구문 수정으로 첫번째 SELECT 제거

-    스키마 추가

결과

-    SELECT문 2 -> 1

-    수행 시간 12% 감소

내용 : 업그레이드 카드 목록 얻기 (레전드 만들기)

문제 : 랜덤 액세스 발생이 심각함

개선 방향 : 랜덤 액세스 제거 & 정렬 제거, 비클러스터형 인덱스 수정

결과

-    랜덤 액세스 제거

-    인덱스 순서를 변경하면 정렬 제거가 가능하다. (CharacterNO ASC 두 번째로)

-    해당 쿼리에 맞는 인덱스 추가 가능 ( INSERT I/O 비용 0.01 증가 )

내용 : 업그레이드 카드 목록 얻기 (특정 클래스만

문제 : 랜덤 액세스 발생 심각함

개선 방향 : 랜덤 액세스 제거 & 정렬 제거, 비클러스터형 인덱스 수정

결과

-    랜덤 액세스 제거

-    정렬 제거 (특정 클래스만 탐색하기 때문에 가능)

-    처리 비용 89% 감소

내용 : 캐릭터 카드 목록을 얻는다.

문제 : 랜덤 액세스 심각, 불필요한 업데이트 구문, 정렬

개선 방향 : 랜덤 액세스 제거, 불필요한 구문 제거, 비클러스터형 인덱스 수정

결과

-    랜덤 액세스 제거

-    UPDATE 구문 제거

-    처리 비용 46% 감소  (정렬 제거 시 58% 감소)

내용 : 강화 재료 카드 목록을 얻는다.

문제 : 보유 수량만큼 INNER JOIN, 클러스터형 인덱스와 조인, 랜덤 액세스, 정렬

       총 네 번의 INNER JOIN이 발생

개선 방향 : 불필요한 JOIN, 랜덤 액세스, 정렬 제거,  인덱스 수정

결과

-    INNER JOIN 감소 ( 4 -> 1)

-    랜덤 액세스 제거

-    정렬 제거

-    처리 비용 88% 감소

튜닝전 UPS_GetCharacterCardList_Enchant 실행 계획

튜닝 후 UPS_GetCharacterCardList_Enchant 실행 계획

운영툴 대회채널 결과 페이지 튜닝전 실행 계획

운영툴 대회채널 결과 페이지 튜닝 후 실행 계획

파란 박스로 체크한 결과가 튜닝 후 쿼리 실행 시 결과를 얻는데 걸리는 시간(ms)

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

Page

 I/O가 이루어 지는 단위, 데이터 R/W 논리적 단위

하나의 레코드에서 하나의 칼럼만을 읽으려고 해도 레코드가 속한 페이지 전체를 읽는다.

SQL 성능 지표 중 액세스하는 Page 수는 매우 중요하다. 옵티마이저의 판단에 가장 큰 영향을 준다.

MSSQL Page 크기는 8KB

Extent

 파일그룹으로부터 공간을 할당하는 단위

테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 파일그룹으로부터 추가적인 공간을 할당 받는데 이때, 정해진 익스텐트 크기의 연속된 블록을 할당 받는다.

예를 들어 8KB Page인 상태에서 64KB 단위로 익스텐트를 할당하도록 정의했다면, 공간이 부족할 때마다 파일그룹으로부터 8개의 연속된 페이지를 Heap/Index 구조 오브젝트에 할당해 준다. 익스텐트 내의 페이지는 인접하지만 익스텐트끼리는 인접하지 않는다.

MSSQL은 8페이지 익스텐트만 사용하고 페이지는 8KB 고정이기 때문에 익스텐트의 크기는 항상 64KB 이다.

Heap/Index 구조 오브젝트 (세그먼트)

 테이블, 인덱스처럼 저장 공간을 필요로 하는 데이터베이스 오브젝트

저장 공간을 사용한다는 것은 한 개 이상의 익스텐트를 사용한다는 뜻이다.

File Group

 세그먼트를 담는 컨테이너, 여러 데이터 파일로 구성된다.

임시 데이터 파일

 대량의 정렬이나 해시 작업 수행 중에 메모리가 부족해지면 중간 결과 집합을 저장하는 용도로 사용한다. MSSQL은 하나의 tempdb 만 사용한다. 전역 리소스이다.

트랜잭션 로그

 버퍼 캐시에 가해지는 모든 변경 사항을 기록하는 파일로 데이터베이스마다 하나씩 생기며 확장자는 .ldf 이다.

Fast Commit 매커니즘^[각주:1]을 사용한다. 

내부적으로 '가상 로그 파일' 이라 불리는 더 작은 단위의 세그먼트로 나뉘며, 세그먼트가 너무 많아지지 않도록 (조각화) 옵션을 지정하는게 좋다.

예를 들어, 애초에 넉넉한 크기로 만들어 자동 증가가 발생하지 않도록 하거나, 어쩔 수 없이 자동 증가한다면 증가하는 단위를 크게 지정하는 것이 좋다.

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

아이템 DB의 인덱스와 쿼리를 튜닝해서 실행 비용을 90% 감소 시킨 노하우를 팀과 공유하기 위한 노트

PK 인덱스 특성을 고려한 데이터 베이스 성능 향상 도모

B-트리 구조적 특징에 따라 설계에 반영해야 할 요소를 확인할 필요가 있다.

PK 설계는 데이터를 접근할 때 경로를 제공하는 성능의 측면에서 매우 중요한 의미를 갖는다. 때문에 설계 마지막에 컬럼의 순서를 조정할 필요가 있다.

PK가 복합식별자 일 때

순서 결정 기준 – 인덱스 정렬 구조를 이해한 상태에서 인덱스를 효율적으로 사용할 수 있도록 PK 순서를 지정한다. 복합 인덱스의 경우 앞쪽에 위치한 값이 최대한 필터링 가능해야 한다.

(점 조건 + 점 조건 + ……+ 선분 조건)

옵티마이저와 실행계획

옵티마이저 - SQL문에 대해 최적의 실행 방법을 결정하는 역할을 한다.

최적의 실행방법을 실행 계획이라고 한다. (Execution Plan)

가장 적은 비용으로 계획을 만들기 위해 노력한다.

관계형 데이터 베이스는 SQL문을 통해서만 데이터를 처리할 수 있는데 사용자의 요구사항만 기술할 뿐 처리 과정에 대한 기술은 하지 않는다. 때문에 요구사항을 만족하는 다양한 실행 방법이 존재할 수 있다.

DB는 옵티마이저가 결정한 실행 방법대로 실행 엔진이 데이터를 처리하여 결과 데이터를 사용자에게 전달할 뿐이다.

MSSQL의 비용기반 옵티마이저 (CBO, Cost Base Optimizer)

규칙 기반 옵티마이저는 호환성을 위해서만 남아있다.

질의 변환기

-        SQL문을 처리하기에 더욱 용이한 형태로 변환한다.

대한 계획 생성기

-       동일한 결과를 생성하는 다양한 대안 계획을 만든다.

-       인덱스 연산의 적용 순서, 연산 방법, 조인 순서 변경

-       대안 계획이 많아지면 최적화 수행 시간이 길어지기 때문에 계획의 수를 제한한다.

-       최적의 계획이 포함되지 않을 수도 있다.

비용 예측기

-       계획의 비용을 예측하는 모듈

B-트리 인덱스 구조

링크 화살표 중요

최상단은 Root Block이라 한다.  Branch Block은 분기를 목적으로 하며 하위 블록의 포인터를 갖는다. Leaf Block은 인덱스를 구성하는 컬럼 데이터와 레코드 식별자를 갖는다.

Leaf Block의 양방향 링크는 오름차순, 내림차순 정렬을 쉽게한다.

B-트리 인덱스 검색 방법

1. 37을 검색한다.

2. Root Block 좌측 값보다 작거나 같으면 좌측으로 이동한다.

3. Branch Block 좌측 값보다 작거나 같으면 좌측, 크면 우측, 사이값이면 가운데로 이동한다.

4. Leaf Block 이면 찾으려는 값을 검색한다.

5. Leaf Block에 값이 존재하면 레코드 식별자를 이용하여 해당 테이블을 읽는다.

디스크 I/O 부하

db file sequential read 

 : single block I/O 대기 이벤트, 한번의 I/O call 에 하나의 데이터 블록만 읽는다. 인덱스를 읽을 때, 인덱스를 거쳐 데이블에 액세스 하는 경우 발생

db file scattered read

 : multiblock I/O 대기 이벤트, I/O call 이 필요한 시점에 인접 블록을 같이 읽어 메모리에 적재한다. table full scan or index fast full scan 인 경우 발생

비클러스터형 인덱스 탐색 순서

- 클러스터형 인덱스가 없는 경우 Leaf block으로 이동 후 RID 를 이용해서 데이터 접근

- 클러스터형 인덱스가 있는 경우 Leaf block으로 이동 후 클러스터형 인덱스를 이용해서 데이터 접근

간단히 설명하면 예를 들어 클러스터형 인덱스 컬럼이 A, 비클러스터형 인덱스 컬럼이 B, C 일 때

비클러스터형 인덱스 구성은 B, C, A 이다. 비클러스터형 인덱스 뒤엔 항상 클러스터형 인덱스가 붙는다.

테이블 스캔을 이용할 때는 순서대로 조건에 맞는 데이터를 찾는다.

비클러스터형 인덱스 구성 CN

SELECT 조건절이 CN AND ITemCode 인 경우 데이터 추출 과정

랜덤 액세스가 발생하면서 낭비가 발생

인덱스 수정으로 랜덤 액세스가 제거된 추출 과정

추가로 인덱스 단편화가 심할 때 상대적으로 SELECT 성능이 20% 정도 감소를 보였음

데이터 베이스를 신규로 생성한 후 게임 서버나 웹에서 접근이 되지 않는 경우 

해당 DB의 보안 -> 로그인 메뉴를 이용하여 접근 권한 설정을 해줄 필요가 있다.

신규 데이터 베이스에 로그인 되는 것을 확인한 후에는 

데이터 베이스 소유자와 접근해서 사용하는 계정이 다른경우 SP에 대한 실행 권한을 별도로 설정해주면 된다.

SQL 서버의 기본 격리 수준(isolaton level^[각주:1])은 read committed^[각주:2] 이다. 그래서 데이터를 읽을 때는 shared lock이 걸린다.

쉽게 설명하면 SELECT 문이 실행 중인 동안에 같은 row혹은 table에 INSERT나 UPDATE하는 작업은 block된다. 거꾸로 같은 row, data page 혹은 table에 데이터를 INSERT, UPDATE하는 동안 SELECT문은 block 된다. 이렇게 되면 DB 성능이 떨어지게 되기 때문에 일반적으로 단순 SELECT 문에는 WITH(NOLOCK) 구문을 추가한다.

 가끔 해당 테이블에 데이터가 얼마나 쌓여있는지 알고 싶을 때가 있는데 천만에서 억단위로 데이터가 쌓여있는 경우 COUNT(*) 를 사용하는 것도 부담스러운 경우가 있다. 1분 이상 기다리는 경우가 허다하다. 그럴경우 아래 쿼리를 한번 사용해보자.

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table name') AND indid < 2

참고 링크 

 http://technet.microsoft.com/ko-kr/library/ms190283.aspx

우리가 수행하는 SQL은 변한다.

- SQL이 변형되더라도 동일한 데이터가 추출되는 것은 데이터베이스 옵티마이저가 보장한다.

!! SQL이 수행되는 방법

1. 구문분석

- 동일한 SQL의 수행 여부 확인

 ( 동일한 SQL이 이전에 수행되었는지 메모리에서 확인 후 재사용 - 소프트 파싱

 ( 메모리에 실행 정보가 존재하지 않는다면 구문 분석을 새로  시작 - 하드 파싱

- 문법/철자 확인

 ( 문제 발생시 syntax 에러 메시지 발생

- Semantic 확인

 ( SQL에 사용된 테이블과 테이블의 컬럼이 실제 데이터베이스에 존해하는 지를 확인. Database Resolution 단계라고도 표현.

- 권한 확인

 ( 권한 에러 

!! SQL은 옵티마이저에 의해 변경된다.

Query Transformation - 구문 분석을 수행하는 중간에 해당 SQL을 변경하는 단계, 옵티마이저 단독 실행

- Transitivity (실행계획)

 ( 논리적으로 이상이 없는 조건을 추가하여 처리 범위를 감소시켜 성능을 최적화

 ( 점(=) 조건은 가능, 선분 조건 불가능, 조인 조건 불가능

- 뷰 Merging

- 서브쿼리 Merging

- OR Expansion(확장)

- Query Rewrite

SQL 명령문을 작성할 때 고려해야하는 사항들을 몇가지 정리..

<=, >= 대신 BETWEEN 연산자를 사용

 범위에 포함된 값을 찾기 위해 WHERE 절에 <=, >= 연산자를 사용한다면 일반적으로 인덱스를 사용하지 않는다.

 SELECT name, height FROM userTbl WITH(NOLOCK) WHERE height >=  180 AND height <= 183

 -> SELECT name, height FROM userTbl WITH(NOLOCK) WHERE height BETWEN 180 AND 183

OR 연산자 대신 IN 연산자를 사용

 조건에 OR 연산자를 가지고 있다면 대부분의 경우 인덱스를 사용하지 않는다.

 SELECT name, height FROM userTbl WITH(NOLOCK) WHERE height =  180 OR height = 183 OR height = 184

 -> SELECT name, height FROM userTbl WITH(NOLOCK) WHERE height IN ( 180, 183, 184)

NOT 연산자를 피해라.

 조건에 NOT 연산자를 가지고 있다면 일반적으로 인덱스를 사용하지 않는다.

 WHERE NOT (height < 1980) 

 -> WHERE height > 1979

나머지는 다음에..

프로시저, 트리거, 함수 또는 일괄 처리와 같은 모듈입니다. 따라서 두 문이 같은 저장 프로시저, 함수 또는 일괄 처리에 있으면 같은 범위에 있는 것입니다.

SCOPE_IDENTITY, IDENT_CURRENT 및 @@IDENTITY는 ID 열에 삽입된 값을 반환하기 때문에 비슷한 함수입니다.

IDENT_CURRENT는 범위와 세션으로 제한되는 것이 아니라 지정된 테이블로 제한됩니다. IDENT_CURRENT는 임의 세션 및 범위에 있는 특정 테이블에 생성된 값을 반환합니다. 자세한 내용은 IDENT_CURRENT(Transact-SQL)를 참조하십시오.

SCOPE_IDENTITY 및 @@IDENTITY는 현재 세션의 테이블에서 생성된 마지막 ID 값을 반환합니다. 그러나 SCOPE_IDENTITY는 현재 범위 내에 삽입된 값을 반환합니다. @@IDENTITY는 특정 범위로 제한되지 않습니다.

예를 들어 두 개의 테이블 T1과 T2가 있고 T1에 INSERT 트리거가 정의되어 있다고 가정합니다. T1에 행이 삽입될 때 트리거가 발생하고 T2에서 행을 삽입합니다. 이 경우 T1에서의 삽입과 트리거에 의한 T2에서의 삽입이라는 두 범위가 해당됩니다.

T1과 T2에 모두 ID 열이 있고 @@IDENTITY 및 SCOPE_IDENTITY가 T1에서 INSERT 문 끝에 다른 값을 반환한다고 가정해 봅시다. @@IDENTITY는 현재 세션의 범위에서 삽입된 마지막 ID 열 값을 반환합니다. 이 값은 T2에 삽입된 값입니다. SCOPE_IDENTITY()는 T1에 삽입된 IDENTITY 값을 반환합니다. 이 값은 같은 범위에서 발생한 마지막 삽입 값입니다. 범위에서 ID 열에 INSERT 문이 발생하기 전에 SCOPE_IDENTITY() 함수가 호출되면 이 함수는 Null 값을 반환합니다.

문 및 트랜잭션이 실패해도 테이블의 현재 ID가 변경되고 ID 열 값 간에 간격이 생성될 수 있습니다. 테이블에 값을 삽입하려고 시도한 트랜잭션이 커밋되지 않아도 ID 값은 롤백되지 않습니다. 예를 들어 IGNORE_DUP_KEY 위반으로 인해 INSERT 문이 실패해도 테이블의 현재 ID 값은 계속 증가합니다.