SQL Server 에서 SQL 튜닝시알아야할힌트와사용 방법 엑셈컨설팅본부 /DB 컨설팅팀박성호 Optimizer 가 SQL 을해석할때항상최적의실행계획을생성하지는못한다. 복잡한 SQL 일수록최적의실행계획을생성하기위해고려해야할대상 (Table, Index 가많은경우 )

Size: px

Start display at page:

Download "SQL Server 에서 SQL 튜닝시알아야할힌트와사용 방법 엑셈컨설팅본부 /DB 컨설팅팀박성호 Optimizer 가 SQL 을해석할때항상최적의실행계획을생성하지는못한다. 복잡한 SQL 일수록최적의실행계획을생성하기위해고려해야할대상 (Table, Index 가많은경우 )"

예리 강
5 years ago
Views:

1 SQL Server 에서 SQL 튜닝시알아야할힌트와사용 방법 엑셈컨설팅본부 /DB 컨설팅팀박성호 Optimizer 가 SQL 을해석할때항상최적의실행계획을생성하지는못한다. 복잡한 SQL 일수록최적의실행계획을생성하기위해고려해야할대상 (Table, Index 가많은경우 ) 이많기때문에, 실행계획생성시 SQL 의 Cost 를잘못계산하여최적의실행계획을세우지못하는경우가발생한다. SQL 작성자가 SQL 을아무리효율적으로작성하더라도 Optimizer 는가끔비효율적인실행계획으로작성자를당황하게하는경우들이있다. 그런데이러한성능문제를해결하기위해서는 Optimizer 가 SQL 의올바른 Access Path ( 조인순서, 조인방법, 데이터액세스등 ) 를가진가장효율적인실행계획을수립할수있도록 SQL 작성자의개입이필요하다. 왜냐하면, Optimizer 가비효율실행계획을수립하였지만, 실행계획을수립하는것은 SQL 이가진오브젝트나기타관련정보를토대로 SQL 의 Cost 를계산하는것이므로비효율실행계획을수립하는원인을 SQL 작성자가찾아내기란쉽지않다. 그러므로, Optimizer 에게 SQL 작성자의의도를전달하여효율적인 Access Path 를가진실행계획을수립할수있도록도와줄필요가있다. 위와같은경우에해당 SQL 에힌트를사용하는것을고려할수있다. 왜냐하면, 힌트는 SQL 작성자가의도한대로 Optimizer 가 SQL 을해석할수있도록유도할수있는키워드이기때문이다. 또한, 힌트는 SQL 에적용하더라도결과 ( 추출데이터 ) 에영향을전혀주지않기때문에데이터무결성에대한부담은가지지않아도된다. 그러나 SQL Server 에서의힌트사용은타 DBMS 보다도더주의해야한다. 잘못된힌트사용으로인해 SQL 구문오류가발생하여 Application 이동작하지않는위험한상황을연출할수있기때문이다. 그리고잘못사용된힌트로인해 SQL 의성능문제를개선하지못하고, 성능문제를더욱가중시킬수있으므로주의해서사용해야한다. 이렇게힌트는 Optimizer 가제대로효율적인실행계획을수립하지못한경우에사용하면 SQL 의성능개선하는데효과적이다. 그러나잘못사용된힌트는 SQL 의성능문제외의또다른문 기술백서 White Paper

2 제를야기할수있기때문에, 힌트를제대로사용하기위해반드시올바른사용법을알고있어야 한다. 언제힌트를사용하는게효율적인가? Optimizer가비효율실행계획을수립하는경우 SQL 에힌트를사용하는가장대표적인경우는 SQL 작성자가의도하지않은비효율실행계획으로수행되어성능문제를일으키는경우일것이다. 대개이런경우는 Optimizer 가 SQL 이가진정보를토대로 Cost 계산하여비효율실행계획을수립하는경우로, 이럴때효율적인실행계획으로수행될수있도록개입이필요하다. 물론, SQL 의성능문제를 SQL 을재작성하여해결할수있다면가장바람직한경우이겠지만, SQL 을재작성한다고해서항상효율적인실행계획으로수행된다고보장할수는없다. 또한, SQL 의성능문제를해결하는데소요시간과다로인해성능문제해결이지연됨으로써더욱심각한문제를초래할수있으므로, 이럴때는 SQL 의실행계획을제어하는힌트를적용하여빠른조치를하는것이바람직할수있다. 인덱스구성의변경에의한실행계획이상 Optimizer 가 SQL 의 Cost 계산시중요한정보중하나가인덱스구성정보이다. 그런데기존테이블에인덱스가추가생성되거나, 기존인덱스가삭제되는등인덱스구성에변경이되는경우 SQL 의실행계획에이상이발생할소지가있고, 이로인해성능문제를유발할수있다. 이런경우 SQL 의성능문제를해결하는방안으로힌트를사용할수있다. 그러면이제부터 SQL Server 에서 SQL 튜닝시알아야할기본힌트에대해알아보고, 그사용 방법을간단한테스트를통해알아보도록하자. 테스트를위해아래의스크립트를사용하여테 스트데이터를생성하자. Script. 테스트에사용될스크립트 create database pshdb; Part 3 SQL Server 497

3 use pshdb; if object_id('sql_t1') is not null drop table sql_t1 if object_id('sql_t2') is not null drop table sql_t2 if object_id('sql_t3') is not null drop table sql_t3 create table sql_t1 (id varchar(10), name varchar(5), regdate varchar(8)) create table sql_t2 (id varchar(10), telseq int, telno varchar(14), teldiv varchar(1)) create table sql_t3 (id varchar(10), addrseq int, addr varchar(100), addrdiv varchar(1)) insert into sql_t1 values ('cust1', 'aman', ' ') insert into sql_t1 values ('cust2', 'bman', ' ') insert into sql_t1 values ('cust3', 'cgirl', ' ') insert into sql_t2 values ('cust1', 3, ' ','b') insert into sql_t2 values ('cust2', 1, ' ','a') insert into sql_t2 values ('cust2', 2, ' ','b') insert into sql_t2 values ('cust3', 4, ' ','c') insert into sql_t3 values ('cust1', 2, ' 서울시송파구 ', '2') insert into sql_t3 values ('cust2', 1, ' 서울시서초구 ','1') insert into sql_t3 values ('cust3', 3, ' 서울시강서구 ','3') insert into sql_t3 values ('cust3', 4, ' 서울시양천구 ','4') 기술백서 White Paper

4 create index idx01_sql_t1 on sql_t1 (id) create index idx02_sql_t1 on sql_t1 (name) create index idx01_sql_t2 on sql_t2 (id, telno) create index idx02_sql_t2 on sql_t2 (telno) create index idx01_sql_t3 on sql_t3 (id, addr) create index idx02_sql_t3 on sql_t3 (addr) 힌트의종류와사용방법 힌트의사용목적은 SQL 의실행계획을효율적인 Access Path 로유도하고, 힌트를적용한시점의실행계획을유지하는것이다. 이러한힌트의사용목적을충족하면서힌트구문을잘적용하기위해서는먼저 SQL 의효율적인 Access Path 를판단하기위한필수 3 요소를알아야한다. 왜냐하면, 성능문제를가진모든 SQL 을개선하는데이 3 요소만잘점검하면거의대부분의성능문제는개선할수있고, SQL 의실행계획을고정하는데반드시필요하기때문이다. [SQL 튜닝시알아야할 Access Path 의구성요소 ] 조인순서 : SQL 수행시먼저수행되는테이블 (Driving Table) 과 FROM 절의테이블간의조인순서는항상존재한다. 이러한조인순서는 SQL 을수행할때효율적인 Access Path 로수행되기위한가장중요한요소이다. 조인방법 : 선행테이블과후행테이블간의조인방법, 그리고선행결과셋과후행테이블의조인방법을결정하는것은 SQL 의효율적인 Access Path 유도하는데필수요소이다. 데이터액세스 : 데이터를액세스할때어떤방법 (Index Scan OR Full Table Scan 등 ) 으로수행할지를결정하는것으로, SQL 의효율적인 Access Path 를결정하는데반드시필요한요소이다. 또한, 조인연결칼럼에대해서도고려해야한다. Optimizer 가 SQL 에대한최적의실행계획을세울때항상존재하고, 반드시결정되어야하는 요소들이바로앞에서알아본조인순서, 조인방법, 데이터액세스이다. Optimizer 가최적의실 Part 3 SQL Server 499

5 행계획을세우기위해반드시결정하여야하는이요소들이바로 SQL 에힌트를적용할때반드시적용해야하는구성요소가된다. 왜냐하면, 힌트의사용목적은 SQL 을효율적인실행계획으로수립하고수립된실행계획은변하지않도록하는데있기때문이다. 최소한이세가지구성요소들에대해정의가되어야기본형태의 SQL 에대한효율적인실행계획을수립하고, 수립된실행계획을고정시켜, 실행계획변경에의한성능문제를피할수있다. 물론, 힌트를적용할때기본형태의 SQL 에뷰, 서브쿼리등에대한부분도고려해야하지만, 가장중요한힌트적용의기본형태는조인순서, 조인방법, 데이터액세스를효율적으로결정하는것이다. 그러면테스트를통해조인순서, 조인방법, 데이터액세스관련힌트와그사용방법을알아보도록하자. 조인순서힌트 힌트 : force order 힌트의미 : From 절에나열된순서대로조인순서를유도하는힌트 From 절에나열된순서가조인순서가되기때문에, 최초 SQL 에 FORCE ORDER 힌트를적용 하기전 From 절의순서를먼저조정해야한다. 또한, SQL 을수정할때 ( 테이블순서가조정될 때 ) 는 SQL 에적용된힌트가효율적인지를다시점검해야한다. 조인순서 [1]. SQL_T1, SQL_T2 순서로수행 from sql_t1 t1 inner join sql_t2 t2 on t1.id = t2.id option (force order) --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003])) --Nested Loops(Inner Join, OUTER REFERENCES:([t1].[id])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1])) --Index Seek(OBJECT:([pshdb].[dbo].[sql_t2].[idx01_sql_t2] AS [t2]),... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t2] AS [t2]),... 조인순서 [1] 은 SQL_T1 테이블을먼저수행후 SQL_T2 테이블과 Nested Loops Join 으로수해되었다. SQL 을확인해보면, SQL 의맨마지막라인에 option (force order) 구문을적용하 기술백서 White Paper

6 여조인순서가 From 절순서대로수행되도록유도한것을알수있다. From 절순서는 SQL_T1, SQL_T2 순이다. 조인순서 [2]. SQL_T2, SQL_T1 순서로수행 from sql_t2 t2 inner join sql_t1 t1 on t2.id = t1.id option (force order) --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003])) --Nested Loops(Inner Join, OUTER REFERENCES:([t2].[id])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t2] AS [t2])) --Index Seek(OBJECT:([pshdb].[dbo].[sql_t1].[idx01_sql_t1] AS [t1]),... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1]),... 앞의조인순서 [1] 과같이힌트구문을동일하며, From 절의테이블순서를 SQL_T2, SQL_T2 순서대로나열하여조인순서를조정하였다. 조인방법힌트 힌트 : hash, loop, merge 힌트의미 : 조인방법을유도하는힌트 조인방법 [1]. 일괄적으로특정조인방법을지정하는경우 조인방법 [1] 은테이블조인은 SQL_T1, SQL_T2, SQL_T3 순서로수행되고, SQL_T2 와 SQL_T3 테이블을 Hash Join 으로수행하도록힌트를적용한것이다. SQL(1) 은 SQL 의 OPTION 절에조인순서와조인방법을유도하는힌트를추가하는것이고, SQL(2) 는 ANSI SQL 의조인구문에각힌트를지정한것이다. SQL(1) from sql_t1 t1 inner join sql_t2 t2 on t1.id = t2.id Part 3 SQL Server 501

7 inner join sql_t3 t3 on t1.id = t3.id option (force order, hash join) --Hash Match(Inner Join, HASH:([t2].[id])=([t3].[id]),... --Hash Match(Inner Join, HASH:([t1].[id])=([t2].[id]),... --Table Scan(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t2] AS [t2])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t3] AS [t3])) SQL(2) from sql_t1 t1 inner hash join sql_t2 t2 on t1.id = t2.id inner hash join sql_t3 t3 on t1.id = t3.id option (force order) --Hash Match(Inner Join, HASH:([t2].[id])=([t3].[id]),... --Hash Match(Inner Join, HASH:([t1].[id])=([t2].[id]),... --Table Scan(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t2] AS [t2])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t3] AS [t3])) 조인방법 [1] 의 SQL(1) 과 SQL(2) 는동일한조인순서와조인방법으로수행된다. 그런데 SQL(1) 과 SQL(2) 의테이블중 SQL_T2 는 Hash Join 으로수행하고, SQL_T3 는 Nested Loops Join 으로수행해야하는경우와같이 Hash Join 이나 Nested Loops Join 등한가지조인방법만가지지않는다면항상 SQL 을 SQL(2) 와같이작성 (ANSI SQL) 후각테이블의조인방법을별도로지정해야한다. 각테이블의조인방법지정은다음의조인방법 [2] 에서확인해보자. 조인방법 [2]. 각테이블별조인방법을지정하는경우 앞에서언급했듯이 From 절의테이블들에각각조인방법을달리적용해야한다면다음의 SQL(1), SQL(2) 와같이 ANSI SQL 의조인구문에힌트를추가하면된다. SQL(1). SQL_T2 는 Hash Join, SQL_T3 는 Nested Loops Join from sql_t1 t1 inner hash join sql_t2 t2 on t1.id = t2.id 기술백서 White Paper

8 inner loop join sql_t3 t3 on t1.id = t3.id option (force order) --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1006])) --Nested Loops(Inner Join, OUTER REFERENCES:([t2].[id])) --Hash Match(Inner Join, HASH:([t1].[id])=([t2].[id]),... --Table Scan(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t2] AS [t2])) --Index Seek(OBJECT:([pshdb].[dbo].[sql_t3].[idx01_sql_t3] AS [t3]),... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t3] AS [t3]),... SQL(2). SQL_T2 는 Nested Loops Join, SQL_T3 는 Hash Join from sql_t1 t1 inner loop join sql_t2 t2 on t1.id = t2.id inner hash join sql_t3 t3 on t1.id = t3.id option (force order) --Hash Match(Inner Join, HASH:([t2].[id])=([t3].[id]),... --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003])) --Nested Loops(Inner Join, OUTER REFERENCES:([t1].[id])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1])) --Index Seek(OBJECT:([pshdb].[dbo].[sql_t2].[idx01_sql_t2]... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t2] AS [t2]),... --Table Scan(OBJECT:([pshdb].[dbo].[sql_t3] AS [t3])) SQL(3) from sql_t1 t1, sql_t2 t2, sql_t3 t3 where t1.id = t2.id and t1.id = t3.id --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003])) --Nested Loops(Inner Join, OUTER REFERENCES:([t3].[id])) --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1006])) --Nested Loops(Inner Join, OUTER REFERENCES:([t1].[id])) --Table Scan(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1])) Part 3 SQL Server 503

9 --Index Seek(OBJECT:([pshdb].[dbo].[sql_t3].[idx01_sql_t3]... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t3] AS [t3]),... --Index Seek(OBJECT:([pshdb].[dbo].[sql_t2].[idx01_sql_t2] AS [t2]),... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t2] AS [t2]),... 각테이블의조인방법을다르게적용해야하는경우에성능문제를발생시키는 SQL 이 SQL(3) 과같이작성되어있다면, SQL Server 가제공하는조인방법힌트를적용할수없다. ( 단, OPTION 절을이용하여모든테이블의조인방법을지정하는것은가능하다.) 이런경우 SQL 을조인방법 [2] 의 SQL(2) 와같이재작성하여각테이블에조인방법힌트를적용하면된다. 데이터액세스힌트 힌트 : index 힌트의미 : 데이터액세스시인덱스를사용하여수행하도록유도하는힌트 SQL_T1 테이블의 ID 칼럼에는인덱스가생성되어있다. 그러나 SQL_T1 테이블에입력된데이터가 3 건으로많지않아, Optimizer 는 SQL(1) 과같이 Full Table Scan 으로수행하였다. 그런데 SQL_T1 테이블의데이터에대한액세스방식이 Full Table Scan 보다인덱스스캔이유리하다고가정한다면 SQL(2), SQL(3) 과같이테이블명뒤에인덱스힌트를적용하여원하는인덱스를사용할수있다. SQL(1) varchar(10) = 'cust1' from sql_t1 t1 where id --Table Scan(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1]),... SQL(2) varchar(10) = 'cust1' from sql_t1 t1 with(index(idx01_sql_t1)) where id 기술백서 White Paper

10 --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) --Index Seek(OBJECT:([pshdb].[dbo].[sql_t1].[idx01_sql_t1] AS [t1])... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1]),... SQL(3) varchar(10) = 'cust1' from sql_t1 t1 with(index=idx01_sql_t1) where id --Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) --Index Seek(OBJECT:([pshdb].[dbo].[sql_t1].[idx01_sql_t1] AS [t1]),... --RID Lookup(OBJECT:([pshdb].[dbo].[sql_t1] AS [t1]),... 힌트사용시주의할점 잘못된인덱스명사용에의한구문오류운영환경에서 SQL 의성능문제를해결하기위해서인덱스힌트를적용해야하는경우가있겠지만, SQL Server 에서일반적으로 Optimizer 가효율적인실행계획을수립하기때문에, 인덱스힌트를남발해서는안된다. 왜냐하면, 인덱스힌트구문안에기술된인덱스가삭제되면해당 SQL 은구문오류가발생하여수행되지않기때문이다. 이러한 SQL 이중요한업무처리를수행하는 Application 내에존재한다면성능문제외로심각한문제를일으킬수있으므로특히주의해야한다. 그리고, SI 프로젝트시 ( 개발환경 ) SQL 에인덱스힌트를적용할때는 DBA 와협의하에운영환경의 Naminig Rule 을반드시지켜야한다. 그렇지않으면개발시인덱스명과운영환경의인덱스명이달라문제를일으킬수있기때문이다. Part 3 SQL Server 505

11 Hint를적용한 SQL이자주변경되는경우 SQL 이자주변경되는것은업무가자주변경된다는것이다. 이런 SQL 은힌트를적용하는것이부적절하다. 특히, SQL 이변경된후조인순서, 조인방법, 데이터액세스를다시점검하고힌트를재조정해야하는경우라면적용된힌트에의해성능문제가발생할소지가높으므로힌트적용외로 SQL 의성능문제를개선할수있는지확인해야한다. Dynamic SQL에적용된 Global Hint 여기서 Dynamic SQL 은조회조건에따라 SQL 이변경되는 SQL 을의미한다. 이런 Dynamic SQL 은하나의 SQL 로보이지만, 하나의 SQL 이아니다. 하나의 SQL 은하나의실행계획만가지지만, 이런 Dynamic SQL 은조회조건에따라각기다른실행계획으로수립되어수행되어야효율적인수행이될수있기때문에하나의 SQL 이아니고, 각조회조건에따라다른 SQL 이된다. 그런데이런 Dynamic SQL 의모든조회조건에일괄적용되도록힌트를부여하면, 특정조회조건에만성능이효율적일뿐다른조회조건에는도리어성능을악화시키는원인이될수있다. 그러므로이런경우에는힌트를조회조건에맞게적용될수있도록힌트부여도 Dynamic 하게적용해야한다. 이제까지 SQL Server 에서 SQL 튜닝시사용되는힌트와그사용방법에대해서알아보았다. SQL Server 의기본적인가이드는 SQL 에대해 Optimizer 가효율적인실행계획을수립하므로, 웬만하면힌트를적용하지말라는것이다. 필자가생각하기에도그말이맞다. 그런데, 특정 Application 의 SQL 성능문제로인해운영중인 DB 의성능이위급한상황에놓이는경우, Optimizer 만믿고기다릴수는없다. 그럴경우불가피하게힌트를적용해야할경우가있다. 그러므로앞에서알아본힌트구문에대한이해와향후활용할수있도록여러테스트를수행하여그사용방법을익혀두길바란다 기술백서 White Paper

WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역

WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역시쉽게해결할수있다. 이번화이트페이퍼에서는 Window Function 중순위 RANK, ROW_NUMBER,