컴퓨토피아

자 이제 SQL서버의 색인을 풀어볼 차례 로군요.

약간 어려워도 조금만 참고 함께 이겨나가 보도록 하지요.

그만큼 중요합니다. ^_^

　

색인의 종류 SQL서버는 두가지 종류의 색인이 있습니다. 두가지 뿐이라니 뭔가 기분

좋지요? 그 두가지는 Clustered 인덱스와 Non-Clustered 인덱스 두가지 입니다.

각각 한글로는 클러스터된 인덱스 / 클러스터되지 않은 인덱스로 불립니다.

-_-;;  늘 그런것처럼 혼용해서 코난이는 쓸겁니다. 아시져? ^_^;;

　

인덱스는 분명 만들어야 하는 것입니다.

이 말은

1. 만드는 비용이 있다라는 것이지요.

다음 인덱스는 공간이 필요하다고 했습니다.

2. 디스크 비용이 필요하며 항상 공간 유지를 적절해야 한다는 겁니다.

만들면 끝인가요? 아니죠. 만들었으면?

3. 유지보수에 비용이 필요하겠지요.

인덱스를 만들면 데이터를 조회하는 속도를 높일 순 있지만..

만약 데이터가 삽입 / 수정 / 삭제 된다고 생각해 보세요.

앞에서 본 인덱스의 구조를 변경하면서 재생성 해야 겠지요?

4. 데이터 변경이 있을때 비용이 추가 된다는 겁니다.

앞에서 또한 말씀 드렸지만 인덱스를 사용하면 항상 빨라진다고 말씀 안드렸죠.

5. 인덱스는 항상 빠르지 않다.

인덱스를 사용하면 유용한 부분은?

5-1. WHERE절에서 참조되는 컬럼

5-2. 참조키가 설정되어 있는 컬럼

5-3. 참조키는 아니지만 JOIN에 사용되는 컬럼

5-4. 범위 검색이 일어나는 컬럼

5-5. ORDER BY로 정렬 되는 컬럼, GROUP BY로 그룹핑 되는 컬럼

5-6. TOP 구문과 함께 사용되는 SELECT절

등에서 사용하면 좋습니다.

그렇다면? 어디에 사용하면 바보 될까요?

5-7. WHERE절에서 사용되지 않는 컬럼에는 물론 효과 없음.

5-9. WHERE절에서 변환(함수등이 사용되는)되는 컬럼과 비교시 효과 없음.

5-10. 선택도(찾을 데이터 / 전체 데이터)가 클 경우 효과 적음.

잠시후 말씀 드리겠지만.. 예를들어 성별 컬럼과 같은 남 / 여 비율적으로 대략 50 : 50 의

구성비가 있는 컬럼이라면? 인덱스의 효과가 떨어 지겠죠.

이런 주의 사항이 필요합니다.

　

자 이제 인덱스 생성 구문을 실제로 봐 보도록 하지요.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name      ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )[ WITH < index_option > [ ,...n] ] [ ON filegroup ] < index_option > ::=      { PAD_INDEX |         FILLFACTOR = fillfactor |         IGNORE_DUP_KEY |         DROP_EXISTING |      STATISTICS_NORECOMPUTE |     SORT_IN_TEMPDB }

기분 좋게도 그다지 길지 않지요? 주요한 몇가지를 말씀 드리면..

UNIQUE를 지정해 고유 인덱스 또는 고유하지 않은 인덱스 생성이 가능합니다.

색인의 종류는 두가지로 CLUSTERED | NONCLUSTERED 로 할지 지정이 가능 합니다.

table이나 view에 생성이 가능합니다. SQL2000의 새로운 기능으로 View에 인덱스 생성이

가능 하지요. 뷰 강좌를 참고 해 보세요.

컬럼을 ASC 또는 DESC로 정렬해 생성 가능합니다. 특히 클러스터드 인덱스를 생성시

유용하며 ORDER BY 구문과도 밀접합니다. SQL2000의 새로운 기능입니다.

다음 인덱스 옵션에서

PAD_INDEX는 중간 레벨을 적절히 비워 데이터 삽입 등에 대비하기 위한 것이며

FILLFACTOR는 리프 레벨을 적절히 비워 역시 삽입 등에 대비하는 것입니다.

샘플에서 이야기를 해 드리도록 하지요.

IGNORE_DUP_KEY 는 중복되는 값을 무시한다는 의미 이구요.

DROP_EXISTING 은 이미 존재하는 인덱스가 있으면 제거하고 재생성하라는 의미 입니다.

STATISTICS_NORECOMPUTE 는 인덱스를 사용할지 안할지 쿼리 최적화기가 결정하는데

이 근거는 통계치 데이터라는 녀석으로 판단하게 됩니다. 이 통계 데이터는 기본적으로

자동적으로 항상 update 되는데 이 통계데이터를 자동 업데이트 하지 말라는 옵션 입니다.

SORT_IN_TEMPDB TEMPDB상에서 정렬하라는 옵션 입니다. 예를들어 데이터와 인덱스가

같은 물리적인 디스크에 있고 데이터가 한 1000만건 정도 된다면 인덱스 생성에 대단히

많은 시간이 소요 됩니다. 이때 TEMPDB에서 인덱스 생성시 필요한 정렬작업을 시키고

사용자 데이터베이스의 물리적인 디스크와 TEMPDB 쿨리적인 디스크가 틀리다면 인덱스

생성시 부하를 줄일 순 있지만 TEMPDB에 다른 불필요 공간이 생기니 주의 하셔야 하지요.

끝으로 ON filegroup은 인덱스 역시 데이터라고 말씀 드렸습니다. 데이터베이스 강좌에서

filegroup 을 적절히 분산시켜 생성해 속도를 높일 수 있다고 말씀 드린 것처럼 인덱스 역시

적절한 filegroup에 위치시켜 최적의 속도를 낼 수 있게 할 수 있지요.

　

백견이 불여일타라고 우선 한번 맹거 보도록 하지요.

CREATE DATABASE konanTestDB GO USE konanTestDB GO --테이블 생성 CREATE TABLE konan_test_table( konan_id int IDENTITY (1, 1) NOT NULL , konan_data char (50) NOT NULL , konan_date char (50) NOT NULL ) GO --10000건의 샘플 데이터 삽입 set nocount on GO declare @i int set @i = 0 while @i < 10000 begin --WAITFOR DELAY '00:00:01' insert into konan_test_table values ( @i , convert(varchar, datepart(yy, getdate())) + '년 ' + convert(varchar, datepart(mm, getdate())) + '월 ' + convert(varchar, datepart(dd, getdate())) + '일 ' + convert(varchar, datepart(hh, getdate())) + '시 ' + convert(varchar, datepart(mi, getdate())) + '분 ' + convert(varchar, datepart(ss, getdate())) + '초 ' + convert(varchar, datepart(ms, getdate())) + '미리초 ' ) set @i = @i + 1 end GO --10초. set nocount off GO --샘플 데이터 조회 SELECT TOP 100 * FROM konan_test_table SELECT COUNT(konan_id) FROM konan_test_table

대략적인 샘플데이터 100건과 만건이 잘 들어간게 보일 겁니다.

여기서 어느정도의 IO 비용이 소요되는지 보도록 할까요?

물론 시간 / IO비용 모두가 중요하지만 우선 IO비용만 보도록 하지요.

--IO통계 보기 SET STATISTICS IO ON SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 SET STATISTICS IO OFF

메시지 부분을 보시면 IO통계를 확인하실 수 있을 겁니다.

이제 인덱스를 생성해 보도록 하지요.

　

--간단한 인덱스 생성 CREATE INDEX idx ON konan_test_table (konan_id) GO

인덱스 생성은 잘 되셨을 거구요. 다시 데이터를 조회해 보도록 할까요?

--IO통계 보기 SET STATISTICS IO ON SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 SET STATISTICS IO OFF

통계를 봐 보세요. 어떠세요?

아마도 WHERE konan_id < 30 부터 인덱스가 없을때와 비슷한 수치가

나올 겁니다.

이것은 뭘 말하는 걸까요? 다음처럼 실행계획 표시를 하시거나 또는 컨트롤 + K를 눌러

실행계획을 봐 보도록 하지요.

바로 위의 같은 쿼리를 수행해 보면

　

위의 그림처럼 실행 계획을 보실 수 있습니다.

중요한건 Index Seek를 했다는 점이지요.

　

다음 쿼리에서 수행 계획을 보면?

이렇게 konan_id < 30을 봐 보시면 Table Scan을 하는 것을 알 수 있습니다.

분명 어떤것은 인덱스를 타고 어떤것은 인덱스를 타지 않지요?

앞에서 말씀드린 선택도 라고 해서 (찾을 데이터수 / 전체 데이터수)로 판단 되게 됩니다.

천천히 말씀 드리지요.

　

다음 인덱스의 정보를 보려면 어떻게 할까요?

--인덱스 정보 조회 sp_helpindex konan_test_table

그러면 인덱스의 정보를 확인할 수 있을 겁니다.

흥미있게 보실 부분으로 인덱스의 종류를 지정하지 않으면? 뒤에서 보시겠지만

넌클러스터드 인덱스로 잡히게 됩니다. 참고하세요.

　

인덱스의 변경 구문은 없으며 DROP INDEX 구문을 이용해 인덱스를 제거할 수 있습니다.

　

--인덱스 제거 DROP INDEX konan_test_table.idx

물론 앞에서 말씀드린 CREATE INDEX구문의 DROP EXISTING 구문으로 존재하는 인덱스를

지우고 생성할 수 도 있습니다.

　

우선 인덱스를 생성하고 돌려는 보셨네요.

이제 본격적인 SQL서버의 인덱스에 대해서 알아 보도록 할까요.

클러스터드 인덱스

클러스터드 인덱스는 간단히 인덱스의 리프레벨이 데이터 페이지와 같은 겁니다.

뭔소리냐구요? 천천히 설명 드리지요.

이런 화면을 생각해 보세요.

클러스터드 인덱스는 물리적으로 행을 재배열해 리프 레벨이 차곡차곡 오른쪽의 데이터

페이지처럼 쌓이게 합니다. 그럼 인덱스를 봐 볼까요?

인덱스가 설정된 컬럼의 값이 순차적으로 재배열 되게 되므로 왼쪽에 보시는

인덱스 페이지는 단지 키값과 페이지의 번호만이 지정되게 됩니다.

자 그럼 우리가 만약 5번 강해원을 찾으려 한다고 생각해 보도록 하지요.

이때는 먼저 클러스터드 인덱스의 값을 보니 4 < 5 < 7 사이가 되므로

4 - 2 인 2번 페이지로 가게 됩니다. 2번 페이지로 가서 바로 5번 강해원을 찾게 되지요.

대략 이러한 그림으로 이루어 지게 되는 겁니다.

만약 12번 김태영을 찾는다면? 이때는 역시 10 < 12 < 13 이므로 4페이지로 가서

12번 김태영을 찾을 수 있게 되겠지요.

　

이것이 가능한 이유가 뭘까요? 바로 물리적으로 행이 정렬되어 있기 때문에 가능한 거지요

만약 물리적으로 행이 재배열 되어 있지 않다면? 이런 작업은 불가해 지는 겁니다.

아울러 물리적으로 행들이 재배열 되어 있으므로 범위 검색에 대단히 유용합니다.

이 말은 선택도가 어느정도 높아도 - 클러스터드 인덱스의 경우 30%정도도 가능 - 인덱스

를 이용해 데이터를 조회할 수 있게 되지요.

아울러 클러스터드 인덱스가 저렇게 테이블에 하나 생성되어 있는데요.

만약 클러스터드 인덱스를 하나 더 테이블에 만들고 싶다면 어떨까요?

안타깝게도 다음번 클러스터드 인덱스는 테이블을 다시 재구성 하고 싶겠지만..

이미 한번 테이블이 정렬된 상태로 재구성 되어 있으므로 불가해 집니다.

오로지 테이블에 단 1개의 클러스터드 인덱스만 생성이 가능하니 주의 하셔야 합니다.

그럼 앞의 샘플쿼리를 클러스터드 인덱스로 생성하고 장난을 조금 쳐 볼까요?

--인덱스가 존재하면 지울것 DROP INDEX konan_test_table.idx --클러스터드 인덱스 생성 CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id) GO

이렇게 클러스터드 인덱스를 생성할 수 있지요. 그런데 특이하게도

SQL서버는 클러스터드 인덱스에 대해서 대단히 높은 우선권을 부여 합니다.

예를들어 이럴땐.. 풀스캔을 해도 좋을것 같은데... 클러스터드 인덱스를 써서

검사할 경우가 있지요. 이는 말씀 드린대로 SQL 쿼리 최적화기가 판단하는데

테이블의 크기가 작거나(로우의 건수가 아닌 전체적인 크기) 또는 클러스터드 인덱스의

키값 컬럼이 작을 경우 종종 발생 합니다.

실제 현업에서는 범위검색에 종종 이 클러스터드 인덱스를 두게 되므로 정상적으로

잘 동작하게 되지요.

또한 클러스터드 인덱스는 비교적 넌클러스터드 인덱스보다 크기가 작습니다.

아울러 클러스터드 인덱스는 크기를 대략적으로 예측할 수 있는 인덱스이기도 하지요.

　

다음 넌 클러스터드 인덱스를 봐 보도록 할까요?

넌 클러스터드 인덱스

실제 테이블의 데이터가 항상 순차적으로 들어가 있는 것은 아닙니다.

관계형 데이터베이스에서 순차라는 것은 사실 의미가 없습니다. 관계형 데이터베이스의

순차 유지는 오로지 ORDER BY에 적절히 이용되는 컬럼을 잘 구성해야만 하는 것이지요.

예를들어 실제 진짜 SQL서버에 들어가 있는 테이블의 순서는 다음과 같을 겁니다.

이런 식의 데이터가 실제로 들어가 있게 되지요. 그렇다면 페이지 내부는 어떤 식일까요?

대략적으로 위의 그림과 같은 식의 데이터가 들어가 있게 됩니다.

실질적인 데이터 페이지 이지요. 이런 데이터페이지들의 번호에 클러스터드 인덱스를

만약 생성한다면 어떻게 될까요? 이럴 경우는 RID라는 녀석이 필요하게 됩니다.

간단히 RID는 로우의 구별하는 특정 값이라고 생각 하시면 됩니다.

넌 클러스터드 인덱스는 바로 이 RID를 가지고 데이터 페이지를 포인팅 하게 되지요.

RID 샘플을 보시면 다음과 같은 식입니다.

여기서 RID의 첫번째 1은 화일 그룸을 의미하게 됩니다.

그다음 숫자는 데이터 페이지 번호이며 마지막 세번째 숫자는 페이지 옵셋으로 정확히

페이지의 한 로우를 포인팅(Pointing)하게 되지요.

또한 이 인덱스 페이지는 다음과 같은 형식으로 분할 되겠지요.

　

이러한 형식이 넌클러스터드 인덱스에서 과연 어떻게 사용 될까요?

실제 구조를 그려 보도록 하지요.

이러한 식으로 생성이 되게 됩니다.

루트 레벨은 인덱스 페이지7이며 중간 레벨은 엔덱스 페이지 1,2,3,4 이고

실제 데이터페이지는 1,2,3,4,5 데이터 페이지가 되지요.

자 값을 네비게이션 해 보도록 합시다.

만약 제가 3번 이승용을 찾으려 한다고 생각해 보지요.

1 < 3 < 5 이므로 1페이지로 가야 겠지요? 같더니 3은 4페이지 2번째 로우에 있다고 합니다.

바로 4페이지로 가서 두번째 로우를 컨택하는 거지요.

또한 만약 8 차영인을 찾으려 한다면

5 < 8 < 9 이므로 인덱스페이지 2로 가서 8 차영인을 보니 1-3-2라고 되어 있습니다.

데이터 페이지 3의 2번째 로우를 포인팅 하게 되지요.

이것이 넌클러스터드 인덱스 입니다.

넌클러스터드 인덱스는 이렇게 포인팅 정보를 가지게 되므로 인덱스의 크기가 커지게

됩니다. 아울러 선택도가 높으면 바로 쿼리 최적화기는 이 넌클러스터드 인덱스를

사용하지 않게 되지요. 일반적으로 3% 이내 정도면 이 넌클러스터드 인덱스를 사용하지

않게 됩니다.

넌클러스터드 인덱스는 데이터페이지를 물리적으로 재배열하지 않으므로 여러개의

인덱스를 생성할 수 있습니다. 최대생성 가능 갯수는 249개 입니다.

두 차이를 비교한다면 다음과 같겠지요.

다음으로 클러스터드 인덱스가 있는 테이블의 넌클러스터드 인덱스를 알아 보도록 하지요.

　

물론 당연히 클러스터드 인덱스와 넌 클러스터드 인덱스가 있을때 넌클러스터드 인덱스를

조회할 경우 겠지요?

　

현재는 번호에 클러스터드 인덱스가 걸린 상태이며 이름에 넌클러스터드 인덱스가 설정된

상태 입니다.

이런 식으로 구성이 되게 됩니다. 먼저 알아 두셔야 할 것은!!

넌클러스터드 인덱스의 RID는 더이상 RID가 아닌 클러스터드 인덱스의 키값을 가지게 됩니다.

좀더 간단히 실제 조회를 해 보도록 하지요.

넌클러스터드 인덱스가 걸린 이름 컬럼의 유병수라는 이름을 조회해 보도록 합시다.

박훈 < 유병수 < 이수선 이므로 넌클러스터드 중간 레벨의 2페이지로 가게 될겁니다.

여기서 유병수를 찾으니 유병수는 클러스터드 인덱스 10번 키값을 가지고 있습니다.

10이라는 값을 가지고 클러스터드 인덱스 페이지로 가니 10은 데이터페이지 4에

있다고 합니다. 데이터페이지 4로 가니? 바로 10 유병수를 찾을 수 있게 되지요.

네 맞습니다. 바로 이런 그림이 되는 것이지요.

만약 한기환을 찾으려 한다면 어떻게 될까요?

한기환 < 한기환 이므로 넌클러스터드 중간레벨의 4페이지로 가게 되겠죠?

클러스터드 인덱스의 키값 2이니. 1 < 2 < 4 이므로 1 데이터 페이지로 가게 되며 1 데이터 페이지에서 2번 한기환을 발견하게 될 겁니다.

[출처] 색인의 종류|작성자 필립박

1. 기본 설정

먼저 실행계획과 페이지 입출력을 보기 위한 두 가지 설정을 ON하도록 하비다.

    SET STATISTICS IO ON

    SET STATISTICS PROFILE ON



그다음 기존글(인덱스와 실행계획)에서 생성했던, TBL_IDX테이블을 사용해서

TBL2_IDX란 테이블을 만들어 냅니다.

    SELECT  T1.noInt, (T2.a * 10000) + T1.noInt col1 INTO TBL2_IDX

    FROM    TBL_IDX T1 CROSS JOIN

        (SELECT 0 a

        UNION ALL

        SELECT 1

        UNION ALL

        SELECT 2) T2

    --30000개의 데이터

설명드렸듯이 SELECT ~ INTO는 바로 테이블을 만들 수 있는 유용한 방법입니다.

위 SQL은 1, 2, 3값을 가지는 T2라는 파생된 테이블(인라인뷰)과 CROSS JOIN을 함으로서

TBL_IDX의 3배에 크기를 가지는 테이블을 만들게 됩니다.

CROSS JOIN은 아무 조건 없는 조인으로서 카테션곱의 결과를 만들게 됩니다.

다음과 같이 TBL2_IDX에 인덱스를 생성합니다.



    CREATE INDEX tbl2_idx_idx1 ON TBL2_IDX(noInt)

    CREATE INDEX tbl2_idx_idx2 ON TBL2_IDX(col1)



2.쿼리 수행

다음쿼리를 수행하고 실행계획을 보도록 합니다.

    SELECT  *

    FROM    TBL_IDX T1 JOIN TBL2_IDX T2

        ON T1.noInt = T2.noInt

    WHERE   T2.col1 = 3



NO Rows Execute StmtText

1   1   1   SELECT *  FROM TBL_IDX T1 JOIN TBL2_IDX T2   ON T1.noInt = T2.noInt  WHERE T2.col1 = 3

2   1   1     |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PLANDB].[dbo].[TBL_IDX] AS [T1]))

3   1   1          |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[noInt]))

4   1   1               |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([PLANDB].[dbo].[TBL2_IDX] AS
[T2]))

5   1   1               |    |--Index Seek(OBJECT:([PLANDB].[dbo].[TBL2_IDX].[tbl2_idx_idx2] AS [T2]), SEEK:
([T2].[col1]=3) ORDERED FORWARD)

6   1   1               |--Index Seek(OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1] AS [T1]), SEEK:([T1].
[noInt]=[T2].[noInt]) ORDERED FORWARD)



*여기서 NO는 설명을 쉽게 하기 위해 제가 적어 놓은 것입니다.



실행계획은 보통 제일 안쪽중에서 위에것을 먼저 읽습니다.

하지만, 이것이 실행계획이 복잡해지면 쉽지 않습니다. 쉬운것부터 보면서 연습을 하는 것이 필요하죠.

저같은 경우는 위에서부터 보면서, 연산에 따라 분기하면 더 쉽게 볼 수 있다고 생각합니다.

물론, 이런 과정도 안쪽중에서 위에것이 먼저라는 순서는 변하지 않습니다.

먼저, 우리가 SELECT문장을 날린것이 제일 위에 있습니다.

SELECT문이 있는데, 해당 SELECT문의 결과를 얻기 위해 마지막으로 한 일은 Bookmark Lookup입니다.(2번과정)

그전에 한 일은 Nested Loops이고요, 여기서 이 연산이 중요합니다.

이 연산은 조인을 수행하는 연산인데, 조인을 수행하는 연산은 두 개의 자식 연산을 가지게 됩니다.

이 때 두개의 자식 연산중에 위쪽에 연산을 먼저 수행하게 됩니다.

여기서는 4번과 6번이 자식 연산인데, 4번이 먼저 수행됩니다. 4번은 하나의 자식 연산을 가지고 있으므로

이 자식 연산이 4번 연산보다 먼저 수행됩니다. 그러므로 5번이 제일 먼저 수행됩니다.

이런 과정을 통해, 역으로 생각해보면 실행 순서는

5번->4번->6번->3번(조인)->2번이 되어서 1번 문장을 만들어 내는 것입니다.

중요한 것은 자신이 자식 연산을 가지고 있으면 자식 연산이 먼저 수행되고, 자식 연산이 두 개 이상일 경우

제일 위에 연산부터 수행된다는 것입니다.

이 말이 곧, 제일 안쪽중에 제일 위에 실행계획부터 읽어야 된다는 말이 되죠.

조금만 습관이 되면, 쉽게 쉽게 볼 수 있습니다.

읽는 순서를 정했으니 순서대로 따라 가면서 분석을 해보도록 하죠.

먼저, 5번 연산은 Index Seek를 하고 있습니다.

5   1   1               |    |--Index Seek(OBJECT:([PLANDB].[dbo].[TBL2_IDX].[tbl2_idx_idx2] AS [T2]), SEEK:
([T2].[col1]=3) ORDERED FORWARD)

5번 연산의 StmtText를 보면 Index Seek라는 것은 쉽게 알 수 있습니다.

Index Seek라는 것은 인덱스를 인덱스 트리(BTree)를 통해 원하는 값을 찾는 것을 뜻합니다.

그렇다면 어떤 값을 찾고 있는지를 알아야 겠죠. StmtText의 뒷부분을 보시거나, 플랜의 Argument컬럼을

보시면 다음과 같은 부분이 있습니다.

OBJECT:([PLANDB].[dbo].[TBL2_IDX].[tbl2_idx_idx2] AS [T2]), SEEK:([T2].[col1]=3) ORDERED FORWARD

처음부터 해석을 해보면 OBJECT라는 것은 어떤 인덱스를 대상으로 하고 있는 것을 나타냅니다.

여기서는 tbl2_idx_idx2인덱스를 사용했습니다. 이 인덱스는 위에서 col1컬럼으로 만들어졌죠.

그다음을 보면 SEEK(T2.col1 = 3)이라고 있습니다. 이 것이 바로 인덱스로 검색(SEEK)하고 있는 값이죠.

이렇게 StmtText를 자세히 보면, 해당 연산에 대해서 더 자세히 알 수 있습니다.

그리고 인덱스를 통해 col1=3이란 값을 찾은 결과 행수는 1이란 것을 Rows컬럼을 통해 알 수 있습니다.

여기서 Plan중에 DefinedValues라는 컬럼의 값도 체크할 필요가 있습니다. 5번 연산의 DefinedValues의 값은

[Bmk1001]입니다. 이 값은 이 연산을 통해 정의된 값이 되겠죠. 이 값은 5번 연산 다음에 행해지는 4번 연산에서

사용되어 집니다.



5번의 다음 연산인 4번은 BookMark Lookup이라는 연산을 수행하고 있습니다.



4   1   1               |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([PLANDB].[dbo].[TBL2_IDX] AS
[T2]))



Bookmark Lookup 연산은 인덱스 키값에 해당하는 실제 데이터를 테이블에 가서 찾아오는 과정을 말합니다.

5번과정에서 T2.col1=3인 데이터를 인덱스에서 찾아낸 후에, 해당 레코드의 나머지 데이터 값(noInt)을 찾아오기 위해

행하는 연산입니다.

StmtText를 보면 BOOKMARK([Bmk1001])이란 것이 있는 것을 알 수 있습니다. 이 Bmk1001은 5번 과정에서 정의된
값이었죠.

그러므로 5번 연산에 대한 Bookmark Lookup을 한다는 것이 증명됩니다. 사실은 자식 연산과 부모 연산이라는 관계만
으로도

어떤 Index에 대한 Bookmark Lookup이라는 것은 명확하게 알 수 있습니다.

다음 과정은 6번 과정입니다. 6번 과정역시 Index Seek를 하고 있습니다.

6번과정의 StmtText를 보면 다음과 같습니다.

OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1] AS [T1]), SEEK:([T1].[noInt]=[T2].[noInt]) ORDERED
FORWARD)

인덱스는 TBL_IDX의 tbl_idx_idx1을 사용했습니다. 이 인덱스는 TBL_IDX의 noInt컬럼에 대해 정의된 인덱스입니다.

찾는 과정은 SEEK(T1.noInt = T2.noInt)입니다.

이 말은 곧, TBL_IDX2의 noInt값을 참조한다는 것입니다. 이 T2.noInt값은 5번과 4번을 과정을 거쳐서 가져온

값이 되겠죠.

그다음 연산인 3번을 보면 Nested Loops라는 연산을 볼 수 있는데 사실상, 6번 과정에서 T2.noInt와 동일한 noInt값을

가진 연산을 수행한 것이 이 연산을 수행한 것입니다. 사실상 이 연산은 두 개의 테이블이 이 방식으로

조인했다는 것을 알려주는 정도가 되겠죠.

이 Join연산에 대해서는 PLAN중에 PhysicalOp와 LogicalOp를 볼 필요가 있습니다. 여기서

Physical Op는 Nested Loops이고, LogicalOp는 Inner Join입니다.

논리적인 동작인 Inner Join은 해당 연산이 결과를 만족하는 값끼리 연결하는 Inner Join이라는 것을 뜻하고,

물리적인 동작인 Nested Loops는 해당 연산을 하기 위해 실제적으로 Nested Loops방식을 사용했다는 것입니다.

여기서 물리적인 동작 방법이 바낀다고 해도 결과는 변경되지 않습니다.

하지만, 논리적인 동작이 변경된다면 결과값은 바뀌게 됩니다.(Outer Join이 된다면 말이죠.)

마지막 과정인 2번을 보시면 Bookmark Lookup을 하고 있는 것을 알 수 있습니다. 이 과정은

TBL_IDX에 대한 실제 데이터를 찾아가는 과정이란 것을 알 수 있습니다.



단순하게 두 개의 테이블을 조인하는 실행계획을 설명하는데도 설명의 양이 너무 많은 거 같다는 생각이 듭니다.

하지만, 여러분들이 조금씩만 노력하다 보면 이런 설명이 필요없어지겠죠.^^

아직 설명이 다 끝난 것은 아닙니다. 실행계획을 보고 각 과정을 실제 쿼리와 연결시킬 수 있는 능력이 필요합니다.

이것또한 실행계획을 차례대로 보는 것만큼 쉽습니다.

단지 여러분들이, 실행계획과 실제쿼리를 자세히 관찰해서 증거를 모아서 판단하는 연습만이 필요할 뿐입니다.ㅁ

먼저 5번 과정에 Index Seek를 T2.col = 3에 대해 수행했습니다.

이것은 곧, 쿼리문의 T2.col = 3을 수행했다는 말이 되겠죠.

그리고, BookmarkLookup을 통해 T2.col=3인 레코드의 모든 데이터 정보를 가져 왔습니다.

우리는 여기서, 왜 이과정을 했을까를 생각해볼 필요가 있습니다. 조금만 생각하신다면

이 과정이 다음에 진행할 과정을 위한 것이라는 것을 알 수 있습니다. 4번의 다음과정은 6번입니다.

6번의 Seek연산은  다음과 같죠

SEEK:([T1].[noInt]=[T2].[noInt]

이 부분이 나온것은 쿼리에서 JOIN ON절에 해당합니다. ON T1.noInt = T2.noInt입니다.

우리는 T2의 인덱스를 통해 T2.col=3인 데이터를 가져 왔습니다. 해당 레코드를 T1과 JOIN을 해야 하는데

T1과 조인하기 위해서는 T2의 noInt컬럼이 필요합니다. 그런 이 컬럼은 실제 테이블에 저장되어 있으므로

해당 테이블을 찾아가는 4번 과정의 Bookmark Lookup이 진행되었던 거죠.

T1과 T2간에 noInt컬럼이 동일한 값을 찾는 Inner Join과정이 진행되었습니다.

그리고 나서 마지막으로 2번과정의 Bookmark Lookup이 수행되었습니다.

이 과정은 SELECT * 를 위해 수행된 것이죠. SELECT리스트에는 두 테이블의 모든 정보를 보여주어야 하므로

실제 테이블을 가서 데이터를 가져오는 작업을 수행한 것이죠.

만약에 2번 과정의 Bookmark Lookup이 제거된 PLAN을 보고 싶다면 다음과 같이 쿼리를 수행할 수 있습니다.



    SELECT  T1.noInt, T2.*

    FROM    TBL_IDX T1 JOIN TBL2_IDX T2

        ON T1.noInt = T2.noInt

    WHERE   T2.col1 = 3



실행계획을 보시면 2번과정에 있던 Bookmark Lookup이 제거된 것을 알 수 있습니다.

이것은 TBL_IDX에 있던 tbl_idx_idx1 인덱스만으로 쿼리가 커버됐기 때문입니다.

커버되었다는 것은 SQL문에 SELECT절과, WHERE절, ON절에 나온 컬럼이 모두 인덱스에 존재하는 컬럼이라는 것
입니다.



약간은 더 복잡한 쿼리를 통해, 실행계획을 보는 순서와, 더불어 실행계획을 보는 능력(?)을 계속

키워보도록 합시다.



이번 SQL문은 다음과 같습니다.





SQL문의 내용은 주문과 주문 상세중에 1998년 2월 26일 데이터에 대해서 고객별로 금액(Quantity * UnitPrice)을 구하
고

해당 고객들의 정보까지 Customers와 연결해서 보여주는 SQL입니다.

먼저 실행계획을 보기 전에 우리는 인덱스를 확인할 필요가 있습니다.

위 SQL에서 사용되는 테이블은 세개이고 각각의 인덱스를 살펴보면 다음과 같습니다.

테이블에 설정된 인덱스를 알아보기 위해서는 sp_helpindex 프로시져를 사용합니다.

이 프로시져를 사용하기 전에 SET STATISTICS PROFILE OFF를 실행하시기 바랍니다.

    USE Northwind

    SET STATISTICS PROFILE OFF

    sp_helpindex Orders

index_name  index_description   index_keys

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

CustomerID  nonclustered located on PRIMARY CustomerID

EmployeeID  nonclustered located on PRIMARY EmployeeID

OrderDate   nonclustered located on PRIMARY OrderDate

PK_Orders   clustered, unique, primary key located on PRIMARY   OrderID

ShippedDate nonclustered located on PRIMARY ShippedDate

ShippersOrders  nonclustered located on PRIMARY ShipVia

ShipPostalCode  nonclustered located on PRIMARY ShipPostalCode



    sp_helpindex [Order Details]

index_name  index_description   index_keys

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

OrderID         nonclustered located on PRIMARY OrderID

PK_Order_Details    clustered, unique, primary key located on PRIMARY   OrderID, ProductID

ProductID       nonclustered located on PRIMARY ProductID



    sp_helpindex [Customers]

index_name  index_description   index_keys

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

City    nonclustered located on PRIMARY City

CompanyName nonclustered located on PRIMARY CompanyName

PK_Customers    clustered, unique, primary key located on PRIMARY   CustomerID

PostalCode  nonclustered located on PRIMARY PostalCode

Region  nonclustered located on PRIMARY Region



여기서는 중복된 인덱스를 제거했습니다. 이 인덱스는 우리가 실행계획을 보기 전에

대충 실행계획이 어떻게 만들어질 것이다를 추측할 수 있게 해줍니다.





    SELECT  T1.*, T4.*

    FROM    Customers T1 JOIN

        (SELECT T2.CustomerID, SUM(T3.Quantity * T3.UnitPrice) amt

        FROM    Orders T2 JOIN [Order Details] T3

            ON T2.OrderID = T3.OrderID

        WHERE   T2.OrderDate = '1998-02-26 00:00:00.000'

        GROUP BY T2.CustomerID) T4

        ON T1.CustomerID = T4.CustomerID



위 SQL과 인덱스만을 보고 추측을 해보도록 하죠.

물론, 실제로는 비용기반 옵티마이져가 복잡한 과정을 통해 실행계획을 만들어 내므로 어느정도까지

추측이 가능할 뿐, 확실히 예측할 수는 없습니다.



제 생각에는 FROM절에 괄호안에 있는 파생된 테이블(또는 인라인 뷰라 함)이 먼저 실행되어질거 같습니다.

이 파생된 테이블 T4에는 Orders와 Order Details가 있습니다. WHERE조건에는 Orders의 OrderDate가 = 조건으로

있으므로 아마도 Orders의 OrderDate컬럼을 가지고 있는 OrderDate인덱스를 사용할 거 같습니다.

그 다음 Orders와 Order Details를 조인하기 위해 Orders에서 해당 OrderDate의 모든 데이터를 BookmarkLookup작
업을

통해 가져올 것입니다.(OrderID, CustomerID를 참조해야 하니까요)

그다음, Orders에서 가져온 OrderID를 Order Details에 공급해서 Join을 수행할 거 같습니다.

여기서는 여러가지 Join방법이 있으므로 어떤 방법을 사용할지는 잘 모르겠군요

JOIN을 수행하고, Order Details의 Quantity와 UnitPrice를 가져오기 위한 Bookmark Lookup이 필요할 거 같습니다.

그 다음, GROUP BY연산을 수행할거라 생각됩니다. GROUP BY를 위해, SORT가 수행될 수도 있습니다.

물론, SORT가 안 일어날 수도 있습니다. 이것에 관련된 기사를 어디선가 봤는데, 이 부분은 나중에 적어보도록 하겠
습니다.

그 다음, Customers와 JOIN을 수행하겠죠. 물론 조인의 방법은 예측을 쉽게 할 수는 없을 거 같습니다.

마지막으로 JOIN된 결과에 대해 Bookmark Lookup을 수행할 것입니다.



JOIN의 선택 방법에는 100%는 아니지만, 어느정도 근접한 룰이 있습니다.

이것에 대한 기사 역시 SQL Server Magazine에서 본적이 있었습니다. 하지만, 모든 정보를

제 머리에 넣고 있을수는 없으니까, 나중에 정리해보도록 하겠습니다.



여기까지는 제 예측잉었습니다. 저도 물론, 아직까지 위 쿼리를 수행해 보지 않았으므로 실행계획을 알 수 없습니다.

여러분들도 실행전에 실행계획을 나름대로 예측해 보셨으면 합니다. 좋은 경험이 되리라 생각되어 지니까요.

그럼 SQL문을 실행해 보도록 하겠습니다.



    SELECT  T1.*, T4.*

    FROM    Customers T1 JOIN

        (SELECT T2.CustomerID, SUM(T3.Quantity * T3.UnitPrice) amt

        FROM    Orders T2 JOIN [Order Details] T3

            ON T2.OrderID = T3.OrderID

        WHERE   T2.OrderDate = '1998-02-26 00:00:00.000'

        GROUP BY T2.CustomerID) T4

        ON T1.CustomerID = T4.CustomerID



1 6 1   SELECT T1.*, T4.*  FROM Customers T1 JOIN   (SELECT T2.CustomerID, SUM(T3.Quantity *
T3.UnitPrice) amt   FROM Orders T2 JOIN [Order Details] T3    ON T2.OrderID = T3.OrderID   WHERE
T2.OrderDate = '1998-02-26 00:00:00.000'   GROUP BY T2.CustomerID) T4   ON
2 6 1     |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[CustomerID]))
3 6 1          |--Stream Aggregate(GROUP BY:([T2].[CustomerID]) DEFINE:([Expr1002]=SUM(Convert([T3].
[Quantity])*[T3].[UnitPrice])))
4 16    1          |    |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[OrderID]))
5 6 1          |         |--Sort(ORDER BY:([T2].[CustomerID] ASC))
6 6 1          |         |    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Orders]
AS [T2]))
7 6 1          |         |         |--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[OrderDate] AS [T2]), SEEK:
([T2].[OrderDate]='02 26 1998 12:00AM') ORDERED FORWARD)
8 16    6          |         |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Order Details].
[PK_Order_Details] AS [T3]), SEEK:([T3].[OrderID]=[T2].[OrderID]) ORDERED FORWARD)

9 6 6          |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers] AS [T1]),
SEEK:([T1].[CustomerID]=[T2].[CustomerID]) ORDERED FORWARD)



먼저 가장 먼저 일어난 작업은 7번에 있는 Index Seek입니다. OrderDate인덱스를 사용하고 있습니다.

여기서 결과가 6건이 나왔고, 이 6건에 대해 Bookmark Lookup이 수행되었습니다. 이것이 6번 과정이죠.

6번과정 다음에 Sort가 나왔군요, 저는 Join후에 GROUP BY가 나올거라 생각했는 데 SQL Server는 먼저 Sort를 했습
니다.

Sort는 CustomerID에 대해 이루어 졌습니다. 이는 CustomerID별로 GROUP BY를 해야 되기 때문에 이루어진거라 생
각되는 군요.

그 다음, 8번 과정이 이루어 집니다. Order Details 테이블에 대해 클러스터드 인덱스 SEEK를 하고 있습니다.

Order Details 테이블에는 OrderID가 클러스터드 인덱스의 컬럼인거 같습니다.

클러스터드 인덱스를 SEEK했으므로 JOIN후 UnitPrice와 Quantity를 가져오기 위한 Bookmark Lookup은 발생하지 않
습니다.

이 또한, 제가 틀린 부분이네요.^^ 클러스터드 인덱스는 리프노드에 실제 데이터를 담고 있기 때문에, Bookmark
Lookup이

필요 없습니다. 이 8번과정을 통해 4번 과정인 Nested Loops가 이루어지는 것이죠. 그 다음, 실제, GROUP BY를 수
행하기 위해

3번 과정인 Stream Aggregate가 일어나게 됩니다. 그 다음에 9번 과정인 Customers에 대한 클러스터드 인덱스 Seek
가 일어나게

9번 과정을 통해 2번과정인 JOIN이 실행되어 지구요.

이런 과정으로 진행이 되는 겁니다.

SQL 서버 2005 실전 활용 2

VS.NET으로 개발하는 SQL 서버 2005

한용희 | 롯데정보통신 칠성IS 사업팀

연+재+가+이+드
운영체제｜윈도우 2000, 윈도우 2003, 윈도우 XP
개발도구｜MS SQL 서버 2005 베타 2, 비주얼 스튜디오 2005 베타 1
기초지식｜MS SQL 서버 2000, C#
응용분야｜MS SQL 서버 2005 관리와 개발

지난 시간에서는 T-SQL의 새로운 모습에 대하여 살펴봤다. T-SQL은 언어 자체가 집합적 언어이기 때문에 여전히 데이터를 조작하고 접근하는 데 있어서는 닷넷 언어보다 더 좋은 성능을 나타낸다. 하지만 T-SQL은 절차적 언어이기 때문에 객체지향적 프로그래밍을 할 수 없다는 단점이 있다. 그러나 닷넷을 이용하면 더 이상 이 문제로 고민하지 않아도 된다. C#, VB.NET, Managed C++를 이용해서 얼마든지 객체지향적 프로그래밍이 가능하다. 또한 복잡한 로직이나 계산, 외부 자원 연동, 코드 재사용 등에 있어서는 T-SQL보다 더 좋은 접근성과 성능을 보여준다. 한 마디로 닷넷 프레임워크와의 통합은 T-SQL을 교체하는 개념이 아니라 더욱 확장하고 강화하기 위하여 도입된 것이라고 보면 된다.
SQL 서버 2005는 닷넷 프레임워크와 통합되면서 안정성이 대폭 향상되었다. 이전 SQL 서버 2000에서 확장 저장 프로시저를 C++를 이용해서 작성하는 경우 간혹 잘못된 코드로 인하여 SQL 서버 전체가 다운되는 경우가 있었다. 그래서 확장 저장 프로시저를 매우 신중하게 만들어야 했으며 만드는 과정 자체도 간단하지가 않았다.
하지만 SQL 서버 2005에서는 기본적으로 닷넷 프레임워크의 호스팅 모델을 따라간다. SQL 서버 2005와 각각의 닷넷 코드로 만들어진 확장 저장 프로시저는 서로의 독립성을 보장한다. 서로 메모리를 직접적으로 침범할 수 없으며, 서로의 실행 환경을 침해할 수도 없다. 각각 별도로 운영된다는 것이다. <그림 1>을 보면 닷넷 프레임워크의 호스팅 모델이 나와 있다. SQL 서버와 외부 어셈블리는 서로 다른 도메인을 가지고 있어 자신의 독립적인 실행 환경을 보호한다. 그래서 이제는 확장 저장 프로시저 때문에 더 이상 SQL 서버가 다운되는 일은 없다.

<그림 1> .NET 프레임웍 호스팅

SQL 서버는 자기 자신만의 특별한 쓰레드 스케쥴링, 동기화, 잠금, 메모리 할당 정책을 가지고 있다. SQL 서버 자체가 워낙 메모리를 많이 사용하고 성능이 중요한 기업용 애플리케이션이기 때문에 보통의 CLR(Common Language Runtime)에서 제공하는 정책을 따르지 않고 자기 자신만의 특별한 방식을 적용해서 운영을 한다. 만약 외부 어셈블리가 CPU나 메모리를 과도하게 많이 써서 SQL 서버를 운영하는데 지장을 준다면, SQL 서버는 이를 즉시 탐지해내고 해당 사용권을 외부 어셈블리로부터 뺏어온다. 이렇게 함으로써 SQL 서버는 더 이상 외부의 간섭에 영향을 받지 않고 자기 자신을 스스로 안정적으로 운영할 수 있는 능력을 가지게 되었다.

간단한 사용자 정의 함수 만들기

먼저 간단한 사용자 정의 함수를 C#으로 만들어 볼 것이다. 복잡한 표현식이나 계산을 요하는 작업의 경우 C#으로 만드는 것이 더 효율적이므로 이번 예제에서는 우편번호를 체크하는 간단한 정규식 표현 함수를 만들어 보자. 먼저 VS.NET을 시작하고 새로운 프로젝트로 SQL 서버 프로젝트를 선택한다. CLREx이라는 새로운 프로젝트를 만들고 AdventureWorks DB 서버에 연결한 후 새로운 아이템으로 IsValidZipCode라는 사용자 정의 함수를 추가한다.
그러면 <화면 2>와 같은 템플릿 코드가 들어 있다. 여기에서 주의해서 봐야 할 것은 함수 위에 있는 속성 [SqlFunction]이다. 이 속성은 다음의 함수가 SQL에서 사용하는 사용자 정의 함수임을 컴파일러에게 알려주는 지시자이다. 이제 기본 코드는 지우고 다음과 같이 코딩을 하자.

using System;
using System.Data.Sql;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
   [SqlFunction]
   public static bool IsValidZipCode(SqlString ZipCode)
   {
      return System.Text.RegularExpressions.Regex.IsMatch(ZipCode.ToString(), 
              @”^\d{3}-\d{3}”);
   }
};

<화면 1> SQL 서버용 템플릿


<화면 2> IsValidZipCode 초기 생성 화면

간단하게 해당 문자열이 우편번호식인지 검사하여 결과를 리턴해 주고 있다. 이제 이 코드를 컴파일하여 배포까지 하자. 그러면 자동으로 SQL 서버에 이 어셈블리가 등록된다. 배포를 성공적으로 끝내면 다음과 같이 테스트해 보자.

select dbo.IsValidZipCode(‘333-333’);
select dbo.IsValidZipCode(‘333-A33’);

-----
1
(1 row(s) affected)
-----
0
(1 row(s) affected)

잘 작동하는 것을 볼 수 있을 것이다. 사용자 정의 함수를 만들어서 사용해 보았는데 함수를 만들고 배포하는 것이 간단하다는 것을 느꼈을 것이다. 그럼 SQL 서버 내부에는 어떻게 등록되어 있는 것일까?

SELECT * FROM sys.assemblies;

sys.assemblies라는 뷰를 보면 해당 CLREx이라는 어셈블리가 등록되어 있는 것을 확인할 수 있을 것이다.

SELECT * FROM sys.assembly_files;

sys.assembly_files에는 실제 어셈블리의 내용이 들어 있다. 즉, DLL 바이너리 자체를 SQL 서버안에 등록한 것이다. 그러므로 한번 어셈블리를 SQL 서버 안에 배포하면 해당 DLL 파일은 없어도 무방하다. 앞에서는 배포를 VS.NET을 이용해서 자동으로 배포하였지만, 수동으로 배포하는 방법도 있다.

CREATE ASSEMBLY UDF1
FROM ‘\\localhost\Projects\CLREx\CLREx\bin\Debug\CLREx.dll’;

CREATE FUNCTION IsValidZipCode(@ZipCode nvarchar(10))
RETURNS bit
EXTERNAL NAME
CLREx.UserDefinedFunctions.IsValidZipCode;

이와 같이 먼저 어셈블리를 등록하고 해당 함수를 만들어 주면 수동으로도 등록할 수 있다.

저장 프로시저를 C#으로 만든다?

이번에는 저장 프로시저를 만들어 보자. 저장 프로시저를 만들려면 먼저 SQL 문장을 실행해서 결과를 리턴해야 한다. 그러기 위해서는 어셈블리가 DB에 접속을 해서 SQL 문장을 보내줘야 한다. 일반적으로 ADO.NET을 이용해서 DB에 접속을 하지만 기존의 연결 방법을 사용할 경우에는 외부에서 접속해 들어오는 것이므로 성능 상에 문제가 있다. 따라서 내부 접속을 위한 별도의 데이터 프로바이더(Data Provider)가 필요한데 그것이 바로 SQL Server Managed Provider이다. 이 프로바이더는 SQL 서버 내에서 실행되므로 별도의 접속을 맺을 필요 없이 빠르게 수행을 한다. 따라서 open, close와 같은 절차가 필요 없는 데이터 프로바이더이다. 사용 방법은 다음과 같이 선언하면 된다.

using System.Data.SqlServer;

SQL Server Managed Provider에는 효과적인 작업을 위하여 Sql Command, SqlPipe, SqlResultSet, SqlTransaction, SqlTrigger Context와 같은 몇 가지 타입을 제공한다. 이중 대부분은 SqlClient에 있는 것과 동일하고 SqlPipe와 SqlTiggerContext가 이번에 새로 등장한 타입이다. SqlTiggerContext는 트리거 작성을 위한 타입이고, SqlPipe는 테이블과 같은 데이터를 호출하는 쪽에 보내줄 때 사용하는 타입이다. 그러면 SqlResultSet과 뭐가 다르냐고 할 수도 있다. SqlResultSet은 성능 문제로 인하여 사용을 권하지 않는 타입이고(이제는 없어질지도 모른다) SqlPipe가 성능상 더 좋은 타입이다. SqlPipe는 말 그대로 호출자에게 파이프로 물을 보내듯이 데이터를 받는 즉시 바로 보낸다. 성능면에서도 T-SQL의 저장 프로시저와 거의 비슷한 성능을 보여준다. 그러므로 앞으로 테이블 데이터를 리턴받는 경우에는 SqlPipe를 써야 한다. 또한 닷넷 저장 프로시저는 리턴 값으로 int형과 void형만을 리턴할 수 있으므로 어차피 SqlResultSet 형식으로 리턴하지도 못한다.
이번에는 직접 저장 프로시저를 만들어 보자. 이전에 만든 프로젝트에 저장 프로시저를 하나 추가하고 다음과 같이 코딩을 한다.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public partial class StoredProcedures
{
   [SqlProcedure]
   public static void SelectEmp(SqlInt16 val)
   {
      SqlCommand sqlCmd = SqlContext.GetCommand();
        
      sqlCmd.CommandText = “SELECT * FROM HumanResources.Employee “
           + “WHERE DepartmentID = @pDeptID”;

      // 파라미터 값 대입
      sqlCmd.Parameters.AddWithValue(“@pDeptID”, (Object)val);

      // SqlPipe를 이용하여 결과 리턴
      SqlContext.GetPipe().Execute(sqlCmd);
   }
};

이번 예제는 사용자 테이블에서 특정 부서의 사람들을 추출하는 저장 프로시저이다. 이를 컴파일하고 배포한 후 다음과 같은 SQL 문장으로 테스트해 보면 결과를 볼 수 있을 것이다.

EXEC dbo.SelectEmp 4;

나만의 데이터 타입을 만들자

SQL 서버에는 기본적으로 char, int와 같은 기본 데이터 타입을 지원한다. 여기에 더 확장하여 우리가 원하는 데이터 타입을 스스로 만들어서 추가할 수도 있다. 예를 들면 위도, 경도, 포인트를 나타내는 데이터 타입이라든지 이메일 주소를 나타내는 데이터 타입을 새로 만들어서 추가할 수 있다. 포인트를 보면 10:30과 같은 표현식을 수용하는 하나의 컬럼을 만들 수도 있다. 그런데 사실 이러한 표현은 기존의 컬럼을 두 개로 나누어서 x, y좌표 값을 저장해도 된다. 굳이 사용자 정의 데이터 타입(User-Defined Data Types, UDT)을 안 만들어도 할 수는 있다. 하지만 의미상 하나로 표현하는 것이 더 타당하고, 그 자료형과 관련된 많은 메쏘드나 행위가 필요할 때에는 하나의 데이터형으로 만드는 것이 바람직하다.
예를 들면 날짜 같은 데이터 타입을 년, 월, 일로 나누어서 3개의 컬럼에 저장하는 것보다는 년월일 하나로 만들어서 하나의 컬럼에 저장하는 것이 더 의미상 더 타당하다는 것은 누구나 알고 있다. 또한 날짜와 관련된 많은 메쏘드와 제약사항들이 있기 때문에 이를 3개의 컬럼으로 나누어서 처리하는 것은 많은 불필요한 코드들을 필요로 한다. 예를 들면 월에 1월을 더하거나 빼는 연산과 같은 것들을 하나의 데이터 타입에 같이 넣어 두면 어디서나 손쉽게 끌어다 쓸 수 있다. SQL 서버의 UDT는 데이터 자체뿐만 아니라 메쏘드도 같이 포함할 수 있으므로(사실 UDT는 클래스나 구조체로 정의한다) 이러한 구현이 가능하다.
그럼 여기서 이런 생각까지 하는 독자가 있을 수도 있다. “UDT를 클래스의 개념으로 볼 수 있으니 이제는 객체를 그대로 DB에 저장할 수 있다는 얘기군. 그럼 아예 사원(Employee) 객체를 통째로 DB에 저장해 볼까?” 여기까지 생각을 하면 “그동안 미들티어에서 했던 OR 맵핑(Object Relational Mapping)이 더 이상 필요 없는 진정한 객체지향의 DB가 탄생했군!”이라고 생각할 수도 있다. 틀린 얘기는 아니다. 하지만 성능과 용량이 문제가 된다. UDT는 8KB라는 크기 제한이 있고, 인덱싱 처리의 제약, 그리고 데이터 업데이트 시 부하가 있다. 그러므로 UDT는 그러한 복잡한 객체를 저장하는 데에는 적절하지 않다. 처음에 예를 들었던 위도, 경도, 포인트와 같이 가벼운 객체를 저장할 때에만 이 UDT를 사용해야 한다.
UDT는 결국 클래스를 하드디스크에 저장하는 것이기 때문에 직렬화를 해야 한다. 직렬화를 위해서는 데이터의 크기가 중요하다. 기본적으로 닷넷 환경에서는 값 타입(Value Type)과 참조 타입(Re ference Type)이라는 두 가지 타입이 있다. 값 타입은 int, char과 같이 실제 데이터가 직접 있는 타입이고, 참조 타입은 string과 같이 실제 데이터가 아닌 데이터의 주소가 들어 있는 타입을 말한다. 따라서 이들 데이터 타입에 따라 저장하는 방법도 달라진다. 값 타입은 대부분 고정된 길이를 가지고 있으므로 컴파일러가 알아서 그 크기를 계산할 수 있지만, 참조 타입의 경우 그 크기가 얼마나 될지 모른다. 그래서 하드디스크에 얼마 정도의 공간을 할당해야 하는지 모르는 것이다. 그래서 참조 타입을 직렬화하는 경우에는 사용자가 직접 그 방법을 정의해줘야 한다. 직렬화 방법을 정리해 보면 다음과 같이 3가지 방법이 있다.

◆ SerializedDataWithMetadata
값 타입이나 참조 타입에 관계없이 어떤 데이터 타입도 저장 가능. 하지만 성능 면에서는 가장 느리다. 아마 베타 3에서는 없어질 포맷이다. 한 마디로 사용하면 안 되는 포맷이다.

◆ Native
크기가 고정된 값 타입의 데이터 형만 저장 가능. 가장 빠르다.

◆ UserDefined
값 타입, 참조 타입 모두 사용가능. 하지만 사용자가 데이터를 읽는 방법과 쓰는 방법을 정의해줘야 한다.

앞의 세 가지 포맷 중 사용자 정의 포맷에서 읽기와 쓰기를 직접 구현하는 것은 간단하지가 않다. 약간 복잡하다. 이번 예제는 UDT를 소개하는 것이 목적이므로 Native 포맷을 이용하는 간단한 포인트 예제를 보여주려고 한다. 기존 CLREx 프로젝트에 새로운 아이템으로 Point라는 사용자 정의 데이터 타입을 추가해 보자. 그러면 기본적인 코드들이 생성되어 있을 것이다. 모두 지우자. 현재 템플릿에서 생성된 코드는 옛날 방식의 코드이다. 기본 구조는 다음과 같다.

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct Point : INullable
{
   private Boolean is_null;
   private Int32 m_x;
   private Int32 m_y;

   // 기본 메쏘드
   public override string ToString() { ... }
   public bool IsNull { get; }
   public static Point Null { get; }
   public static Point Parse(SqlString s) {...}

   // 추가한 메쏘드
   public Int32 x {...}
   public Int32 x {...}
   public decimal DistanceTo(Point other) {...}       // 두 포인트 간 거리구하기
}

이 메쏘드들을 채워주면 포인트 UDT가 완성된다. 앞의 가상코드를 보면 직렬화를 지원하고 Native 포맷으로 정의되어 있는 것을 볼 수 있을 것이다. 그리고 class가 아닌 struct로 선언한 것이 보일 것이다. 굳이 class가 아닌 struct를 쓴 이유는 전통적으로 사용자 정의 데이터 타입은 구조체를 썼기 때문이다. 그 이유는 클래스는 힙에 데이터가 저장되지만 구조체는 그렇지가 않다. 따라서 클래스는 가비지 콜렉터가 쉽게 수거해 갈 수 있지만 구조체는 그렇지 않다. 성능 면에서 구조체가 약간 더 빠르다는 것이다. 또한 NULL 값을 구현하는데 있어 구조체는 별도의 초기화 없이 기본적으로 모든 값을 기본 값으로 초기화해 준다.
예를 들면 숫자형은 모두 0으로 자동 초기화를 해준다. 그래서 데이터 형을 다루는 데에는 아무래도 클래스보다는 구조체가 약간 더 편하다고 할 수 있다. SQL 서버에서는 NULL이라는 값이 존재한다. 따라서 UDT를 만들 때에는 NULL이라는 의미를 부여해줘야 한다. 그래서 INullable 인터페이스를 상속받아서 NULL을 구현하고 있다.
포인트를 저장하기 위해서 x, y값을 위한 공간을 마련하고 널 값 체크를 위한 공간도 마련하였다. 그런데 사실 널 값 체크를 위해서 이와 같이 별도의 저장공간을 사용하는 것은 하드디스크 낭비가 될 수 있다. 그래서 어떤 사람들은 이와 같은 경우 Int32.MinValue를 널 값 대신으로 사용하기도 한다. 즉 Int32의 최소 값을 널 값으로 대신하는 것이다.
만약 포인트의 데이터형이 string형이면 이러한 불편이 없다. string형은 참조 타입이기 때문에 null이라는 값을 수용할 수 있기 때문이다. Int32라는 데이터형은 값 타입이기 때문에 NULL을 수용할 수가 없어 이와 같은 방법을 사용하였다. 어떤 방법을 사용하든 그것은 개발자의 몫이니 상황에 따라 적절한 방법을 사용하면 된다. 이번 예제에서는 하드디스크의 공간을 걱정 안 해도 되므로 그냥 따로 널 값 체크를 위한 데이터형을 따로 만들었다. 기본적인 메쏘드의 설명은 <표 1>과 같다.

<표 1> 기본적인 메쏘드 설명

구분	설명
IsNull	이 데이터형이 NULL인지 아닌지를 리턴
Null	이 데이터형의 Null 자체를 정의
Parse	외부로부터 문자열을 받아서 UDT의 데이터를 저장할 때 쓰이는 메쏘드
ToString	UDT 내부의 데이터를 문자열 형식으로 외부로 표현할 때 정의하는 메쏘드

실제 완성된 코드는 ‘이달의 디스켓’으로 제공하니 참고하기 바란다. 이제 이 UDT를 컴파일하고 배포하면 다음과 같이 테스트할 수 있다.

DECLARE @a Point, @b Point;

IF @a is null 
   PRINT ‘null’
ELSE 
   PRINT ‘not null’;

SET @a.x = 10;
SET @a.y = 20;

SET @b.x = 100;
SET @b.y = 110;

SELECT CAST(@a AS CHAR);
SELECT CAST(@b AS CHAR);

SELECT @a.DistanceTo( @b );   -- 두 점 사이의 거리구하기
-----------------------------------------------------------------
null
10:20                         
100:110                       
127

SUM, MAX와 같은 집합 함수만으로는 더 이상 충분하지 않다

이번에 SQL 서버의 CLR 통합 기능 중에서 제일 반가운 것이 바로 이 기능이다. 기존에 MIN, MAX, SUM, COUNT, AVG 같은 집합 함수를 쓰다 보면 부족함을 느끼는 경우가 많다. 이러한 집합 함수가 있으면 좋을 것이라고 많은 사람들이 원했던 것이 사실이다. 이제는 이러한 집합 함수를 직접 만들어 쓸 수 있다. 만드는 방법은 UDT와 상당히 유사하다. 이번 예제에서는 최대 변이 값을 구하는 함수를 만들 것이다. 즉, 최대 값-최소 값을 구하는 MaxVariance라는 함수이다. 기존 프로젝트에 새로운 아이템으로 Aggregate를 추가하고 이미 있는 템플릿 코드는 역시 옛날 방식이므로 지운다. 기본 구조는 다음과 같다.

[Serializable]
[StructLayout(LayoutKind.Sequential)]
[SqlUserDefinedAggregate(Format.Native)]
public struct MaxVariance
{
   private Int32 m_LowValue;
   private Int32 m_HighValue;

   public void Init() {...}
   public void Accumulate(SqlInt32 Value) {...}
   public void Merge(MaxVariance Group){...}
   public SqlInt32 Terminate() { ... }
}

데이터 형이 값 타입 밖에 없으므로 Native 포맷으로 했으며, 최대 값과 최소 값을 저장하는 별도의 변수를 만들었다. 각 메쏘드별 설명은 <표 2>와 같다.

<표 2> 각 메쏘드별 설명

구분	설명
Init	값초기화
Accmulate	실제 계산 함수
Merge	병렬 처리시 필요한 연산 수행
Terminate	최종 결과 리턴

자세한 코드는 ‘이달의 디스켓’에 있으니 참고하기 바란다. 앞의 사용자 정의 집합(User-Defined Aggregate, UDA)을 컴파일하고 배포한 후 다음과 같은 코드로 테스트해 보자. 다음 코드는 전체 사원 중에서 휴가 시간이 가장 많은 사람과 가장 적은 사람의 차이를 나타낸 것이다.

SELECT 	dbo.MaxVariance(VacationHours)
FROM	HumanResources.Employee;
SELECT 	MAX(VacationHours) - MIN(VacationHours)
FROM	HumanResources.Employee;

-----------
99
(1 row(s) affected)
99
(1 row(s) affected)

앞뒤의 쿼리문을 대조해 보면 제대로 된 결과가 나왔음을 확인해 볼 수 있다.

클라이언트 ADO.NET의 개선점

이번에 ADO.NET 2.0으로 나오면서 SQL 서버와 관련해서 크게 주목할 부분은 두 가지가 있다. 하나는 비동기 호출기능과 하나의 연결로 다수의 커맨드를 실행하는 기능(Multiple Active Result Sets, MARS)이다. 지난 호에서 ADO.NET에서도 페이징 처리가 가능하다고 했는데, 그 기능이 이젠 없어질 예정이라서 이번에 제외했다.

더 이상 기다릴 필요 없는 비동기 호출

비동기 호출 기능은 기존에 쿼리 문장을 수행시키고 결과가 올 때까지 기다려야 했단 불편을 없애고 클라이언트는 결과가 올 때까지 나름대로의 작업을 할 수 있다. 그러므로 사용자는 쿼리 문장을 날리고 모래시계의 아이콘을 기다릴 필요 없이 다른 작업을 수행할 수도 있다. 이때 처음 DB에 연결을 맺을 때 비동기 호출을 쓴다는 표시를 “Asynchronous Processing=true”와 같이 해줘야 한다. 간단한 예제를 보자.

SqlConnection cnn = new SqlConnection(
   “Data Source=localhost;” +
   “Initial Catalog=AdventureWorks;” +
   “Integrated Security=SSPI;” +
   “Asynchronous Processing=true”);

cnn.Open();
// 2초 간의 딜레이 후 조회
SqlCommand cmd = new SqlCommand(
   “WAITFOR DELAY ‘00:00:02’;SELECT * FROM Sales.Customer”, cnn);

Console.WriteLine(“작업 시작”);
IAsyncResult iar = cmd.BeginExecuteReader();

while (!iar.IsCompleted) {  Console.Write(“*”); }  // 결과 올 때까지 별 찍기

cmd.EndExecuteReader(iar);
Console.WriteLine(“\n작업 끝”);

--------------------------------------------------------------------
작업 시작
******************
작업 끝

앞의 예제는 고객 데이터를 조회하는데 있어 비동기 호출을 이용하고 있다. 먼저 비동기 호출의 장점을 보려면 DB에서 시간이 오래 걸리는 작업을 돌려봐야 그 효과를 확실히 볼 수 있다. 그래서 2초간 딜레이를 주는 문장을 삽입하여 강제로 시간이 오래 걸리도록 하였다. 그리고 클라이언트는 결과가 올 때까지 계속 별을 찍다가 결과가 오면 끝내는 예제이다. 그런데 이번 예제에서는 간단히 하기 위해서 끝났는지 안 끝났는지를 알아보기 위하여 WHILE문에서 계속 체크를 하였지만, 실제 사용할 때에는 이렇게 할 필요 없이 비동기 콜백 함수를 만들어서 다 끝나면 저절로 그 함수가 호출되게 하는 것이 더 좋은 방법이 될 것이다.

하나의 연결로 다수의 쿼리 실행

기존 ADO.NET에서는 하나의 연결을 맺으면 하나의 커맨드만 실행 가능하였다. 그래서 다른 커맨드를 실행하려면 별도의 연결을 다시 맺어야만 했다. 하지만 이제는 하나의 연결로 다수의 커맨드를 실행할 수 있다. 이렇게 함으로써 매번 새로운 연결을 맺지 않아도 되므로 성능 향상이 있는 것이다. 구현하는 방법은 어렵지 않다. 그냥 쓰면 된다. 다음 예제를 보자.

// 하나의 연결
SqlConnection cnn = new SqlConnection(
   “Data Source=localhost;” +
   “Initial Catalog=AdventureWorks;” +
   “Integrated Security=SSPI;”);
cnn.Open();

// 첫 번째 실행
SqlCommand cmd1 = new SqlCommand(
   “SELECT * FROM Production.Location”, cnn);
SqlDataReader dr1 = cmd1.ExecuteReader();
// 두 번째 실행
SqlCommand cmd2 = new SqlCommand(
   “SELECT * FROM HumanResources.Department”, cnn);
SqlDataReader dr2 = cmd2.ExecuteReader();

// 결과 출력
while (dr1.Read() == true && dr2.Read() == true)
{
   Console.WriteLine(dr1[0] + “  |  “ + dr2[0] );
}

----------------------------------------------------------
1  |  1
2  |  2
3  |  3
4  |  4
5  |  5
.......

cmd1과 cmd2가 하나의 cnn이라는 연결을 공유해서 쓰고 있다. 전체 예제는 ‘이달의 디스켓’에 있다.

SQL 서버의 변신은 무죄?

처음에 SQL 서버가 닷넷 프레임워크(CLR)에 통합된다고 하였을 때 많은 사람들이 궁금증을 가지고 지켜보았다. 이제는 C#을 공부해야 하는가 하고 걱정하는 사람들도 있었다. 하지만 막상 뚜껑을 열어보니 CLR 통합이라는 기능은 T-SQL을 대체하는 기능이 아닌 좀 더 확장하고 보강하기 위한 기능으로 보는 것이 좋다는 결과가 나왔다. SQL 서버를 개발하는 데 있어 기본은 T-SQL이다. 하지만 거기서 멈추지 않고 더욱 새로운 기능을 추가하고 확장하고 싶다면 닷넷을 이용하면 된다. 다음 시간에서는 DB 관리 툴과 보안에 대해 소개하겠다.

제공 : DB포탈사이트 DBguide.net