가늘어도 긴놈이 장땡

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

박노철
maceo.park@gmail.com
http://merritt.co.kr

1.
remote server 에 SP 를 만들고 그걸 호출한다. 이게 젤 좋다.

2.
remote 서버에 SP를 만들 권한이 없다면 OPENQUERY 를 사용한다. 이렇게 하면 Linked Server 쪽을 호출할때 메타데이터의 교환이 최소화되어서 매우 빠른 속도로 쿼리가 수행된다.

insert into OPENQUERY(REMOTE, 'select col1, col2 from test.dbo.test_table where 1=0')
values ('aaaa','bbb')

update OPENQUERY(REMOTE, 'select col1, col2 from test.dbo.test_table where col3=1')
set col1 = 'aaaa', col2 = 'bbb'

참고1) OPENQUERY 의 두번째 인자인 SQL은 변수를 받을 수 없다. 만약 변수를 넘겨야 한다면 update 문을 통째로 동적SQL로 만들고 그걸 sp_executesql 로 호출한다. MSDN 에 이 내용을 담은 KB문서가 있는데 못찼겠다.

참고2) update 의 대상이 OPENQUERY 로 읽어오는 remote table 일 경우 update OPENQUERY().. set ... from localtable1 join OPENQUERY() 같은 형태의 SQL은 실행되지 않을 것이다. (긴가민가하다-_-) OLEDB가 뭔가 에러를 냈던 것으로 기억.

참고3) OPENQUERY 와 관련된 문서들

SQL Server 에서 Oracle Table Query 하기

SQL Server 에서 Oracle 에 DDL 수행하기

3.
left join 에는 remote table 을 사용하지 않는다. 예를 들어

select *
from dbo.localtable l
left join remotesvr.test.dbo.test_table r on l.col1 = b.col2

와 같이 left join 에 remote table 을 쓰면 remote table scan 을 해서 몽땅 다 읽어온 다음에 outer join 을 수행할 가능성이 높다. 따라서 실행계획을 반드시 확인해봐야 한다. SQL서버 옵티마이저가 이럴때 보면 좀 멍청한 것 같다. inner loop join 은 괜찮다. 그런데 remote table 과 loop join 을 한다는 것 자체가 network io 를 발생시키는 일이기 때문에 당연히 local table 과 join 하는 것 보다 엄청나게 느릴 수 밖에 없다.

결국 결론은, DB분산을 해도 전혀 상관없는 업무영역끼리 잘 나눠야 한다는 것이다...

Posted by maceo

박노철
maceo.park@gmail.com
http://merritt.co.kr


MS 플랫폼에서 대규모의 OLTP 를 하다보니 이것저것 테스트해보다가 알아낸 사실.

. ODBC DSN 을 쓴다는 것은 순수한 의미에서 ODBC 를 쓰는 것이 아니라 Microsoft OLE DB Provider for ODBC driver 를 거쳐서 ODBC 를 사용하는 것이다. 따라서 직접 SQL OLEDB Provider 를 쓰는 것보다 느리다.

. MS플랫폼에서는 두가지의 DB연결 풀링이 존재한다. 하나는 다들 아는 ODBC 커넥션 풀링이고 다른 하나는 OLEDB 세션 풀링이다. 그런데 ODBC DSN 을 써도 사실은 MS OLEDB Provider for ODBC driver 를 사용하는 것이므로 디폴트로 OLEDB 세션풀링을 사용하게 된다. 따라서 ODBC DSN 으로 DB 연결을 해도 ODBC 퍼포먼스 카운터에는 아무것도 잡히지 않는다. ODBC API 를 사용하면 퍼포먼스 카운터가 잡힌다. OLEDB 세션풀링에 관련된 퍼포먼스 카운터는 없는 것으로 알고 있다.

. ODBC DSN 을 쓰면 DB콜 할 때마다 DSN 를 찾기 위해 레지스트리를 뒤지게 된다. OLEDB 를 쓰면 그렇지 않다.

. SQL Server SP3 이후에 ODBC DSN 으로 연결이 이뤄지면 sp_reset_connection 이 호출되지 않는다. 풀링을 통해서 DB연결을 재사용하기는 하지만 재사용되는 연결정보가 깨끗이 정리되지 않는다. 만약 이전 연결에 분리된 트랜잭션이 있었나 temp table 을 지우지 않았다면 이 정보가 그대로 남아있게 된다. OLEDB 를 쓰면 그렇지 않다.

. 만약 DSN=TEST;UID=TEST;PWD=TEST 가 있고 DSN=TeST;UID=TEST;PWD=TEST 이 있다면 커넥션 풀은 두개가 만들어진다. 그리고 서로 다른 프로세스(exe) 에서 DB연결을 시도할 때도 프로세스마다 커넥션 풀이 만들어진다. 즉 같은 프로세스에서 같은 connection string 을 사용해야 동일한 커넥션 풀을 사용하게 된다. connection string 은 대소문자까지 구분한다. 커넥션 풀이 복수개가 생기면 DB서버에 일시에 엄청난 숫자의 커넥션이 몰려버릴 수 있는 가능성을 배제하지 못한다.

. ADODB.Connection 의 ConnectionTimeout 은 커넥션 풀에서 놀고 있는 연결을 얻어올 때까지 기다리는 시간 + DB에 연결을 맺을 때까지 기다리는 시간을 모두 포함한다.

. ADODB.Connection 의 CommandTimeout 과 ADODB.Command 의 CommandTimeout 이 동시에 설정되었다면 ADODB.Command 의 설정치가 적용된다.

. ADODB.Connection 의 Execute 를 쓰지말고 ADODB.Command 를 사용하라. 무조건! 이걸 써야 한다. 스토어드 프로시저와 같이 쓰는게 제일 좋다. SP 실행계획 재사용으로 인한 CPU 사용률 감소, 컴파일LOCK 회피등의 효과가 있다. 하지만 가장 큰 이득은 CPU 가 튀지 않는다는 것이다. 실행계획이 한번 제대로 올라가면 SP 리컴파일이 일어나서 엄한 실행계획을 세우지 않는한 CPU가 매우 안정적인 모습을 보인다. 그리고 갑자기 CPU가 튀면 십중팔구 SP하나가 엄한 실행계획을 타고 있는 것이므로 이넘만 sp_recompile 해주면 다시 조용해진다. 즉, 튀는 놈을 잡아내기가 쉽다.

. 동적SQL은 무조건 피해라. 도저히 안되겠으면 sp_executesql 을 사용하라. 모 사이트에서 DB서버 CPU가 90넘던거를 모든 동적SQL을 sp_executesql 로 바꿔주기만 해도 CPU가 50근처로 떨어지고 CPU사용량도 안정적으로 되었다고 한다.

. 사실 기계나 DB가 딸린다기 보다는 개발을 잘못해서 시스템이 꺽꺽대는 경우가 90% 이상이다. 기계탓, DB탓 하지말고 자신을 탓하는 자세를 가지자-_-;

Posted by maceo