SQL 서버의 튜닝

- 튜닝이란...

** 대부분의 튜닝은 인덱스에 문제가 있다.(병목현상을 찾아서 해결 하는 경우가 대부분이다)
-> 병목현상 해결은 하드웨어는 한계가 있으므로 튜닝을 해야한다.
튜닝은 인덱스(색인)을 잘 타서 통제하는 것이라고 볼 수 있다.

 

 

튜닝을 쓰는 이유!!!!!!!!!! : 

DB서버의 응답시간과 처리량에 대하여 요구사항에 맞게 빠르게 처리하기 위해서 튜닝을 한다.

 

튜닝방법
1. 네트워크 : 성능좋은 것과 트래픽(전달해주는 데이터라고 봄)을 줄인다.
랜케이블은 에러가 많이나므로 서버 랜케이블은 아주 좋은것을 쓰는경우가 많음
2. 디스크 속도 및 I/O 속도(낮춤)
3. CPU 처리량(낮춤)

 

 

- 튜닝을 하는 방법

1. SQL Server Profiler
-> 쿼리 튜닝/잠금 을 실행함

2. Windows 기본 도구 사용
-> 성능모니터 실행

3. 쿼리 분석기
-> 예상 실행계획/ 실제 실행계획을 분석함

4. T-SQL 사용
-> DMV나 시스템 저장 프로시저를 확인

 

 

 

 

튜닝 중점 포인트
1. 응답시간(Response Time)
2. 처리량(Throughput)

업무로직에 맞추어 설계가 잘 되어있으면 2번이 증가한다. 

사용자 -> 웹 서버 -> WAS(MIDDLE WARE) -> DB서버
1번--------------여기까지.  2번-- <->
사용자 <- 웹 서버 <- WAS(MIDDLE WARE) <- DB서버


모니터링(측정)
- 튜닝을 할 때 먼저 해야할 것
1) 모니터링
2) 자료확보
3) 분석 -> 기초자료(기준점)이 나와야 한다 :Baseline -> 해당 하드웨어/시스템에서의 속도를 측정하고 증명한다
4) 수정
5) 모니터링
6) 자료확보
7) 분석 후 확인
* 기준점을 먼저 잡아야하는이유???  문제가 생겼을 때, 또는 튜닝을 할 때 어떤것을 해야 할지 알기 위해서,
해결 후 성능 증가한 것을 확인하기 위해서.


어떻게 해야할 지 전략을 수립한다.

- 자신만의 방법과 엔진이 제공해주는 방법을 확인한다.
- 병목현상을 찾는 것 
: 어디서 데이터를 많이 가져오면서 멈춤이 있는 곳을 찾는다.
일반적으로 디스크에대한 병목현상이 대부분이므로 해결방법으로는 SSD가 있다.

중점을 두어야 할 곳을 찾기
- 대부분 하드웨어(H/W)디스크 때문에 병목현상이 일어나는 경우가 많은데 SSD를 바꾸면 된다 :예산 많으면 됨
- OS튜닝
- SQL OS 튜닝
- 데이터베이스 튜닝(디스크 위치, 파일 크기조정, 로그파일, 인덱스 등등을 조절함)
- App 튜닝(쿼리 자제할것 - 저장프로시저(플랜캐시 포함) 사용, 커서 또는 트리거 자제할것- 이 두개는 CPU를 매우많이잡아먹는다)

 

 

 

 

 

 

열단위로 생성되어있다.
종류는 2가지(클러스터 인덱스, 클러스터 되지않은 인덱스)

인덱스를 만들지않고 테이블생성시 PK를 테이블에서 지정하고 생성하는것이 권장사항이다.
-> 일반적으로 identity 처럼 자동증가로 진행한다. = 유니크 인덱스에 해당: 중복된데이터 입력불가

<트리구조>
최상위수준(Root Level)  <-  데이터 검색 시작위치
중간 수준(Intermediate Level)  <-  
마지막 수준(Leaf Level) 더에터가 실제 위치하거나 실제 데이터가 존재

인덱스가 있는 테이블 

 

              인덱스가 없는 테이블                                            인덱스가 있는 테이블

데이터 정렬          X                                                   O (데이터의 위치 값 정렬)
저장(Insert)     빈공간,맨뒤에저장                              정렬한 후 특정위치에 정렬
찾기            Table Scan(느림)                                   인덱스를 이용하면 빠르고 아니여도 빠름
                                                                       (Heap에 순차적으로 정렬이 되어있어서)

 

두개의 차이는
Heap 저장소라고 보면 된다.
클러스터형 인덱스는 실제 데이터가 정렬되어있고
비 클러스터형 인덱스는 데이터의 위치값이 정렬되어있다고 본다.

 

 

 

                 클러스터 인덱스                                                              비클러스터인덱스

테이블          테이블 자체                                                            테이블과 별도의 인덱스를 생성
힙            실제 데이터를 저장/정렬                                                 실제 데이터의 위치 저장
개수            테이블당 한개                                                               테이블당 249개
검색             검색                                                                                  변경

 

 

 

Columnstore 인덱스
- 기존 인덱스 : 행을 기준으로 저장
* 단점 : 특정 컬럼만 읽을 때 -> 전체 데이터 페이지를 읽는다(불필요한컬럼도...)
- 컬럼에 대한 그룹 및 연산 할 때에는 Columnstore인덱스가 성능이 더 좋다
(특정 열/특정 컬럼에 대한 값을 별도의 페이지에 저장해서 조회할 때 사용하기 때문)

 

 

---

 

인덱스 관리

 

1. 조각화 : 조각모음
1) 내부조각화 : 인덱스의 페이지가 모두 차야하는데 비어있는 상황
(주로 OLAP에 일어남. 이유는 Online으로 트랜젝션을 할 때에는 빈공간이 많으면
SELECT 를 할 때 페이지수가 증가해서 성능이 떨어짐-> 내부조각화 필요)
(OLTP에서는 빈공간이 많으면 SELECT를 할 때 페이지수가 증가하지 않기 때문에
성능이 좋음)

2) 외부조각화 : 인덱스는 논리적/물리적 순서를 맞추어야 하는데 이게 맞지 않는 경우라면
성능에 도움이 안됨. 따라서 이것을 제거함
(제거방법 : Rebuild(30%이상일경우 제거 필요), Reorganize(5~30%이상일경우 제거
-> DBM명령어로 확인:

sys.dm_db_index_physical_state & sys.indexes

연결하여 사용

 

예시)

SELECT
						a.index_id, name, 
						avg_fragmentation_in_percent AS 'Ext_farg',	--외부 조각화
						avg_page_space_used_in_percent AS 'In_farg'	--내부 조각화
					FROM 
						sys.dm_db_index_physical_stats(DB_ID, OBJECT_ID('테이블이름',NULL,NULL,NULL)
							AS a
					INNER JOIN sys.indexes AS b
					ON (a.object_id = b.object_id) AND (a.index_id = b.index_id)
					
					)

 

 

 

 

 

 

 

- FILLFACTOR - 채우기 비율
인덱스 페이지가 꽉 차 있을경우 새로운 데이터가 추가 되면 페이지 분할이 됨-> 성능에 영향을 줌
따라서 미리 일정 비율의 인덱스 페이지를 비워 놓으라는 FILLFACTOR 욥션을 사용.
-> 가끔 약간씩 차이가 있는데,
채우기 비율 : 읽기는 값을 높게하고, 변경은 값을 낮게 함
-> 보통 70~80을 왔다갔다 함!!!

 

 

 

 

 

 

 

인덱스의 조각모음/재구성
인덱스를 생성하고 데이터 변경이 많이생기면 페이지의 빈 공간이 생기거나 페이지의 순서에 오차가생김
, 조회시 성능이 떨어짐 -> 인덱스 조각화가 필요
조회코드 : DBSS SHOWCONTIG(테이블이름, 인덱스이름)
만약,

      - Scan Density가 100%면 괜찮음/ 70%아래로 떨어지면 조각모음 실행을 고민해야함(논리적 조각상태)
      - Local Scan Fragmentation이 100%면 괜찮음/70%아래로 떨어지면 조각모음 실행을 고민해야함(논리적 조각상태)
      - Extent Scan Fragmentation이라면 물리적 조각상태.. 물리적이기 때문에 20~30% 사이가 넘어가면 고려해야함.

      - Avg.Byte Free per Page -가용페이지 공간

      - Avg.Page Density(Full)이 80% 밑으로 떨어지면 조각모음 고려 필요
           조각모음 : 인덱스의 물리적 순서가 Leaf Level의 논리적 순서와 일치하도록 하는 작업.

 

재구성(Rebuild)
인덱스의 조각화가 너무 많을경우 인덱스를 다시 작성함
- 조각모음보다 더 많은 작업이 필요하다: 잠금현상이 발생.....
(따라서 SQL Standard버전에서는 주의 필요, Enterprise버전에서는 Rebuild옵션에서 
Online = ON 옵션을 하면 잠금현상 해결되나 시간이 오래걸림.)


- 외부조각화 : 5~25(30%) 정도면 조각모음, 30% 넘어가면 Rebuild가 필요!!!!!!!!!!

---

 

 

- 통계

컬럼에 대하여 인덱스가 만들어 질 때 컬럼 값의 히스토그램 및 관련 정보를 구성하는 것
Auto create statistics / auto update statistics 설정
인덱스 없는 컬럼에 대해서 필요할 경우 자동으로 통계를 생성

전체량 중 약 20%(20%의 행)의 데이터가 업데이트 되면 자동으로 통계가 업데이트 된다.
검색에서 WHERE조건의 해당 컬럼으로 구성된 인덱스를 사용할 지 여부를 최적화 프로그램이 결정한다.
즉, 근거자료가 된다.

DBCC명령어중에 통계 속성을 보는 명령어
:

 DBCC SHOW_STATISTICS(테이블 이름, 인덱스 이름) 

선택도란? 기본적으로 밀도가 낮을수록 높아지고, 선택도가 높으면 인덱스를 사용(인덱스를 잘탐)
가끔, 밀도가 낮을 수 있는데 특정 키 값에 대해서 선택도가 낮을 수 있다(인덱스를 잘 사용하지 못할 수 있다)

- 20%정도의 테이블내용이 변경되면 업데이트를 하는것이 좋다. (자동으로 할 경우)
- 수동으로 할 경우도 있는데 이럴 때에는 아래 명령어를 쓰면 된다.(20%까지 기다리지말고 17%정도에 확인한 경우)
: 

UPDATE STATISTICS 테이블이름

 

 

---

 

 

- 자동튜닝

2014지원
Query Store (쿼리저장소)
- 쿼리 및 실행 계획을 영구적(?)으로 보관하면서 DMV를 통해서 조회 가능하도록 하는것이 목적
- 쿼리, SQL 텍스트, 실행계획 및 횟수, 실행시간, 논리적 일기 등 저장
- 추후 분석
- 추후 활성화 -> 시스템데이터베이스 불가(master, tempdb)
--------------------------
3개 저장소
- 계획저장소 : 실행계획
- 런타임 통계 저장소 :  실행 통계 정보
- 대기 통계 저장소 :  대기 통계 정보
단점 : 성능저하. 따라서 쿼리분석 튜닝용도로만 사용을 권장한다


이걸 사용하지 않으면 : DBV 명령어를 사용했고, 혹은 일부쿼리에 대하여 캐시되지 않기때문에 쿼리를 캡쳐하지 못함
또한, 쿼리에 대한 계획 캐시에서 항목을 제거하면 쿼리 통계가 손실 가능성 높음.

또, 오랜시간 통계를 사용하기 불가하다.