가늘어도 긴놈이 장땡

다음 SQL을 SSIS 데이터 흐름 작업으로 변환시키는 시나리오를 생각해보자.


(1)번은 심플하다. OLE DB 원본으로 주욱 읽어들인다.
(2)도 쉽다. 조회 변환을 사용해서 REF_DATA1 을 읽고 오류 무시를 설정, 조인키는 col1, r1.colA를 조회 새로운 열 colA로 추가한다.
(3)도 쉬워보인다. 조회 변환, REF_DATA2 를 읽고 오류 무시, 조인키는 col2, r2.colB를 읽어서 (2)에서 추가한 colA에 대해서 바꾸기 colA 로 설정한다.

자 돌려보자!

이런-_-;;;;; (2) 에서 조회에 성공한 1000개의 REF_DATA1 의 값 colA가 몽땅 NULL로 바뀐 거시다!!!! ㅠ_ㅠ

이게 왜 그럴까?

그렇다.. (3) 에서 조회 성공한 100건에 대해서 colA의 값을 바꾸고 나머지 10000건 - 100건 의 데이터는 몽땅 NULL로 바꿔버리는 거시다... 이거때문에 한참 헤맸다. DB에 너무도 익숙해진 나머지 위의 조회 변환도 당연히 DB의 update 처럼 동작하는 것으로 착각한 것이다. 이걸 제대로 돌게 하려면 (3)에서 읽어들이는 값을 새로운 열 추가 colA_1 로 해놓고 파생열 변환에서 바꾸기 colA 열, !ISNULL(colA_1) ? colA_1 : ( !ISNULL(colA) ? colA : "N" ) 이런식으로 바꿔준다.

앞으로 이런 삽질을 얼마나 더 해야할까. 흑.

Posted by maceo

조회 변환을 LEFT JOIN 처럼 작동하게 하려면 보통 오류 무시를 하는데, 이때 조회 실패로 인한 오류도 무시하지만 DB에서 뱉어내는 오류도 무시해버리는 문제가 있다. 그런데 하필이면 그 오류가 데이터를 한참 넘겨주다가 데이터상의 문제로 쿼리 프로세싱이 멈춰버리는 경우라면 데이터가 다 넘어오지 않아서 문제가 되는 경우가 발생한다. 아래는 역시나 sqlleader.co.kr 에서 주고받은 글.

---------

· 제 목   조회변환에서 SELECT를 수행하던중 에러가 났을때 그대로 진행됩니다 · 작성자    박노철 · 글정보   Hit : 16, Date : 2007/02/22 17:39

이건 질문이라기보다는 제가 겪은 문제 상황을 해결하는 과정에서 알아낸 조회 변환의 특이한 동작? 인데요...   조회 변환/전체 캐시모드에서 다음과 같은 쿼리가 있었습니다.   select cast(col1 as int) as money_to_int_col, (다른 컬럼들..) from TABLE with(nolock) where col2 > XXXX   원래대로라면 이 쿼리는 90만건 정도의 데이터를 반환해야 하는데 SP2 를 설치하고 로그를 확인해보니 총 반환되는 행이 45만건 밖에 안되는겁니다. 그렇다고 해서 조회 변환 로그에 에러가 찍히는 것도 아니었고 패키지 실행이 실패하지도 않았습니다. 패키지 실행중 에러가 하나라도 나면 실패하도록 해놨거든요.   문제는 cast() 연산에 있었는데요, col1 의 데이터중에 1000억이 넘는 값이 들어간 데이터가 있었습니다. 한 45만개 정도를 리턴하고 45만1번째 데이터가 1000억이었는데, 이걸 int로 변환하다가 overflow 가 나버린겁니다. 그래서 select 문의 실행이 중지되었지요. 데이터가 넘어가긴 했구요. 그래서 where 조건에 col1 < 20억 이렇게 넣고 나니 90만건을 모두 읽어오는군요.   이런 상황에서 조회 변환 컴포넌트쪽에서 에러를 내고 프로세싱이 멈췄다면 문제를 좀 더 일찍 확인할 수 있었겠는데... 조회 변환의 디폴트 설정에서 이런 에러는 무시하나 봅니다. 아니면 제가 조회 변환쪽이나 패키지 실행쪽에 뭔가 에러 처리 설정을 좀 더 상세하게 했었어야 하는 거였을까요???? 하여간 이런 문제가 있어서... 노하우 공유차 글을 올려봅니다^^

한대성 좋은 내용 올려주셔서 감사합니다.^^ 저도 테스트 해 보고 있는데, SELECT 쿼리에서 Overflow로 인해 실패가 나는 경우 조회 변환 자체도 실패로 처리되지 않는지요? select OrderID, CAST(CASE WHEN EmployeeID <4 THEN 1000000 ELSE 10000000000 END as int) as OrderID2 FROM Northwind..Orders 와 같은 형태로 총 830개의 데이터 중 347개만 리턴하고 Overflow 에러가 발생하도록 하고 이 쿼리를 조회 변환에서 수행해 보았는데, 저의 경우에는 조회 단계에서 에러가 나네요.. 혹시나(^^) 조회 변환에서 오류 무시로 설정을 하신 건 아니신지 궁금하네요..^^ 다시 한 번 감사드립니다.. 2007/02/23 박노철 아 오류 무시로 했네요. 그런데 조회 변환에서 오류 무시로 해놓으면 조회가 실패했을때도 오류 무시를 하고 DB에서 데이터를 읽어오다가 실패해도 무시를 하도록 설정이 되기 때문에 문제인 것 같습니다. 조회 변환을 LEFT JOIN 처럼 동작하도록 하고 싶었기 때문에 오류 무시를 해놨는데 그게 이럴때 문제가 되네요. 처음해보는거라 좌충우돌 미숙한 점이 많습니다. ㅋ 많은 도움 감사드립니다^^ 2007/02/23   한대성 아..그렇군요^^ 대량 데이터를 이용한 테스트가 어려운 상황에서 좋은 정보를 많이 공유해 주셔서 정말 감사합니다. 덕분에 개인적으로도 테스트 하고 익힐 수 있는 기회가 되어 많은 도움을 받고 있습니다... 저도 감사.ㅎㅎ 꾸벅 ^__^ 2007/02/23

Posted by maceo

역시나 sqlleader.co.kr 에서 주고받은 글...

---------

제 목   Re) NULL 데이터와 ? : 3항 연산자와 파생열 변환이 함께 쓰였을 때 이상한 동작 · 작성자    한대성 · 글정보   Hit : 7, Date : 2007/02/23 00:32

Null에 대한 처리로 인한 것 같습니다..   (is_dnt_goods_yn1 == "Y") ? "Y" : "N" 의 조건식에서 is_dnt_goods_yn1이 NULL 인 경우에는 "N"이 아닌 NULL로 처리됩니다.   NULL 이 "Y"는 아니지만, 그렇다고 해서 "Y"가 아닌 것도 아니기 때문에..   말씀하신 조건에 대한 조건식은 다음과 같이 하면 어떨까 합니다..   is_dnt_goods_yn2 == "A" && !ISNULL(is_dnt_goods_yn2) ? "A" : (is_dnt_goods_yn1 == "Y" && !ISNULL(is_dnt_goods_yn1) ? "Y" : "N")   조건 중에 "A가 아니면, ... Y가 아니면..." 이라는 조건 때문에 단순히   !ISNULL()   로만 판단하면 안되지 않을까 생각되네요..^^       작성자 : 박노철 , 등록일 : 2007-02-22 오후 5:31:00   파생열 변환과 관련하여 아무래도 이해가 잘 되지 않는 점이 있어서 다시 질문드립니다. 다음과 같은 소스 데이터가 있습니다.   select * from is_dnt_goods_src   is_dnt_goods_yn1 is_dnt_goods_yn2 ---------------- ---------------- Y                NULL Y                NULL Y                NULL Y                NULL Y                NULL NULL             A NULL             A NULL             A NULL             A Y                NULL Y                NULL Y                NULL Y                NULL Y                NULL Y                NULL Y                NULL NULL             A NULL             A NULL             A NULL             A NULL             A NULL             A Y                NULL Y                NULL Y                NULL Y                NULL Y                NULL Y                NULL   여기서 필요한 비즈니스 로직은 is_dnt_goods_yn 이라는 필드값을 파생열 변환을 사용해서 새 열로 만들어내는데,   'is_dnt_goods_yn2 가 A 면 A를 리턴하고 A가 아니면(NULL이거나 다른값) 이면 is_dnt_goods_yn1 을 검사해서 그 값이 Y면 Y를 리턴하고 그게 아니면(NULL이거나 다른값) 이면 N 를 리턴한다'   입니다. 위의 로직을 파생열 변환에서 다음과 같이 구현해보았습니다.   is_dnt_goods_yn      <새열로 추가>   is_dnt_goods_yn2 == 'A' ? 'A' : ((is_dnt_goods_yn1 == 'Y') ? 'Y' : 'N')   결과는 다음과 같습니다. is_dnt_goods_yn1 is_dnt_goods_yn2 is_dnt_goods_yn ---------------- ---------------- --------------- Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL NULL             A                A NULL             A                A NULL             A                A NULL             A                A Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL NULL             A                A NULL             A                A NULL             A                A NULL             A                A NULL             A                A NULL             A                A Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL Y                NULL             NULL   A는 제대로 넘어오는데 Y값이 제대로 채워지지 않습니다. 아무래도 이상해서 파생열 변환의 expression 을 바꿨습니다.   !ISNULL(is_dnt_goods_yn2) ? is_dnt_goods_yn2 : (!ISNULL(is_dnt_goods_yn1) ? is_dnt_goods_yn1 : 'N')   NULL을 검사하도록 했죠. 그랬더니 다음과 같이 나왔습니다.   is_dnt_goods_yn1 is_dnt_goods_yn2 is_dnt_goods_yn ---------------- ---------------- --------------- Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y NULL             A                A NULL             A                A NULL             A                A NULL             A                A Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y NULL             A                A NULL             A                A NULL             A                A NULL             A                A NULL             A                A NULL             A                A Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y Y                NULL             Y   정확한 결과가 나왔습니다.   제가 expression 을 잘못 사용한 것일까요? 3항 연산자 evaluate 하는데 버그가 있는 것일까요? -_-;;;; 이거때문에 정말 한참을 헤맸습니다. 흑. 혹시 이런 경우를 겪어 보셨는지요????

박노철 아.. 그렇군요. 감사합니다. DB에서 적용되던 TRUE, FALSE, NULL 의 원리가 SSIS 스크립트에서도 그대로 적용되는 모양이군요. 보통 프로그래밍 언어에서 하듯이 하면 될 줄 알았는데. 다시 한번 감사드립니다^^ 2007/02/23

Posted by maceo

Posted by maceo

테이블 레벨에 제약조건을 걸 때 함수를 쓰게 된다. 이런 식이다.


이렇게 하면 테이블에 데이터가 들어갈 때나 지워질 때, 수정될 때 언제나 위의 함수가 수행되게 된다. 그런데 만약 함수안에 들어가 있는 SQL문을 만족하는 인덱스가 없다면 test_table에 대한 풀스캔이 일어나게 되어 급격하게 성능이 나빠진다.

더욱 심각한 것은, 함수로 인한 성능저하는 실행계획에 잡히지도 않아서 알아내기가 매우 까다롭다. 그리고 위의 SQL이 트랜잭션 도중에 진행된다면 with(nolock) 이 없기 때문에 블러킹이 일어날 수가 있다. 이때 master..sysprocesses.blocked 컬럼에 spid가 0이 나온다. 즉 블러킹이 없다고 나오게 된다. 역시나 알아내기 힘들다. 또는 함수내의 SQL문이 복잡하다면 이 함수의 실행계획이 꼬일 가능성도 배제하지 못한다.

위와 같은 경우 master..sysprocesses 만 봐서는 상황을 알아내기가 힘들다.

조치법은 대략 다음과 같다.

1. 먼저 SQL2000 SP4를 깔자.
2. 구글에서 aba_lockinfo 를 쳐서 나오는 Sommerg? 의 블로그에서 aba_lockinfo 를 받아서 DB에 설치하자.
3. 블러킹 상황이 발생하면 aba_lockinfo 를 돌린다.

이 sp의 좋은 점은 블러킹 체인의 시작점을 보여줄 뿐 아니라 inputbuffer 와 함께 현재 시점에 실행중인 SQL문을 정확하게 잡아주는 것이다. 이걸 이용하면 테이블 레벨 제약조건에 걸린 함수가 문제를 일으키고 있음을 명시적으로 확인할 수 있다.

만약 함수내의 SQL이 비효율적이면 튜닝하거나 인덱스를 잡아주고, 함수의 실행계획이 잘못 잡혀 있으면 리컴파일 해주거나 drop 했다가 다시 create 해준다.

Posted by maceo

일반적인 경우 tempdb contention 은 커서, temp table, table 변수의 과도한 사용때문에 일어난다. tempdb contention 을 확인하려면



을 날려봐서 waittype 이 PAGELATCH_XX 또는 PAGEIOLATCH_XX 이고 waitresource가 2:XXXXXXX:XX 처럼 나올 때(2는 tempdb의 dbid임) tempdb contention 이라고 판단하면 된다. 이때 inputbuffer를 봐서 해당 쿼리에서 table 변수, order by, group by, temp table 사용을 제거하는 것이 1차적인 조치법이다.

하지만 tempdb log growth로 인해서 tempdb contention 이 발생하는 경우도 있다. 대기를 보면 마찬가지로 tempdb 에 대기가 생기는데, 정작 inputbuffer로 본 sp들은 tempdb를 그렇게 과도하게 사용하지 않거나 contention이 몇분정도 지난 후 자연스럽게 풀리는 경우 tempdb log growth로 인한 contention이라고 의심해볼 수 있다.

tempdb는 복구모드가 simple 이고 2,3시간에 한번씩 로그를 떨어내는데, 로그 떨어내는 시점에 활성 트랜잭션이 tempdb 에 걸려있으면 로그 떨어내는게 실패하고 log growth 가 일어나게 된다. 굉장히 드문 경우다.

log growth 를 확인하는 것도 어렵다. log growth가 성공한다 하더라도 아무런 기록이 남지 않는다. 단지 파일 크기의 증가로 확인할 수 있을 뿐이다. log growth가 실패하면 그때야 비로소 에러로그에 남는다. log growth 가 실패하는 경우는 과도한 IO부하가 원인일 때가 많다. 이것도 에러로그에 남는다. 그런데 IO부하로 log growth가 실패할 정도로 과도한 IO를 보이는 사이트는 국내에 몇개 없다.-_-;;;

사실 log growth가 성공할 때도 에러로그를 남기는 undocumented trace flag가 있다고 하는데, 뭔지도 모르겠고 undocumented 이므로 MS의 공식적인 기술지원이 없다면 쓰지 않는 것이 현명하겠다. 사실 tempdb log growth로 인한 contention이 일어날 정도의 사이트가 국내에는... 다섯군데도 안될거라 본다. 모두 MS기술지원 받고 있겠지머-_-;;;;;;

조치법은 간단하다.

주기적으로 tempdb log에 대해서 truncate log 를 해서 비워주면 된다.
dbcc loginfo 로 tempdb log 사용량에 대한 모니터링도 지속하자.

Posted by maceo

http://merritt.co.kr/tt/73 에서 인덱스 머지에 관한 기초적인 이야기를 썼는데, 1억건이 넘어가는 엄청나게 큰 테이블들에 조회조건이 굉장히 다양하게 들어올 때 인덱스 구조를 잡는 것이 상당한 고민이 된다.

이때 인덱스 머지 전략을 적절하게 사용하면 인덱스 길이를 최소화하면서 성능도 그렇게 떨어지지 않는 쿼리를 만들어낼 수 있다. 그러면 인덱스 머지를 어떻게 유도할 것이냐가 문제다. 대부분의 경우 서버가 알아서 실행계획을 세워준다지만, 힌트를 줘서 유도할 수 있으면 싶을 때도 있다.

SQL 서버에는 인덱스 머지라는 힌트는 없지만, index 를 여러개 지정함으로써 인덱스 머지 플랜을 유도할 수 있다. 자세한 것은 SELECT 문에 대한 BOL 의 설명을 자세히 읽어보면 된다.


이렇게 하면 ix_1 과 ix_2 를 각각 읽어서 clustered index key 를 이용한 join 을 수행하게 된다. 컬럼의 분포도와 선택성을 고려해서 여러가지로 테스트해보면 이런 전략이 유용할 때가 있다.

Posted by maceo

local server 에서



을 날리면

remote 서버와의 통신상태가 좋지 않을 경우 OLEDB 대기가 걸릴 수 있다.

OLEDB대기는 local server에서



으로 확인가능하며, 이때 waittype은 OLEDB, waitresource 는 remote(SPID:xx) 와 같은 형식으로 나오게 된다. 잽싸게 remote 로 들어가서



를 해보자. 블러킹이 걸렸을 수도 있는데, 이럴때는 블러킹 해결하면 되고  sp_cursorfetch;1 이라고 나오는 경우가 있다. 이거는 remote 연결에서 server cursor 를 쓴다는 의미이다.

(참조) http://www.dbguide.net/know/know101003.jsp?&catenum=14&IDX=542

local server에서 remote로 바로 update를 날리지 말고 remote 에 sp 를 만들어두고 그걸 호출하도록 하자. 아, 물론 remote에 대해서 rpc in, rpc out 설정이 되어 있어야 된다.

Posted by maceo

장난이 아니다 ㅠ_ㅠ

이렇게 날리면 physical_io 가 초당 1500정도 나오고 쿼리 취소하면 순식간에 취소되는 것이

이렇게 하고 돌리면 physical_io 가 1/4 수준으로 줄어들고 쿼리 취소시 롤백도 한참 걸린다.

울회사 메인DB서버 스토리지 같은 괴물 스토리지에서도 똑같은 걸 보니 identity_insert on

옵션이 SQL서버 내부적으로 굉장히 복잡한 처리 과정을 거치는 것으로 생각된다.

insert 할 때 병렬 실행 계획이 세워지지도 않는다.

아. 이런.. ㅠ_ㅠ

Posted by maceo

울회사 권병준 대리가 개발팀에 뿌린 가이드. 실수하기 쉬운 것임...

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


안녕하세요. DB개발팀의 권병준입니다..

오늘도 찾아온 주의사항 한마디입니다.

역시나 길게 글 읽기 싫어하시는 분들을 위해 요약해서 한줄로 먼저 말씀드리겠습니다.

WHERE 조건에서 같은 컬럼에 대해 IS NOT NULL 조건과 <, >, <>, ANS SO ON.. 등의 조건을

같이 사용하지 마십시요.

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

이제 설명 부분..

SP 검수하다보면 간간히 (A를 컬럼명이라고 가정합시다)

WHERE A IS NOT NULL AND A >= @YESTERDAY AND A <= @TODAY

와 같은 조건이 있는 경우가 있습니다.

물론, 해석은 A 가 NULL이 아니면서 어제와 오늘 사이의 값에 대한 조회.. 라는 건데,

저렇게 사용할 필요가 전혀 없습니다.

여기서 NULL 이라는 것에 대해 다시 한 번 재조명을 해보도록 하지요.

NULL 이라는 것은 값없음(이 아니라!) 미확정값이라는 뜻입니다.

아직 해당 컬럼에 값이 정해져있지 않다는 얘기죠. WHERE 조건에서

WHERE A >= @YESTERDAY 라고 조건을 주게 되면 당연히 어제 이후로의 확정된! 값에

대한 것만 조회를 해달라는 의미입니다. 여기서 미확정값은 당연히 제외가 되겠죠.

고로 저렇게 확정된 값에 대한 조회조건을 주면서 또다시 미확정된 값을 제외하라는 조

건을 주는건 CPU 부담만 증가시킬 뿐입니다. (Bookmark Lookup시에 꽤 부담을 주게 됩

니다.)

실제 간단한 실험을 통해서 비용적인 측면의 비교를 한 번 해 보겠습니다.

하나의 테이블을 만들고 여기에 테스트 값을 넣어줍니다.

CREATE TABLE NULL_TEST (
SEQ INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
NULL_COL INT NULL
)

DECLARE @CNT INT
SET @CNT = 1
WHILE @CNT < 5000
BEGIN
INSERT INTO NULL_TEST VALUES (NULL)
INSERT INTO NULL_TEST VALUES (@CNT)
SET @CNT = @CNT + 1
END

10000개의 데이터가 생성이 되었습니다. 여기서 제가 위에 말한 조회조건대로 IS NOT NULL이라는

조건을 추가로 준 것과 아닌 것에 대해서 각각 간단하게 조회를 해 봅니다.

SELECT * FROM NULL_TEST WHERE NULL_COL < 10 AND NULL_COL IS NOT NULL

SELECT * FROM NULL_TEST WHERE NULL_COL < 10

물론 결과값은 같습니다. 하지만 조회에 해당되는 비용은 다르게 나옵니다.

아래 그림을 봐 주십시요.

위의 쿼리와 아래의 쿼리 비용이 63.64% VS 36.36%로 차이가 나는 것을 보실 수 있을 겁

니다. 안 써도 되는 컬럼 추가로 인해 비용 차이가 이만큼이나 벌어졌습니다. 행 수가

많아지면 많아질수록 이 격차는 더욱 커집니다.

SP 작성하실 때에 여러분의 주의를 부탁드립니다. ^^

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

이건 좀 더 알고 싶으신 분들을 위해 추가합니다.

ANSI_NULLS 라는 옵션을 대부분 알고 계실 것입니다. 대부분의 DB에서는

(G마켓의 MS-SQL SERVER 포함) ANSI_NULLS ON 으로 사용합니다.

(NULL을 본래 그대로의 뜻인 미확정값으로 사용하겠다는 의미입니다.

MS-SQL SERVER에서는 NULL을 확정값처럼 사용하도록 할 수 있습니다.

특이한 옵션인거죠? ㅎㅎ (오라클 사용하던 저로서는 마냥 신기해했던 옵션입니다)

그렇다면..

SET ANSI_NULLS OFF 로 해주고 위의 쿼리를 다시 돌려보면 결과가 어떻게 나올까요?

............ 한 10초쯤 생각해 보시기 바랍니다. ...........................

예상하셨다시피 NULL 값을 확정값처럼 DB에서 생각하기 때문에

위의 두 쿼리는 아래처럼 같은 비용을 내게 됩니다.

하지만, 울회사에서는 ANSI_NULLS 를 ON 으로 주고 사용하기 때문에 여러분들은 역시

SP 작성하실 때에 주의를 기울여 주시지 않으면 안된다는 겁니다. ^^

결론 : 여러분의 작은 관심이 시스템을 조금 더 안정적으로 만들 수 있습니다.

그럼 또 좋은 '꺼리' 생기면 찾아뵙겠습니다. ㅎㅎ

Posted by maceo