Linked Server 이렇게사용하자! 엑셈컨설팅본부 / SQL Server 팀김성식 Q( 고객 ) : 연결된서버를사용하는쿼리인데실행시간이굉장히오래걸리네요. 1 분이상걸립니다. 왜그런가요???? ( 아.. MSSQL Server 못쓰겠네!!! ) A( 나 ) : 흠 왜그럴까요? ^^ ㅋㅋ 연결된서버가무엇인지간단히알아보고, 정말문제점이 MSSQL Server 이기때문인가? 개선방법은존재하지않는것인가? 차근차근알아보자. Linked Server 의용도 SQL Server 데이터베이스엔진에서 SQL Server 인스턴스외부의 OLE DB 데이터원본에대해명령을실행할수있도록연결된서버를구성한다. 일반적으로연결된서버는데이터베이스엔진에서 SQL Server 의다른인스턴스또는 Oracle 과같은다른데이터베이스제품에있는테이블이포함된 Transact-SQL 문을실행할수있도록구성된다. Microsoft Access 및 Excel 을포함한많은유형의 OLE DB 데이터원본을연결된서버로구성할수있으며연결된서버에는다음과같은이점이있다. SQL Server 외부에서데이터에액세스할수있음. 기업전체에걸쳐유형이다른데이터원본에대해분산쿼리, 업데이트, 명령, 트랜잭션등을수행할수있음. 다양한데이터원본을유사하게처리할수있음. 다음그림은연결된서버구성의기본사항을보여준다. 520 2013 기술백서 White Paper
연결된서버는일반적으로분산쿼리를처리하는데사용된다. 여기서분선쿼리란다른여러데이터원본의데이터를액세스하는것을의미하며이러한데이 터원본은동일컴퓨터나다른컴퓨터에저장될수있다. 클라이언트응용프로그램이연결된서버를통해분산쿼리를실행할때 SQL Server 는명령을 구문분석하고 OLE DB 로요청을보낸다. 행집합요청은공급자에대해쿼리를실행하거나공 급자로부터기본테이블을여는형식일수있다. 연결된서버를통해데이터를반환하는데이터원본의경우해당데이터원본에대한 OLE DB Provider(DLL) 는 SQL Server 인스턴스와같은서버에있어야한다. 타사 OLE DB Provider 를사용하는경우 SQL Server 서비스가실행되는계정에는공급자가 설치된디렉터리및모든하위디렉터리에대한읽기및실행권한이있어야한다. Part 3 SQL Server 521
Linked Server 가미치는성능이슈. 기술지원을수행하다보면고객담당자에게이러한질문을많이받는다. 저희가 Oracle 를 Linked Server 로구성하여사용중인데성능이느려요. 원인을분석하면크게 3 가지유형의패턴을찾을수있었다. 첫째, Linked Server(Oracle) 을사용한최적화되지못한쿼리. 사용되는쿼리는 Oracle 로요청되는쿼리로써 Oracle 환경에최적화가되어져야한다는사실을이해하지못하는경우. 둘째, 네트워크성능이슈. 셋째, 비효율적인옵티마이저의실행계획. 세번째부분이이번백서에서중점으로다룰소재이며, 실제사례를통해살펴보도록하자. 문제쿼리 > SELECT A.ZUONR AS OrderNo 이하생략 ~ FROM HANDW.HANDW.HANDWADM.BSID AS A WITH (NOLOCK) LEFT JOIN HANDW.HANDW.HANDWADM.VBRP AS B WITH (NOLOCK) ON A.BELNR = B.VBELN AND B.POSNR % 100 = 0 WHERE A.BUDAT BETWEEN @sdate + ' 00:00:00.000' AND @edate + ' 23:59:59.999' AND A.KUNNR IN (SELECT RetailStoreCd FROM @SaleChnl_T) AND A.BLART IN ('RV', 'DR') Q( 고객 ) : 연결된서버를사용하는쿼리인데실행시간이굉장히오래걸리네요. 1 분이상걸립니다. 왜그런가요???? ( 아.. MSSQL Server 못쓰겠네!!! ) A( 나 ) : 흠 왜그럴까요? ^^ ㅋㅋ한번상태를확인해볼가요? 그로부터잠시후. A( 나 ) : 원격테이블을가져오는단계에서부하가발생되고있네요. 조인후행테이블을원격서 버로부터많은건수의데이터를가져오는단계에서성능저하가발생하고있네요. 522 2013 기술백서 White Paper
Q( 고객 ) : 그럼어떻게하면되지요? A( 나 ) : 연결된서버를사용하여원격서버의결과셋을가져올때는 Cursorfetch 동작을하게됨으로네트웍 I/O 가과다하게발생되어성능이저하됩니다. 연결된서버를반대로구성하여값이적은서브쿼리테이블 ((@SaleChnl_T) 값을가져다가 HANDW 에서수행되도록하면두테이블간에 LEFT OUTER JOIN 한결과셋만전송. 엄청난네트웍 I/O 를개선할수있겠어요. Q( 고객 ) : 아하그렇군요. 적용하니잘됩니다. 감사합니다. 문제쿼리플랜의일부이다. 2131234 1 --Remote Query(SOURCE:(HANDW), QUERY:(SELECT "Tbl1003"."VBELN" "Col1115","Tbl1003"."FKIMG" "Col1117","Tbl1003"."MATNR","Col1118","Tbl1003"."ARKTX" "Col1119","Tbl1003"."KZWI1" "Col1120" FROM "HANDW"."HANDWADM"."VBRP" "Tbl1003" WITH (NOLOCK) WHERE CONVERT(int,"Tbl1003"."POSNR",0)%(100)=(0) ORDER BY "Col1115" ASC)) HANDW.HANDWADM.VBRP 테이블전체가조회되는상황에서 Cursorfetch 동작원리로 인한과도한네트웍 I/O 가성능저하문제의원인이다. 이해를좀더돕기위해, 간단한테스트로동작방식을확인해보자. 테스트환경은아래와같으며, NODE1\REP 서버에서 TEST29 연결된서버를이용하여쿼리가수행된다. Part 3 SQL Server 523
select COUNT(*) from test29.northwind.dbo.customers select COUNT(*) from test29.northwind.dbo.orders -- 91 건 -- 186,734 건 DBCC freeproccache # 테스트쿼리 > select * from test29.northwind.dbo.customers a left outer join test29.northwind.dbo.orders b on a.customerid = b.customerid where a.phone in (select phone from customers2 where customerid <> 'RICAR' ) TEST29 서버 customers 테이블총 91 건, orders 테이블 186,734 건. NODE1\REP 서버의 customers2 테이블과 TEST29 서버의 customers 테이블정보는일치하 며 RICAR 고객을제외한고객들의주문정보를확인하는쿼리이다. # 실제실행계획 : 요약 > 6512 1 --Merge Join(Left Outer Join, 90 1 --Sort(ORDER BY:([test29].[Northwind].[dbo].[customers].[CustomerID] ASC)) 90 1 --Merge Join(Inner Join, 90 1 --Sort(DISTINCT ORDER BY:([T10].[dbo].[customers2].[Phone] ASC)) 90 1 --Table Scan(OBJECT:([T10].[dbo].[customers2]), 0 0 --Compute 91 1 --Remote Query(SOURCE:(test29), QUERY:(SELECT 0 0 --Compute 186734 1 --Remote Query(SOURCE:(test29), QUERY:(SELECT "Tbl1003"."OrderID" #IO or 수행시간 > (6512 개행이영향을받음 ) 테이블 'customers'. 검색수 1, 논리적읽기수 4, 물리적읽기수 0, 미리읽기수 0, LOB 논리적읽기수 0, LOB 물리적읽기수 0, LOB 미리읽기수 0. (10 개행이영향을받음 ) (1 개행이영향을받음 ) SQL Server 실행시간 : CPU 시간 = 921 밀리초, 경과시간 = 20083 밀리초 524 2013 기술백서 White Paper
TEST29 서버의 customers 테이블과 NODE1\REP 서버의 customers2 테이블와의조건에서얻어진 90 건과 TEST29 서버의 orders 테이블 186,734(Table Scan) 건을 Merge Join 하여최종 6512 건을조회하였다. 실제사례와동일하게조인후행테이블이 Table Scan 되어많은네트웍 I/O 및과다실행시간을 유발하였다. 어떻게하면좋을까? 1. 연결된서버는원격서버와통신을함으로네트워크가불안정한것같은데요? 2. 과도한연결된서버사용은 Microsoft 가권장하지않습니다. 흠.. 그럴싸하다. ^^;;; 하지만아무런근거자료없이이러한답변을하면서두의대화글처럼못쓰겠다라는말이나올것이다. 우리가아무리비싸고좋다라는 DSLR 카메라를샀지만사용법을익히지못한다면똑딱이보다못한물건이되는것과동일하다. 우리는그래도배운사람들이고 ( ㅋ ) 원인이무엇인지알았으니좋은방법이없는지생각해보자. Linked Server 의성능이슈개선방법. 문제쿼리의패턴을분석해보면조회대상테이블들이한서버에존재하는것이아니라서로다른원격서버에존재함으로 TEST29 서버의 customers 테이블과 NODE1\REP 서버의 customers2 테이블에서얻은결과로 TEST29 서버의 orders 테이블과조인을하게되는데, 여기서 orders 테이블을가져오는단계에서 Cursorfetch 동작에의한과도한네트웍 I/O 가발생하게된다. Part 3 SQL Server 525
결국, 네트웍 I/O 를최소화할수있는솔루션을찾으면문제를해결할수있을것이다. 해서아래와같이 NODE1\REP 서버에서프로시져를요청하는방식으로 NODE1\REP 서버에있던연결된서버를제거하고, TEST29 서버에다 NODE1\REP 서버로의연결된서버를구성하여 TEST29 서버에서 NODE1\REP 서버 customers2 테이블의 91 건을 Remote Query 후에조인되도록유도하였다. 그결과, 결과값 6512 건만 NODE1\REP 서버로리턴하게되었으며서버간에네트웍 I/O 감소와네트웍환경에민감한연결된서버사용은효율적이게되었다. # 테스트 > NODE1\REP 서버에아래프로시져를생성하고 TEST29 서버에서는프로시져를실행. -- TEST29 : 저장프로시져생성 create procedure Lk as 526 2013 기술백서 White Paper
select * into #tmp from (select phone from [node1\rep].t10.dbo.customers where customerid <> 'RICAR') a select * from customers a left outer join orders b on a.customerid = b.customerid where a.phone in (select phone from #tmp); -- NODE1\REP : 저장프로시져실행 execute test29.northwind..lk 실제실행계획 > 6512 1 select * from {IRowset 0x20E2A58100000000} 6512 1 --Remote Scan(OBJECT:(STREAM)) (6512 개행이영향을받음 ) (2 개행이영향을받음 ) (1 개행이영향을받음 ) SQL Server 실행시간 : CPU 시간 = 47 밀리초, 경과시간 = 512 밀리초 SQL Server 실행시간 : CPU 시간 = 47 밀리초, 경과시간 = 518 밀리초 그리고 NODE1\REP 서버에서 TEST29 서버로의프로시져 ( 그림 _1) 와 TEST29 서버에서프로시져내부조회쿼리 ( 그림 _2) 를각각실행시켜실행계획을비교하니동일한플랜을보였다. 즉, Cursorfetch 동작방식으로인한과도한네트웍 I/O 가원인임을뒷받침해주는증거자료가되겠다. 그림 _1 Part 3 SQL Server 527
그림 _2 마치며 실행시간이약 40 배가량개선된것을확인하였다. 이렇듯 DB 튜닝은크게어려운것이아니다. 우리가일상생활에서 PC 와자동차등을튜닝하듯이 DB 튜닝또한약간의기본전문지식 + 관심과이해가전부라생각한다. 아무리뛰어난제품에도장점만이있는것은아니다. 단점또한존재하지만그것을대체가능한솔루션이존재한다면단점이라말하기힘들다. 고정관념을깨는것또한튜닝의필수요소! 앞으로우리는옵티마이져의동작방식을이해하고상황에맞게적절한대체솔루션찾는태도로임하자. 528 2013 기술백서 White Paper