MS-SQL성능[펌]

안녕하십니까? 유일환(ryu1hwan@empal.com) 입니다. SQL Server의 실행계획과 인덱스에 대해 간단히 적어볼까 합니다.
먼저 인덱스가 무엇인지는 다들 아시리라 믿습니다.
물론, 실행계획이 무엇인지도요.
인덱스가 있는 테이블과 없는 테이블간에 성능 차이, 성능을 확인 하는 정도를 다루고 있습니다.

계속해서 어려운 부분도 다루도록 노력하겠습니다.

1. 먼저 할일은 인덱스 없는 테이블과 인덱스 있는 테이블을 생성하는 일입니다.
IF OBJECT_ID('TBL_NO_IDX') IS NOT NULL DROP TABLE TBL_NO_IDX
IF OBJECT_ID('TBL_IDX') IS NOT NULL DROP TABLE TBL_IDX
go
CREATE TABLE TBL_NO_IDX
(    noInt int,
tmpTxt char(800))
go

INSERT  INTO TBL_NO_IDX VALUES (1, 'A')

   -- 만건의 데이터를 TBL_NO_IDX테이블에 입력
   DECLARE @i as int
   DECLARE @maxNo as int

   SET @maxNo = 1

   WHILE @maxNo < 10000
   BEGIN
INSERT INTO TBL_NO_IDX
SELECT  noInt + @maxNo, 'A'
FROM    TBL_NO_IDX
WHERE   noInt + @maxNo <= 10000

SELECT  @maxNo = MAX(noInt)
FROM    TBL_NO_IDX
END

go

SELECT  COUNT(*)
FROM    TBL_NO_IDX

go

------------------------------------------------------------------------------

TBL_NO_IDX 테이블에는 만건의 데이터가 만들어져 있습니다.
이 테이블을 CREATE할 때, tmpTxt라는 컬럼에 800을 주었으므로 한 레코드당
크기는 804바이트 정도가 될 것입니다. SQL Server는 한 페이지(*)에 8000바이트 정도의
데이터를 담을 수 있습니다. 그러므로 한 페이지에 10건 정도의 데이터가
들어가게 됩니다.

*페이지(page):SQL Server의 페이지 입출력을 위한 최소 단위로서 데이터를 읽거나 쓸때
페이지 단위로 동작하게 됩니다.

여기서 1000건의 데이터를 넣기 위해 사용한 방법을 보시면 WHILE문에 조건으로 @maxNo를
사용한 것을 볼 수 있습니다. 이 방법을 사용하게 되면, 10000건의 데이터를 금방 집어 넣을
수 있습니다.

WHILE 루프가 처음 돌때, 2건의 데이터가 들어가 있고, 2번째 돌 때는 4건의 데이터가 들어가고,
3번째 돌때는 8건의 데이터가 들어가고, 4번째는 16건, 이런식으로 2의 n승만큼의 데이터를 한번에
넣게 되므로 WHILE문을 만번 반복하지 않고, 데이터를 넣을 수 있게 됩니다. 이런 것이 집합처리(*)의
기본원리라 할 수 있습니다. 들어가 있는 집합들에 최대값을 더해서, 2배의 데이터를 만들어 내는
것입니다.

*집합처리 : 프로그래머들이 SQL문을 다룰때, 한 개의 레코드에 대해서만 생각을 하고 순차적으로
접근하는 경향이 있습니다. 하지만, SQL은 비 절차적이기 때문에, 이는 옳은 방법이 아닙니다.
우리 개발자들은 SQL을 다룰때, 집합적으로 데이터를 다루어야 합니다. 한번에 한 건의 데이터가 아닌
한번에 여러 집합을 다룰 수 있어야 합니다.

2.데이터를 모두 담았으므로 해당 테이블과 동일한 TBL_IDX라는 테이블을 만들어 내고, TBL_IDX
테이블에는 인덱스를 생성해 줍니다.

--인덱스 생성을 위한 테이블 생성
SELECT  * INTO TBL_IDX
FROM    TBL_NO_IDX
go

--인덱스 생성

CREATE UNIQUE INDEX tbl_idx_idx1 ON TBL_IDX(noInt)

go

--테이블의 크기 확인

SELECT  id, used, rowcnt, name
FROM    sysindexes
WHERE   id IN (OBJECT_ID('TBL_NO_IDX'), OBJECT_ID('TBL_IDX'))

go

--1200정도의 페이지를 가지고 있다.
id          used        rowcnt               name
1977058079  1251        10000                TBL_NO_IDX
1993058136  1139        10000                TBL_IDX
1993058136  25          10000                tbl_idx_idx1

위의 SQL문을 보면 SELECT INTO라는 것이 있는데, 이것은 FROM절에 기술한 테이블과 동일한
테이블을 쉽게 만들 수 있게 해줍니다. SELECT INTO는 여러가지로 응용이 가능하므로 나중에
따로 설명하도록 하겠습니다.
TBL_IDX에는 인덱스를 설정하고, TBL_NO_IDX에는 인덱스를 설정하지 않습니다.
CREATE UNIQUE INDEX로서 UNIQUE한 인덱스를 TBL_IDX에 설정합니다.
인덱스의 이름은 tbl_idx_idx1입니다.
준비된 테이블들의 크기를 보기 위해 sysindexes테이블을 SELECT합니다.
여기서 used라는 컬럼은 해당 object가 얼만큼의 페이지를 가지고 있는지를 보여줍니다.
1페이지는 8000바이트, 즉 8K이므로(정확히 8000바이트는 아닙니다.) 각 테이블이 8 * 1200KB정도의
크기를 가지고 있습니다. 그리고, tbl_idx_idx1에 대해서는 25페이지가 따로 설정되어 있는 것을 알
수 있습니다. 인덱스는 int컬럼인 noInt만 가지고 설정을 했으므로 실제 테이블보다 크기가 적을 수
밖에 없겠죠.

3. 실행계획을 보기 위해 설정을 합니다.
이 설정을 하기 전에, 그래픽으로 보는 실행계획은 OFF를 시키셔야 합니다.
--실행계획과, IO를 보기위한 설정을 ON
SET STATISTICS IO ON        --페이지의 입출력 수를 알 수 있다.
SET STATISTICS PROFILE ON   --실행계획에 대한 결과를 알 수 있다.
go
여기서 PROFILE ON은 실행계획을 보여주지만, 해당 실행계획이 실제 실행된 결과도 보여주게 됩니다.
나중에 보시면 아시겠지만, ROWS와 Executes를 보여줌으로써, 해당 과정에서 몇 Row가 발생되고,
해당 과정이 몇번 실행 됐는지를 알 수 있습니다.

4. 다음의 쿼리를 통해 인덱스를 사용한 경우와 사용하지 않은 경우의 상태를 비교 합니다.
SELECT  *
FROM    TBL_NO_IDX    --인덱스가 없는 테이블
WHERE   noInt = 3  

SELECT  *
FROM    TBL_IDX     --인덱스가 있는 테이블
WHERE   noInt = 3
go 

--인덱스가 없는 테이블의 실행계획 및 IO

Rows    Executes StmtText
1   1   SELECT * FROM [TBL_NO_IDX] WHERE [noInt]=@1
1   1     |--Table Scan(OBJECT:([PLANDB].[dbo].[TBL_NO_IDX]), WHERE:([TBL_NO_IDX].[noInt]=Convert([@1])))

'TBL_NO_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 1250, 물리적 읽기 수 0, 미리 읽기 수 0.

--인덱스가 있는 테이블의 실행 계획 및 IO

Rows    Executes StmtText
1   1   SELECT * FROM [TBL_IDX] WHERE [noInt]=@1
1   1     |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PLANDB].[dbo].[TBL_IDX]))
1   1          |--Index Seek(OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1]), SEEK:([TBL_IDX].[noInt]=Convert([@1])) ORDERED FORWARD)
'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0.

실행 한 결과를 보면, 결과 밑에 실행계획이 보여지고, 메세지 창을 확인하게 되면 페이지의 IO를
보여줍니다.
TBL_NO_IDX의 실행계획은 Table Scan이라는 연산을 하고 있습니다.
(실행계획을 보는 순서에 대해서는 나중에 따로 다루도록 하겠습니다.)
TBL_NO_IDX에는 WHERE절에 기술된 noInt = 3이라는 값을 찾을수 있는 인덱스가 없으므로 테이블 전체를
스캔한다는 것이 됩니다. 그 결과, 논리적 읽기 수는 실제 테이블의 크기인 1251페이지에 가깝다는
것을 알 수 있습니다. 여기서, 논리적 읽기 수는 실제 데이터를 위해 페이지를 읽은 수가 됩니다.
물리적 읽기 수는 메모리에 데이터 페이지가 없으므로 실제 물리적으로 저장된(하드같은곳.)곳에서
페이지를 읽어온 횟수가 됩니다.(여기서는 해당 페이지들이 모두 메모리에 올라와 있으므로 물리적
읽기 수가 없습니다.) 실제로, 똑같은 쿼리가 처음에는 느리고, 두번째 부터 빠른 이유는 이런
이유입니다. 처음 실행된 쿼리의 데이터는 물리적 읽기로 데이터를 찾아와야 하고, 두번째 같은 쿼리는
메모리에서 데이터를 찾아오기 때문이죠.
결과적으로 TBL_NO_IDX테이블에서 한건을 찾아오기 위해 1250페이지를 뒤진 것을 확인 할 수 있습니다.
반면에 TBL_IDX테이블에 대한 것을 살펴 보면, 실행계획은 TBL_NO_IDX과 틀린 것도 알 수 있고,
논리적 읽기 수 역시 3밖에 안되는 것을 알 수 있습니다.
실행계획을 먼저 보면, Index Seek를 하고 있습니다. 이것은 인덱슬르 이용해서 조건에 해당하는
값을 찾아가고 있다는 행동입니다. Index Seek와 Index Scan이 있는 이 두가지가 틀리다는 것을
알아야 합니다. Index Scan은 인덱스의 Leaf Page(인덱스 키값과 해당 키값의 데이터의 주소가
저장된 페이지)를 모두 뒤지는 연산이고, Index Seek는 인덱스 트리(B Tree)를 검색해서 원하는
데이터를 바로 찾아내는 방법입니다.
B Tree 역시 나중에 다루도록 하겠습니다.
여기서는 Index Seek를 했고, 그 후 Bookmark Lookup이라는 연산을 수행했습니다.
Bookmark Lookup은 실제 데이터를 찾아가는 과정이라고 보시면 됩니다. 인덱스안에는 noInt컬럼에 대한
값밖에 없습니다. 그러므로 tmpText까지 포함한 실제 데이터를 보여주기 위해서 실제 데이터를
찾아가는 과정이죠.
이렇게 TBL_IDX에서 데이터를 찾아내는 과정이 진행되었습니다.
결과적으로 TBL_IDX에서는 3개의 페이지만을 읽었다는 것이 중요합니다.
해당 SQL의 성능은 곧 페이지의 IO와 많이 연관이 있기 때문입니다.
페이지 IO를 줄이기 위해 노력하는 것이 해당 SQL문에 대한 튜닝이 될 수 있습니다.

5. 인덱스를 사용하지 않는 경우에 대해서도 알아보도록 합시다.
SELECT  *
FROM    TBL_NO_IDX
ORDER BY noInt  ASC

SELECT  *
FROM    TBL_IDX
ORDER BY noInt  ASC

'TBL_NO_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 1250, 물리적 읽기 수 0, 미리 읽기 수 0.

'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 1113, 물리적 읽기 수 0, 미리 읽기 수 0.

Rows    Executes StmtText
10000   1   SELECT * FROM [TBL_IDX] ORDER BY [noInt]
10000   1     |--Sort(ORDER BY:([TBL_IDX].[noInt] ASC))
10000   1          |--Table Scan(OBJECT:([PLANDB].[dbo].[TBL_IDX]))

go

위 두개의 SQL문은 정렬을 하고 있고, 모든 데이터를 보여주는 작업을 하고 있습니다.
TBL_NO_IDX는 특별히 설명할 것이 없고, TBL_IDX 테이블에 대한 SQL에 대해 말씀드리고 싶은 것은
인덱스가 있는데도, 인덱스를 사용하지 않았다는 것입니다. 이것은 SQL Server의 실행계획을 만들어
내는 옵티마이져란 녀석이 판단을 하기에, 인덱스를 사용하지 않는 것이 더 효율적이라
판단을 했기 때문입니다. 사실, 해당 SQL에서는 인덱스를 사용하게 할 수 있는 여건이 아무것도 없는
것이죠. 그러므로 TBL_IDX도 테이블을 스캔한 후에, Sort라는 작업을 하는 것을 볼 수 있습니다
우리는 테이블에 데이터를 집어넣을 때, 1부터 시작해서 데이터를 넣었습니다.
그러므로 실제 저장된데이터는 1부터 10000까지 시퀀스하게 저장되어 있습니다. 하지만, 관계형 DB는 순서가 없으므로
이런 순서는 언제가는 깨지게 됩니다. 그러므로, ORDER BY를 이용해서 꼭, 순서를 명시하는
SQL문이 필요한 것이죠.

6. 인덱스를 사용해서 Sort를 제거하기

  --인덱스를 사용해서 Sort를 제거
  SELECT  *
  FROM    TBL_IDX (index = tbl_idx_idx1)

--어마어마한 논리적 읽기 수

'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 10024, 물리적 읽기 수 0, 미리 읽기 수 0.
Rows    Executes StmtText
10000   1   SELECT *  FROM TBL_IDX (index = tbl_idx_idx1)
10000   1     |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PLANDB].[dbo].[TBL_IDX]))
10000   1          |--Index Scan(OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1]))

힌트를 주어서 Order By를 제거 할 수 있습니다. 데이터는 무순서로 저장이 되지만,
인덱스는 인덱스로 지정한 컬럼에 대해 순서적으로 저장되어 있으므로 인덱스를 경유해 검색을
하게 된다면 Order By와 동일한 결과를 얻을 수도 있습니다.
하지만 위의 SQL의 IO를 보시면, 10024라는 어마어마한 물리적 읽기가 수행된 것을 알 수 있습니다.
그것은 위의 SQL이 모든 데이터를 대상으로 하므로 실제 데이터를 찾아가는 과정이 필요하기 때문입니다.
위의 실행계획은 이런식으로 실행이 됩니다.

첫번째 인덱스 컬럼에 위치->주소를 참조 실제 데이터를 가져온다.->다음 인덱스로 이동
두번째 인덱스 컬럼에 위치->주소를 참조 실제 데이터를 가져온다.->다음 인덱스로 이동
...
..

만건이 이렇게 수행되어지므로 만번의 Bookmark Lookup이 일어나게 되고, 이것에 대한
부하가 심한 것입니다.

7. 커버된 인덱스의 사용

--커버된 인덱스를 사용해서 페이지IO를 줄일 수 있다.
SELECT  noInt
FROM    TBL_IDX (index = tbl_idx_idx1)

--커버된 인덱스는 확연한 성능개선
Rows    Executes StmtText
10000   1   SELECT noInt  FROM TBL_IDX (index = tbl_idx_idx1)
10000   1     |--Index Scan(OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1]))

'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 24, 물리적 읽기 수 0, 미리 읽기 수 0.

결과를 보면 인덱스 스캔만 있을 뿐, Bookmark Lookup이 없는 것을 알 수 있습니다.
이것은 SELECT절에 noInt만 표시되어 있기 때문입니다. noInt컬럼에 대한 정보는
인덱스에 이미 있으므로 실제 테이블을 뒤질 필요가 없는 것입니다.
이렇게 인덱스만으로 쿼리문이 해결되는 것을 인덱스로 커버된 쿼리라고 합닙다.
커버된 쿼리의 내용 역시 후에 자세히 따로 다루도록 하겠습니다.
하지만 이런 커버된 쿼리는 SQL에 있는 모든 컬럼들이 인덱스 안에 있어야 한다는 제약조건이
있다는 것을 유의해서 사용해야 합니다.

간단하게 인덱스와 실행계획에 대해 살펴보았습니다.
인덱스를 사용하는 쿼리는 Index Seek가 나타나게 되고, Index Seek가 나온다 해도 Bookmark Lookup이 많다면
곧, 성능에 부하가 생긴다는 것이죠. Bookmark Lookup은 인덱스로 찾고자 하는 데이터가 많을수록 많이
나타나게 됩니다.
그러므로 인덱스를 이용해서 데이터를 찾는 것은 많은 데이터가 아닌, 적은 데이터여야 합니다.
물론, 이 적다는 의미가 어느정도인지는 시스템에 따라, 상황에 따라 달라지게 됩니다.
아무리 적어도 1000건까지는 인덱스를 사용해도 괜찮다고 제 개인적으로 생각합니다.
(물론, 인덱스의 깊이에 따라 달라지겠지만요. 그외 클러스터드 인덱스도 고려요소가 됩니다.)

여기서 여러분들이 최소한 건져야 할 것은,
Index Scan이냐, Seek와, Bookmark Lookup이 무엇인지, 그리고 실행계획을 볼려면 어떤
옵션을 주어야 하는지 정도라 생각되어 집니다.

다음에 또, 다른 글들 올리도록 하겠습니다.
부족한 글 읽어주셔서 감사합니다.