[mssql] 실행계획

[mssql] 실행계획 

조회수	4193
제목 : 실행계획을 보는 순서(5)
이번에는 실행계획을 보는 순서에 대해 적어보고자 합니다. 제 글이 어떤 분들에게 어떤 도움을 줄지는 저도 모르겠습니다. 하지만, 적어 봅니다. 누군가에게는 필요할꺼라 생각되어 지니까요. 이번에는 SQL을 실행하고, 실행계획을 분석해보는 과정정도라고 생각되어 집니다. 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이 실행되어 지구요. 이런 과정으로 진행이 되는 겁니다. 제 설명이 너무 말만 많다고 생각되어 지는군요. 어느정도 다듬어서 올린다면 더 멋지게 표현할 수 있지 않을까란 생각도 해보지만,,, 벌써 밤 12시 26분인지라.......낼 일하러 가는것도 걱정되고 해서 여기서 줄여야 할 거 같습니다. 어딘가에 정식 취직해서 출근하는 건 아니고, 예전에 일하던 곳에서 좀 도와달라고 해서 한 3주정도 일하러 갑니다. 오랫만에 예전에 일하던 분들과 일한다 생각하니 즐겁네요.^^ 물론, 그 후에 또 취업을 걱정해야 하지만 말이죠.^^