'연구개발/실행계획' 카테고리의 글 목록

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

RID Lookup은 물리연산자로써 제공된 RID(행 식별자)를 사용하여 힙을 조회하는 책갈피 조회이다.

Bookmark Lookup 연산자에서 설명하였듯이 SQL Server 2005 이후 버전에서 Bookmark Lookup 연산자의 기능을 대신한다.

SQL Server 2005 SP2 이후에서는 Key Lookup 연산자도 동일한 기능을 제공한다.

RID Lookup은 항상 NESTED LOOP JOIN과 함께 사용된다.

그래프 실행 계획 아이콘

RID Lookup 연산자의 예

아래의 예제는 [dbo].[DatabaseLog] 테이블에서 DatabaseLogID = 1인 데이터를 조회하고 있다.

SELECT *
FROM [dbo].[DatabaseLog]
WHERE DatabaseLogID = 1

[dbo].[DatabaseLog] 테이블의 [DatabaseLogID] 컬럼에는 넌클러스터드 인덱스(PK_DatabaseLog_DatabaseLogID)가 작성되어 있다.

작성된 넌클러스터드 인덱스에서 DatabaseLogID = 1인 행의 RID값을 NESTED LOOP JOIN([Bmk1000]=[Bmk1000])으로 조인하여

조회하고 있다.

텍스트 실행 계획

SttText

SELECT * FROM [dbo].[DatabaseLog] WHERE [DatabaseLogID]=@1
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |--Index Seek(OBJECT:([AdventureWorks].[dbo].[DatabaseLog].[PK_DatabaseLog_DatabaseLogID])
        , SEEK:([AdventureWorks].[dbo].[DatabaseLog].[DatabaseLogID]=CONVERT_IMPLICIT(int,[@1],0))
          ORDERED FORWARD)
       |--RID Lookup(OBJECT:([AdventureWorks].[dbo].[DatabaseLog])
        , SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Filter 실행 계획 연산자(Filter Showplan Operator) (0)	2011.07.18
[MS SQL Server]Concatenation 실행 계획 연산자(Concatenation Showplan Operator) (0)	2011.07.18
[MS SQL Server]Compute Scalar 실행 계획 연산자(Compute Scalar Showplan Operator) (0)	2011.07.18
[MS SQL Server]Collapse 실행 계획 연산자 및 Split 실행 계획 연산자 (Collapse Showplan Operator & Split Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Update 실행 계획 연산자(Clustered Index Update Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Filter 연산자는 입력을 검색하고 Argument 열에 표시되는 필터 식(조건자)에 부합되는 행만 반환한다.

즉, 입력값을 검색해 가면서 입력 Argument 에 부합되는 행만 걸러내는 역할을 한다.

주로 Having절을 사용할 때 이 연산자를 확인할 수 있다.

그래프 실행 계획 아이콘

Filter 연산자의 예

아래의 예제는 [Person].[Address] 테이블에서 도시별 인구 수를 구하는 예이다.

단, 아래의 실행 계획에서도 확인할 수 있듯이 Filter 연산자는 도시별 인구 수가 1이상인 것만 걸러내고 있다.

SELECT [City],COUNT([City]) AS CityCount
FROM [Person].[Address]
GROUP BY [City]
HAVING COUNT([City]) > 1

텍스트 실행 계획

SttText

SELECT [City],COUNT([City]) AS CityCount FROM [Person].[Address] GROUP BY [City] HAVING COUNT([City]) > 1
|--Filter(WHERE:([Expr1003]>(1)))
       |--Compute Scalar(...
            |--Hash Match(Aggregate, HASH:(...
                 |--Index Scan(...]))

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]RID Lookup 실행 계획 연산자(RID Lookup Showplan Operator) (0)	2011.07.18
[MS SQL Server]Concatenation 실행 계획 연산자(Concatenation Showplan Operator) (0)	2011.07.18
[MS SQL Server]Compute Scalar 실행 계획 연산자(Compute Scalar Showplan Operator) (0)	2011.07.18
[MS SQL Server]Collapse 실행 계획 연산자 및 Split 실행 계획 연산자 (Collapse Showplan Operator & Split Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Update 실행 계획 연산자(Clustered Index Update Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Concatenation 연산자는 여러 개의 입력을 검색하고 검색된 각 행을 반환한다. Concatenation은 일반적으로 Transact-SQL UNION ALL 구조를 구현하는 데 사용된다.

그래프 실행 계획 아이콘

Concatenation 연산자의 예

이해를 돕기 위해 BOL의 UNION 연산자의 예제를 살펴보도록 하겠다.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

-- Here is the simple union.
USE AdventureWorks;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

텍스트 실행 계획

SttText

SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4)

UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name;
|--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1007] ASC))

|--Concatenation

|--Index Scan(OBJECT:([AdventureWorks].[Production].[ProductModel].[AK_ProductModel_Name]),

WHERE:([AdventureWorks].[Production].[ProductModel].[ProductModelID]<>(3)

AND [AdventureWorks].[Production].[ProductModel].[ProductModelID]<>(4)))

|--Table Scan(OBJECT:([AdventureWorks].[dbo].[Gloves]))

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]RID Lookup 실행 계획 연산자(RID Lookup Showplan Operator) (0)	2011.07.18
[MS SQL Server]Filter 실행 계획 연산자(Filter Showplan Operator) (0)	2011.07.18
[MS SQL Server]Compute Scalar 실행 계획 연산자(Compute Scalar Showplan Operator) (0)	2011.07.18
[MS SQL Server]Collapse 실행 계획 연산자 및 Split 실행 계획 연산자 (Collapse Showplan Operator & Split Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Update 실행 계획 연산자(Clustered Index Update Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Compute Scalar 연산자는 논리/물리 연산자로써, 식을 계산하여 계산된 스칼라 값을 만든다.

즉, 사칙연산이나 스칼라 함수 사용과 같이 식을 계산할 때 나타난다.

그래프 실행 계획 아이콘

Compute Scalar 연산자의 예

이해를 돕기 위해 dbo.ComputeScalarTest이름으로 테이블을 작성하였다.

CREATE TABLE dbo.ComputeScalarTest
( EmpID INT,
Salary MONEY,
Comm MONEY )
GO

INSERT INTO dbo.ComputeScalarTest VALUES (1, 2000, 0.33)
INSERT INTO dbo.ComputeScalarTest VALUES (2, 1000, 0.05)
INSERT INTO dbo.ComputeScalarTest VALUES (3, 1500, 0.01)
INSERT INTO dbo.ComputeScalarTest VALUES (4, 500, 0)
GO

이제 사원별로 계산된 커미션을 추가한 급여를 출력해 보도록 하겠다.

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

SELECT EmpID, Salary + (Salary * Comm) AS TotSalary
FROM dbo.ComputeScalarTest
GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행계획

SttText

SELECT UserID, Salary + (Salary * Comm) AS TotSalary FROM dbo.ComputeScalarTest
|--Compute Scalar(DEFINE:([Expr1004]=

[TestDB].[dbo].[ComputeScalarTest].[Salary]+

[TestDB].[dbo].[ComputeScalarTest].[Salary]*[TestDB].[dbo].[ComputeScalarTest].[Comm]))
|--Table Scan(OBJECT:([TestDB].[dbo].[ComputeScalarTest]))

그래픽 실행계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Filter 실행 계획 연산자(Filter Showplan Operator) (0)	2011.07.18
[MS SQL Server]Concatenation 실행 계획 연산자(Concatenation Showplan Operator) (0)	2011.07.18
[MS SQL Server]Collapse 실행 계획 연산자 및 Split 실행 계획 연산자 (Collapse Showplan Operator & Split Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Update 실행 계획 연산자(Clustered Index Update Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Seek 실행 계획 연산자(Clustered Index Seek Showplan Operator) (0)	2011.07.18

※실행 계획 연산자 전체 보기

Collapse 연산자는 논리/물리 연산자로써, 업데이트 처리를 최적화할 때 나타나는 연산자이다.

그리고 Split 실행 계획 연산자라는 것이 있는데 Collapse 실행 계획 연산자와 함께 업데이트 처리를 최적화하는데 상호작용한다.

Split 실행 계획 연산자는 논리/물리 연산자로써, 각 업데이트 작업을 삭제 및 삽입 작업으로 분할한다.

그래프 실행 계획 아이콘

Collapse 실행 계획 연산자 Split 실행 계획 연산자

Collapse 논리/물리 연산자에 대해 BOL의 내용을 인용하면 "쿼리 프로세서가 동일한 키 값을 삭제 및 삽입하는 인접 행을 발견하면 별개의 두 작업을 보다 효율적인 한 개의 업데이트 작업으로 교체한다" 라고 되어 있다.

역시 어렵다... ㅡ.ㅡ

언제나 느끼는 것이지만 BOL을 해석하는 것은 어렵다 ㅜ.ㅜ

이를 쉽게 설명하자면,

인용문에서 "쿼리 프로세서가 동일한 키 값을 삭제 및 삽입하는 인접 행을 발견하면" 이란 "두 개 이상의 인접한 인덱스 키 값을 업데이트 할 때..." 로
인용문에서 "별 개의 두 작업을..." 은 "업데이트할 열 들(즉 열 하나하나가 작업이다)" 로
인용문에서 "보다 효율적인 한 개의 업데이트 작업으로 교체한다" 란 "두 개 이상의 업데이트 열을 한 번의 업데이트 작업으로 바꾸어 처리한다" 라는

의미로 해석하면 이해가 빠르지 않을까 싶다.

즉, "두 개 이상의 인접한 인덱스 키 값을 업데이트할 때, 한 행 한 행 업데이트하는 것이 아니라, 쿼리 프로세스가 내부적으로 하나의 업데이트 프로세스로 처리한다" 라는 것이다.

이에 대해 좀 더 부연 설명하도록 하겠다. ^^;

행을 업데이트 할 때, MS SQL Server의 쿼리최적화기(Query Optimizer)는 타 RDMBS의 비용기반 쿼리최적화기(Cost Based Optimizer)와 마찬가지로 여러가지 업데이트 전략 중에서 가장 빠르게 업데이트 할 수 있는 방법을 스스로 판단하고 선택한다.

이 때 얼마나 많은 행을 업데이트하느냐? 어떤 방법으로 행에 엑세스 하느냐(Scan을 하느냐? Seek를 하느냐? 인덱스를 사용한다면 어떤 인덱스를 사용하느냐?)? 인덱스 키를 업데이트 하느냐? 등을 고려하여 전략을 수립한다.

특히 인덱스 키를 업데이트할 경우, 그 중에서 인접한 복수의 행을 업데이트할 경우, Collapse 연산자가 나타나게 되는데, Collapse 연산자는 업이 때 발생하는 행의 이동을 행 단위가 아닌 하나의 프로세스로 처리한다.

다시 말해, 1행과 2행을 업데이트 할 때

Update 1행...

Update 2행...

이 아닌,

Update 1,2행

이런 식으로 일괄(Grouping) 처리하는 것이다 (아래에 예를 통해서 설명하겠지만, Collapse의 연산자의 실행 계획의 Argument 열에서 GROUP BY가 나타나는 것을 유심히 관찰해 보기 바란다).

그리고 이 때, 업데이트할 행들은 Split 연산자에 의해 삽입과 삭제 두 과정으로 나뉘어져서 Collapse 연산자의 Argument로 공급된다.

1. 클러스터형 인덱스 키가 있는 열을 업데이트할 경우의 Collapse 연산자의 예

이해를 돕기 위해 아래와 같이 dbo.CollapseTest1이라는 테이블을 만들고 col1에 클러스터형 인덱스를 작성해 보도록 하겠다.

CREATE TABLE dbo.CollapseTest1
( col1 INT,
col2 VARCHAR(10),
CONSTRAINT CollapseTest1_PK
PRIMARY KEY CLUSTERED (col1) WITH FILLFACTOR = 100)
GO

INSERT INTO dbo.CollapseTest1 VALUES (1, 'A')
INSERT INTO dbo.CollapseTest1 VALUES (2, 'B')
INSERT INTO dbo.CollapseTest1 VALUES (3, 'C')
INSERT INTO dbo.CollapseTest1 VALUES (4, 'D')

GO

SELECT * FROM dbo.CollapseTest1

col1        col2
----------- ----------
1           A
2           B
3           C
4           D

이제 클러스터형 인덱스가 있는 col1에 대해 업데이트를 하고 실행 계획을 살펴보도록 하자.

단, col1은 한 행이 아닌 인접해 있는 복수 개의 행을 업데이트해야 Collapse 연산자가 나타난다.

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

UPDATE dbo.CollapseTest1

SET col1 = col1 + 10

WHERE col1 BETWEEN 1 and 3

GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행 계획

SttText

UPDATE dbo.CollapseTest1 SET col1 = col1 + 10 WHERE col1 BETWEEN 1 and 3
|--Clustered Index Update(OBJECT:([TestDB].[dbo].[CollapseTest1].[CollapseTest1_PK])

, SET:([TestDB].[dbo].[CollapseTest1].[col1] = RaiseIfNull([TestDB].[dbo].[CollapseTest1].[col1])

                          ,[TestDB].[dbo].[CollapseTest1].[col2] = [TestDB].[dbo].[CollapseTest1].[col2]))
       |--Collapse(GROUP BY:([TestDB].[dbo].[CollapseTest1].[col1]))
            |--Sort(ORDER BY:([TestDB].[dbo].[CollapseTest1].[col1] ASC, [Act1015] ASC))
                 |--Split
                      |--Compute Scalar(DEFINE:([Expr1003]=[TestDB].[dbo].[CollapseTest1].[col1]+(10)))
                           |--Top(ROWCOUNT est 0)
                                |--Clustered Index Seek(OBJECT:([TestDB].[dbo].[CollapseTest1].[CollapseTest1_PK])

, SEEK:([TestDB].[dbo].[CollapseTest1].[col1] >= (1)

AND [TestDB].[dbo].[CollapseTest1].[col1] <= (3)) ORDERED FORWARD)

그래픽 실행 계획

실행계획의 설명

Clustered Index Seek : 업데이트할 행을 CollapseTest1_PK 인덱스를 검색한다. 이 때 SEEK 조건자는 1 <= col1 <= 3 이다.
Compute Scalar : 업데이트할 데이터 값을 계산한다. 업데이트할 값은 col1 + 10 이다.
Split : 업데이트 할 데이터를 각각 삭제와 삽입의 작업으로 분할한다. 즉, 11, 12, 13, 14는 새로운 삽입 작업이고, 1,2,3,4는 삭제 작업이다.
Sort : Split에 의해 분리된 작업을 작업이 편하게 정렬한다.
Collapse : 정렬된 Argument 열 목록을 그룹핑하여 일괄처리 작업을 만든다.
Clustered Index Update : 마지막으로 해당 열을 업데이트한다.

그렇다면, 넌클러스터형 인덱스 키가 있는 열을 업데이트할 경우, 어떤 실행계획이 나타날까?

한 번 테스트해 보길 바란다.

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Concatenation 실행 계획 연산자(Concatenation Showplan Operator) (0)	2011.07.18
[MS SQL Server]Compute Scalar 실행 계획 연산자(Compute Scalar Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Update 실행 계획 연산자(Clustered Index Update Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Seek 실행 계획 연산자(Clustered Index Seek Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Scan 실행 계획 연산자(Clustered Index Scan Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Clustered Index Update 연산자는 논리/물리 연산자로써, Argument 열에 지정한 행만 클러스터형 인덱스에서 업데이트 한다.

WHERE:() 조건자가 있는 경우에는 조건자에 부합되는 행만 업데이트되고, SET:() 조건자가 있는 경우에는 업데이트된 각 열이 이 값으로 설정된다.

그래프 실행 계획 아이콘

Clustered Index Update 연산자의 예

이해를 돕기 위해 dbo.ClusteredIndexUpdateTest 이름으로 테이블을 작성하고 col1에 Clustered Index를 작성하겠다.

CREATE TABLE dbo.ClusteredIndexUpdateTest
( col1 INT Identity(1, 1),
col2 VARCHAR(10),
CONSTRAINT ClusteredIndexUpdateTest_PK
PRIMARY KEY CLUSTERED (col1) WITH FILLFACTOR = 100)
GO

Declare @i int

SET @i = 1

WHILE @i <= 200

BEGIN

INSERT INTO dbo.ClusteredIndexUpdateTest(col2) values ('Data');

SET @i = @i + 1

END

GO

이제 Clustered Index를 이용하여 검색해 보겠다.

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

UPDATE dbo.ClusteredIndexUpdateTest

SET col2 = 'NEW Data'

WHERE col1 = 10
GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행 계획

SttText

UPDATE dbo.ClusteredIndexUpdateTest SET col2 = 'NEW Data' WHERE col1 = 10
|--Clustered Index Update(OBJECT:([TestDB].[dbo].[ClusteredIndexUpdateTest].[ClusteredIndexUpdateTest_PK])

, SET:([TestDB].[dbo].[ClusteredIndexUpdateTest].[col2] = [Expr1003])

, DEFINE:([Expr1003]=CONVERT_IMPLICIT(varchar(10),[@1],0))

, WHERE:([TestDB].[dbo].[ClusteredIndexUpdateTest].[col1]=CONVERT_IMPLICIT(int,[@2],0)))

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Compute Scalar 실행 계획 연산자(Compute Scalar Showplan Operator) (0)	2011.07.18
[MS SQL Server]Collapse 실행 계획 연산자 및 Split 실행 계획 연산자 (Collapse Showplan Operator & Split Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Seek 실행 계획 연산자(Clustered Index Seek Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Scan 실행 계획 연산자(Clustered Index Scan Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Insert 실행 계획 연산자(Clustered Index Insert Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Clustered Index Seek 연산자는 논리/물리 연산자로써, Argument 열에 공급 받은 SEEK:() 조건자에 부합되는 행만 클러스터형 인덱스에서 검색한다.

Argument 열에 ORDERED 절이 있으면 쿼리 프로세서는 클러스터형 인덱스가 정렬한 순서대로 행을 반환하며, ORDERED 절이 없으면 저장소 엔진은 출력을 정렬하지 않고 최적의 방법으로 인덱스를 검색한다.

키워드 LOOKUP을 사용하면 책갈피 조회가 수행된다. SQL Server 2005 SP2 부터는 Key Lookup 연산자가 책갈피 조회 기능을 제공한다.

그래프 실행 계획 아이콘

Clustered Index Seek 연산자의 예

이해를 돕기 위해 dbo.ClusteredIndexSeekTest 이름으로 테이블을 작성하고 col1에 Clustered Index를 작성하겠다.

CREATE TABLE dbo.ClusteredIndexSeekTest
( col1 INT Identity(1, 1),
col2 VARCHAR(10),
CONSTRAINT ClusteredIndexSeekTest_PK
PRIMARY KEY CLUSTERED (col1) WITH FILLFACTOR = 100)
GO

Declare @i int

SET @i = 1

WHILE @i <= 200

BEGIN

INSERT INTO dbo.ClusteredIndexSeekTest(col2) values ('Data');

SET @i = @i + 1

END

GO

이제 Clustered Index를 이용하여 검색해 보겠다.

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

SELECT *

FROM dbo.ClusteredIndexSeekTest

WHERE col1 = 10
GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행 계획

SttText

SELECT * FROM dbo.ClusteredIndexSeekTest WHERE col1 = 10
|--Clustered Index Seek(OBJECT:([TestDB].[dbo].[ClusteredIndexSeekTest].[ClusteredIndexSeekTest_PK])

, SEEK:([TestDB].[dbo].[ClusteredIndexSeekTest].[col1]=CONVERT_IMPLICIT(int,[@1],0))

ORDERED FORWARD)

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Collapse 실행 계획 연산자 및 Split 실행 계획 연산자 (Collapse Showplan Operator & Split Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Update 실행 계획 연산자(Clustered Index Update Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Scan 실행 계획 연산자(Clustered Index Scan Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Insert 실행 계획 연산자(Clustered Index Insert Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Delete 실행 계획 연산자(Clustered Index Delete Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Clustered Index Scan 연산자는 물리 연산자로써, 쿼리 실행 계획의 Argument 열에 지정된 클러스터형 인덱스를 검색한다.

선택 사항인 WHERE:() 조건자가 있는 경우에는 조건자에 부합되는 행만 반환된다.

Argument 열에 ORDERED 절이 있으면 쿼리 프로세서가 행의 출력이 클러스터형 인덱스에 의해 정렬되는 순서로 반환한다. ORDERED 절이 없으면 저장소 엔진은 출력을 정렬하지 않고 최적의 방법으로 인덱스를 검색합니다.

Clustered Index Scan 은 Table Scan 과 같다고 보면 된다. 왜냐하면, Clustered Index의 리프 레벨 페이지에는 실제 데이터가 저장되어 있기 때문이다. 우리가 흔히 생각하는 인덱스를 이용한 검색의 실행 계획 연산자는 Index Seek이다.

그리고, 당연히 해당 테이블에 클러스터형 인덱스가 정의되어 있을 경우에만 유효하다.

그래프 실행 계획 아이콘

Clustered Index Scan 연산자의 예

이해를 돕기 위해 dbo.ClusteredIndexScanTest 이름으로 테이블을 작성하고 col1에 Clustered Index를 작성하겠다.

CREATE TABLE dbo.ClusteredIndexScanTest
( col1 INT Identity(1, 1),
col2 VARCHAR(10),
CONSTRAINT ClusteredIndexScanTest_PK
PRIMARY KEY CLUSTERED (col1) WITH FILLFACTOR = 100)
GO

Declare @i int

SET @i = 1

WHILE @i <= 200

BEGIN

INSERT INTO dbo.ClusteredIndexScanTest(col2) values ('Data');

SET @i = @i + 1

END

GO

이제 Clustered Index를 이용하여 검색해 보겠다.

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

SELECT *

FROM dbo.ClusteredIndexScanTest
GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행 계획

SttText

SELECT * FROM dbo.ClusteredIndexScanTest

|--Clustered Index Scan(OBJECT:([TestDB].[dbo].[ClusteredIndexScanTest].[ClusteredIndexScanTest_PK]))

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Clustered Index Update 실행 계획 연산자(Clustered Index Update Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Seek 실행 계획 연산자(Clustered Index Seek Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Insert 실행 계획 연산자(Clustered Index Insert Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Delete 실행 계획 연산자(Clustered Index Delete Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bookmark Lookup 실행 계획 연산자(Bookmark Lookup Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Clustered Index Insert 연산자는 물리 연산자로써, 입력 행을 Argument 열에 지정된 클러스터형 인덱스에 삽입한다.

당연히 해당 테이블에 클러스터형 인덱스가 정의되어 있을 경우에만 유효하다.

그래프 실행 계획 아이콘

Clustered Index Insert 연산자의 예

이해를 돕기 위해 dbo.ClusteredIndexInsertTest 이름으로 테이블을 작성하고 col1에 Clustered Index를 작성하겠다.

CREATE TABLE dbo.ClusteredIndexInsertTest
( col1 TINYINT,
col2 VARCHAR(1),
CONSTRAINT ClusteredIndexInsertTest_PK
PRIMARY KEY CLUSTERED (col1) WITH FILLFACTOR = 100)
GO

이제 한 행을 삽입해 보겠다.

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

INSERT INTO dbo.ClusteredIndexInsertTest(col1, col2) VALUES (1, 'A');
GO

SET SHOWPLAN_ALL OFF
GO

SELECT * FROM dbo.ClusteredIndexInsertTest
GO

텍스트 실행 계획

SttText

INSERT INTO dbo.ClusteredIndexInsertTest(col1, col2) VALUES (1, 'A');

|--Clustered Index Insert(OBJECT:([TestDB].[dbo].[ClusteredIndexInsertTest].[ClusteredIndexInsertTest_PK])

, SET:([TestDB].[dbo].[ClusteredIndexInsertTest].[col1] = RaiseIfNull([Expr1003])

,[TestDB].[dbo].[ClusteredIndexInsertTest].[col2] = [Expr1004])

, DEFINE:([Expr1003]=CONVERT_IMPLICIT(tinyint,[@1],0)

, [Expr1004]=CONVERT_IMPLICIT(varchar(1),[@2],0)))

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Clustered Index Seek 실행 계획 연산자(Clustered Index Seek Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Scan 실행 계획 연산자(Clustered Index Scan Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Delete 실행 계획 연산자(Clustered Index Delete Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bookmark Lookup 실행 계획 연산자(Bookmark Lookup Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bitmap 실행 계획 연산자(Bitmap Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Clustered Index Delete 연산자는 물리 연산자로써, 쿼리 실행 계획의 Argument 열에 지정된 클러스터형 인덱스에서 행을 삭제한다.

당연히 해당 테이블에 클러스터형 인덱스가 정의되어 있을 경우에만 유효하다.

클러스터형 인덱스의 최하위 리프 레벨에는 실제 데이터가 저장되어 있으므로 클러스터형 인덱스에서 WHERE 조건에 부합되는 행을 제거하는 것이다.

그래프 실행 계획 아이콘

Clustered Index Delete 연산자의 예

이해를 돕기 위해 dbo.ClusteredIndexDeleteTest 이름으로 테이블을 작성하고 col1에 Clustered Index를 작성하겠다.

CREATE TABLE dbo.ClusteredIndexDeleteTest
( col1 TINYINT IDENTITY(1,1),
col2 VARCHAR(1),
CONSTRAINT ClusteredIndexDeleteTest_PK
PRIMARY KEY CLUSTERED (col1) WITH FILLFACTOR = 100)
GO

INSERT INTO dbo.ClusteredIndexDeleteTest (col2) VALUES ('A');
INSERT INTO dbo.ClusteredIndexDeleteTest (col2) VALUES ('B');
INSERT INTO dbo.ClusteredIndexDeleteTest (col2) VALUES ('C');
INSERT INTO dbo.ClusteredIndexDeleteTest (col2) VALUES ('D');
GO

SELECT * FROM dbo.ClusteredIndexDeleteTest
GO

Result>>

col1 col2
---- ----
1    A
2    B
3    C
4    D

이제 col1 _ 1인 데이터를 삭제해 보겠다.

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

DELETE FROM dbo.ClusteredIndexDeleteTest
WHERE col1 = 1
GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행 계획

SttText

DELETE FROM dbo.ClusteredIndexDeleteTest WHERE col1 = 1

|--Clustered Index Delete (OBJECT:([AdventureWorks].[dbo].[ClusteredIndexDeleteTest]....,
WHERE:([AdventureWorks].[dbo].[ClusteredIndexDeleteTest].[col1]=[@1]))

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Clustered Index Scan 실행 계획 연산자(Clustered Index Scan Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Insert 실행 계획 연산자(Clustered Index Insert Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bookmark Lookup 실행 계획 연산자(Bookmark Lookup Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bitmap 실행 계획 연산자(Bitmap Showplan Operator) (0)	2011.07.18
[MS SQL Server]Assert 실행계획 연산자(Assert Showplan Operator) (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Bookmark Lookup 연산자는 책갈피(행 ID 또는 클러스터링 키)를 사용하여 테이블이나 클러스터형 인덱스에서 해당 행을 조회한다. Argument 열에는 테이블이나 클러스터형 인덱스에서 행을 조회할 때 사용하는 책갈피 레이블이 포함된다. Argument 열에는 행을 조회하는 테이블 또는 클러스터형 인덱스의 이름도 포함된다. WITH PREFETCH 절이 Argument 열에 나타나는 경우에 쿼리 프로세서에서는 테이블 또는 클러스터형 인덱스에서 책갈피를 조회할 때 비동기 사전 인출(미리 읽기)을 사용하는 것을 최적의 방법으로 결정하게 된다.

SQL Server 2005 이후 버전에서는 Bookmark Lookup이 사용되지 않는다. 대신 Clustered Index Seek 및 RID Lookup이 책갈피 조회 기능을 제공하며, SQL Server 2005 서비스 팩 2 이후에서는 Key Lookup 연산자도 이 기능을 제공한다.

그래프 실행 계획 아이콘

[주인장의 말.말.말]

위의 내용은 BOL을 인용한 것으로 처음 접하는 사람에게는 이해하기 힘들 수 있을 것이다. 블로그 주인이 Bookmark Lookup을 부연 설명하자면, 내가 Select하고자 하는 컬럼이 인덱스에 없을 경우 넌클러스터드 인덱스의 키 값을 가지고 힙 페이지 (혹은 클러스터형 인덱스가 있는 테이블일 경우는 클러스터형 인덱스를) 뒤지는 것을 의미한다. 따라서 힙이나 클러스터형 인덱스를 찾아가는 과정의 추가적인 Page I/O가 발생하게 된다.

이는 마치 Oracle에서 인덱스 영역에 저장되어 있는 rowid를 가지고 데이터 블록을 엑세스하는 과정과 같다.

하지만 내가 Select하고자 하는 컬럼이 모두 인덱스에 있을 때, 즉 인덱스가 모두 커버할 수 있을 때 - 이를 유식한 말로 Covered Index라고 한다. - 는 Bookmark Lookup이 발생하지 않게 될 것이다. 이는 아래의 예에서 확인해 보도록 하자.

BookMark Lookup 연산자의 예 (이 테스트는 SQL Server 2000에서 이루어졌다)

먼저, 앞서 소개한 dbo.idxinfo_v 뷰를 조회하여 authors 테이블의 인덱스를 먼저 확인해 본다.

SELECT Owner, TableName, IndexName, ClusterType, UniqueType, IndexType, AllColName
FROM dbo.idxinfo_v
WHERE TableName = 'authors'
GO

조회 결과

Owner	TableName	IndexName	ClusterType	UniqueType	IndexType	AllColName
dbo	authors	UPKCL_auidind	CLUSTERED	PRIMARY KEY	CONSTRAINT	au_id
dbo	authors	aunmind	NONCLUSTERED	INDEX		au_lname, au_fname

이제 아래의 SQL을 실행해 보자. 실행 계획에서 Bookmark Lookup을 확인 할 수 있을 것이다.

USE pubs
GO

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

SELECT *
FROM authors AS a
WHERE au_lname LIKE 'St%'
ORDER BY a.au_lname DESC
GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행 계획

SttText

SELECT * FROM authors AS a WHERE au_lname LIKE 'St%' ORDER BY a.au_lname DESC

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[authors] AS [a]))

|--Index Seek(OBJECT:([pubs].[dbo].[authors].[aunmind] AS [a]),

SEEK:([a].[au_lname] >= 'Ss' AND [a].[au_lname] < 'Su'),

WHERE:(like([a].[au_lname], 'St%', NULL)) ORDERED BACKWARD)

그래픽 실행 계획

다음의 앞에서 언급한 Covered Index의 예이다.

위 SQL에서 달라진 것은 Select한 컬럼이 모두 인덱스에 있다는 점 뿐이다.

USE pubs
GO

SET NOCOUNT ON
GO

SET SHOWPLAN_ALL ON
GO

SELECT a.au_lname, a.au_lname
FROM authors AS a
WHERE au_lname LIKE 'St%'
ORDER BY a.au_lname DESC
GO

SET SHOWPLAN_ALL OFF
GO

텍스트 실행 계획

SttText

SELECT a.au_lname, a.au_lname FROM authors AS a WHERE au_lname LIKE 'St%' ORDER BY a.au_lname DESC

|--Index Seek(OBJECT:([pubs].[dbo].[authors].[aunmind] AS [a]),

SEEK:([a].[au_lname] >= 'Ss' AND [a].[au_lname] < 'Su'),

WHERE:(like([a].[au_lname], 'St%', NULL)) ORDERED BACKWARD)

그래픽 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Clustered Index Insert 실행 계획 연산자(Clustered Index Insert Showplan Operator) (0)	2011.07.18
[MS SQL Server]Clustered Index Delete 실행 계획 연산자(Clustered Index Delete Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bitmap 실행 계획 연산자(Bitmap Showplan Operator) (0)	2011.07.18
[MS SQL Server]Assert 실행계획 연산자(Assert Showplan Operator) (0)	2011.07.18
[MS SQL Server]실행 계획 연산자 리스트 (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행 계획 연산자 전체 보기

Bitmap 연산자는 병렬 쿼리 계획 - SQL Server 2005의 BOL에서는 ‘병렬 쿼리 계획에서만’ 이라고 언급되어 있다 - 에서 비트맵 필터링을 구현할 때 사용되는 물리 연산자이다.

비트맵 필터링은 Parallelism 연산자와 같은 다른 연산자로 행을 전달하기 전에, 조인 레코드를 생성할 수 없는 키 값을 가진 행을 제거하여 쿼리의 실행 속도를 높인다.

비트맵 필터는 연산자 트리의 한 부분에 있는 테이블의 값 집합에 대한 압축된 표현을 사용하여 트리의 다른 부분에 있는 다른 테이블에서 행을 필터링한다. 쿼리 초기 단계에서 필요 없는 행을 제거하면 이후 연산자에서 작업할 행 수가 더 적어지고 쿼리의 전체적인 성능이 향상된다.

비트맵이 유용할 만큼 충분히 선택 가능성이 높아지는 시점은 언제인지, 필터를 적용할 연산자는 무엇으로 사용할 지는 옵티마이저가 판단한다.

자세한 내용은 Microsoft Technet 의 비트맵 필터링을 통한 데이터 웨어하우스 쿼리 성능 최적화 을 참조하길 바란다.

그래프 실행 계획 아이콘

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Clustered Index Delete 실행 계획 연산자(Clustered Index Delete Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bookmark Lookup 실행 계획 연산자(Bookmark Lookup Showplan Operator) (0)	2011.07.18
[MS SQL Server]Assert 실행계획 연산자(Assert Showplan Operator) (0)	2011.07.18
[MS SQL Server]실행 계획 연산자 리스트 (0)	2011.07.18
16. 저장 프로시저에서의 실행 계획 (0)	2011.07.18

본 포스트는 Microsoft TechNet의 [논리 및 물리 연산자 참조]내용을 참고하여 정리한 내용이다.

※실행계획 연산자 전체 보기

Assert 연산자는 제약 조건 확인, 참조 무결성 확인 또는 스칼라 하위 쿼리(Scalar Subquery)에서 한 개의 행만 반환하는지 등 상태 검증을 이행할 때 나타나는 물리 연산자이다.

Assert 연산자는 각 입력 행에 대해 실행 계획의 Argument 열의 식을 계산하게 되는데, 이 식의 결과 값이 NULL이면, 그 행은 Assert 연산자를 통과해서 쿼리 실행을 계속하게 된다. 이 식이 NULL이 아니면 해당 오류가 발생한다.

그래프 실행계획 아이콘

예제

1. CHECK 제약 조건의 유효성 검사

다음 예에서는 HumanResources.Employee 테이블에서 지정한 직원의 ID에 대해 Gender 열의 값을 갱신한다. 이 테이블에는 열에 'F' 및 'M' 값만 허용하는 CHECK 제약 조건(CK_Employee_Gender)이 있다. 쿼리 실행 계획에서는 쿼리 최적화 프로그램이 Assert 연산자를 사용하여 CHECK 제약 조건에 대해 UPDATE 문에서 지정한 값의 유효성을 검사하고, 제약 조건의 조건이 충족되지 않을 때 오류를 발생시킴을 보여준다.

USE AdventureWorks
GO
SET NOCOUNT ON
GO
SET SHOWPLAN_ALL ON
GO
UPDATE HumanResources.Employee
SET Gender = 'X'
WHERE EmployeeID = 1
GO
SET SHOWPLAN_ALL OFF
GO

Assert 연산자의 실행 계획

StmtText
------------------------------------------------------------------------------------------------------------------------------
UPDATE HumanResources.Employee
SET Gender = 'X' WHERE EmployeeID = 1;
|--Assert(WHERE:(CASE WHEN upper([AdventureWorks].[HumanResources].[Employee].[Gender])<>N'F'
                         AND upper([AdventureWorks].[HumanResources].[Employee].[Gender])<>N'M' THEN (0)
                        ELSE NULL END))
       |--Clustered Index Update(OBJECT:([AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID]),
           SET:([AdventureWorks].[HumanResources].[Employee].[Gender] = RaiseIfNull([Expr1003])),
           DEFINE:([Expr1003]=CONVERT_IMPLICIT(nchar(1),[@1],0)), W

2. FOREIGN KEY 제약 조건의 유효성 검사

다음 예에서는 Person.Contact 테이블에서 행을 삭제한다. 이 테이블의 ContactID 열에 대해서 외래 키 참조하는 6개의 테이블이 있는데, 쿼리 실행 계획에서는 쿼리 최적화 프로그램이 Assert 연산자를 사용하여 이러한 각 제약 조건에 대해 DELETE 문의 유효성을 검사함을 보여준다.

USE AdventureWorks
GO
SET NOCOUNT ON
GO
SET SHOWPLAN_ALL ON
GO
DELETE Person.Contact WHERE ContactID = 1209
GO
SET SHOWPLAN_ALL OFF
GO

Assert 연산자의 실행 계획

StmtText...
----------------------------------------------------------------------------------------------------...
DELETE Person.Contact WHERE ContactID = 1209;...
|--Sequence...
       |--Table Spool...
       |    |--Assert(WHERE:(CASE WHEN NOT [Expr1030] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1031]...
       |         |--Nested Loops(Left Semi Join, OUTER REFERENCES:([AdventureWorks].[Person].[Contac...
       |              |--Nested Loops(Left Semi Join, OUTER REFERENCES:([AdventureWorks].[Person].[C...
       |              |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([AdventureWorks].[Perso...
       |              |    |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([AdventureWorks].[...
       |              |    |    |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([AdventureWor...
       |              |    |    |    |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Adventu...
       |              |    |    |    |    |    |--Clustered Index Delete(OBJECT:([AdventureWorks].[P...
       |              |    |    |    |    |    |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sal...
   …

※예제에서 확인할 수 있듯이, 제약 조건을 사용하게 되면 Assert 연산자의 추가 비용이 발생한다. 특히 FOREIGN KEY 제약 조건을 설정했을 경우 위의 두 번째 예처럼 참조 무결성을 체크하기 위해 Join 연산을 수행하게 된다.

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Bookmark Lookup 실행 계획 연산자(Bookmark Lookup Showplan Operator) (0)	2011.07.18
[MS SQL Server]Bitmap 실행 계획 연산자(Bitmap Showplan Operator) (0)	2011.07.18
[MS SQL Server]실행 계획 연산자 리스트 (0)	2011.07.18
16. 저장 프로시저에서의 실행 계획 (0)	2011.07.18
15. 텍스트 및 XML 실행 계획의 기초 - XML 실행 계획 (0)	2011.07.18

http://blog.naver.com/wing3a07/100060679248

MS SQL Server의 실행 계획 연산자 리스트이다.

BOL은 Microsoft TechNet으로 하이퍼링크되어 있으며 주인장의 포스트는 점차적으로 채워갈 예정이다.

1. 논리 및 물리 연산자 Logical and Physical Operators

연산자	그래픽 실행계획 아이콘	참고 사이트
Arithmetic Expression		주인장의 포스트 BOL Ver 2005: SQL Server 2005에서 사용되지 않음 BOL Ver 2008: SQL Server 2008에서 사용되지 않음
Assert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Bitmap		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Bookmark Lookup		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Clustered Index Delete		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Clustered Index Insert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Clustered Index Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Clustered Index Seek		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Clustered Index Update		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Collapse		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Compute Scalar		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Concatenation		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Constant Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Delete		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Deleted Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Eager Spool		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Filter		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Hash Match		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Hash Match Root		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Hash Match Team		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Insert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Inserted Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Iterator Catchall		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Lazy Spool		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Log Row Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Merge Interval		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Merge Join		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Nested Loops		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Nonclustered Index Delete		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Nonclustered Index Insert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Nonclustered Index Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Nonclustered Index Seek		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Nonclustered Index Spool		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Nonclustered Index Update		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Online Index Insert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Parameter Table Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Remote Delete		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Remote Insert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Remote Query		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Remote Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Remote Update		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
RID Lookup		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Row Count Spool		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Segment		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Sequence		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
SequenceProject		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Sort		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Split		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Spool		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Stream Aggreate		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Switch		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Table Delete		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Table Insert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Table Scan		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Table Spool		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Table Update		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Table-valued Function		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Top		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
UDX		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Update		주인장의 포스트 BOL Ver 2005 BOL Ver 2008

2. 커서 논리 물리 실행 계획 연산자 Cursor Logical and Physical Showplan Operators

연산자	그래픽 실행계획 아이콘	참고 사이트
Cursor Catchall		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Dyanmic		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Fetch Query		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Keyset		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Population Query		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Refresh Query		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Snapshot		주인장의 포스트 BOL Ver 2005 BOL Ver 2008

3. 병렬 실행 계획 연산자 Parallelism Showplan Operator

연산자	그래픽 실행계획 아이콘	참고 사이트
Distribute Streams		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Gather Streams		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Repartition Streams		주인장의 포스트 BOL Ver 2005 BOL Ver 2008

4. 언어요소 Language element

연산자	그래픽 실행계획 아이콘	참고 사이트
Assign		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Convert		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Declare		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
If		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Intrinsic		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Language Element Cathchall		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
Result		주인장의 포스트 BOL Ver 2005 BOL Ver 2008
While		주인장의 포스트 BOL Ver 2005 BOL Ver 2008

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Bitmap 실행 계획 연산자(Bitmap Showplan Operator) (0)	2011.07.18
[MS SQL Server]Assert 실행계획 연산자(Assert Showplan Operator) (0)	2011.07.18
16. 저장 프로시저에서의 실행 계획 (0)	2011.07.18
15. 텍스트 및 XML 실행 계획의 기초 - XML 실행 계획 (0)	2011.07.18
14. 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획 (0)	2011.07.18

저장 프로시저에서의 실행 계획

저장 프로시저에서의 실행 계획은 지금까지 살펴본 예문에서의 실행 계획과 별반 다를 바 없다.

이해를 돕기 위해 AdventureWorks 데이터베이스에 아래와 같이 저장 프로시저를 작성해 보자.
CREATE PROCEDURE [Sales].[spTaxRateByState]
    @CountryRegionCode NVARCHAR(3)
AS
    SET NOCOUNT ON ;
 
    SELECT [st].[SalesTaxRateID],
           [st].[Name],
           [st].[TaxRate],
           [st].[TaxType],
           [sp].[Name] AS StateName
      FROM [Sales].[SalesTaxRate] st
      JOIN [Person].[StateProvince] sp
        ON [st].[StateProvinceID] = [sp].[StateProvinceID]
     WHERE [sp].[CountryRegionCode] = @CountryRegionCode
     ORDER BY [StateName]
GO
그리고 아래와 같이 저장 프로시저를 실행 해보자.
EXEC [Sales].[spTaxRateByState] @CountryRegionCode = 'US'
실질 실행 계획의 결과는 아마 다음과 같이 보여질 것이다.

[그림 1] 저장 프로시저 [Sales].[spTaxRateByState] 를 실행하였을 때의 실질 실행 계획

먼저, @CountryRegionCode 파라미터에 바인드된 값(‘US’)을 토대로 Person.StateProvince 테이블을 Clustered Index Scan 연산을 한다. 그리고 이 연산을 통해 얻어진 결과는 Sort 연산자에 의해 정렬된다. 정렬된 데이터는 Sales.SalesTaxRate 테이블에서 Index Seek 연산을 통해 얻어진 결과와 Nested Loop Join을 한다.

다음으로 Key Lookup 연산을 수행한다. 이 연산자는 Sales.SalesTaxRate 테이블에 대해 AK_CountryRegion_Name 인덱스를 Index Seek 연산한 결과인 키 리스트를 공급 받아, 공급 받은 키 값을 토대로 Clustered Index 상에 데이터를 검색한다.

마지막으로 앞에서 Nested Loop 연산을 한 결과와 다시 Nested Loop 연산을 수행한다.

선두에서 이미 언급하였다시피 저장 프로시저라고 해서 실행 계획이 특별한 것은 아니다. 우리가 관심을 가져야 할 부분은 실행 계획 자체가 아니라, 저장 프로시저 안의 T-SQL구문 역시 일반 T-SQL문과 똑같이 실행 계획이 풀린다는 점이다.

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]Assert 실행계획 연산자(Assert Showplan Operator) (0)	2011.07.18
[MS SQL Server]실행 계획 연산자 리스트 (0)	2011.07.18
15. 텍스트 및 XML 실행 계획의 기초 - XML 실행 계획 (0)	2011.07.18
14. 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획 (0)	2011.07.18
13. 그래픽 실행 계획의 기초 - Insert, Update, Delete문의 실행 계획 (0)	2011.07.17

예상 XML 실행 계획

아래의 예제를 살펴보자.

이해를 돕기 위해 앞서 14. 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획 에서 사용하였던 예제를 그대로 사용하였다.

다만, 이번에는 XML 형식으로 예상 실행 계획을 캡처하기 위해 SHOWPLAN_XML 명령어를 실행하였다.
SET SHOWPLAN_XML ON;
GO
 
SELECT c.[LastName],
       a.[City],
       cu.[AccountNumber],
       st.[Name] AS TerritoryName
  FROM [Person].[Contact] c
  JOIN [Sales].[Individual] i
    ON c.[ContactID] = i.[ContactID]
  JOIN [Sales].[CustomerAddress] ca
    ON i.[CustomerID] = ca.[CustomerID]
  JOIN Person.Address a
    ON [ca].[AddressID] = [a].[AddressID]
  JOIN [Sales].Customer cu
    ON cu.[CustomerID] = i.[CustomerID]
  JOIN [Sales].[SalesTerritory] st
    ON [cu].[TerritoryID] = [st].[TerritoryID]
 WHERE st.[Name] = 'Northeast'
   AND a.[StateProvinceID] = 55;
GO
 
SET SHOWPLAN_XML OFF;
GO
이제 결과 창에 나타난 XML 문서의 링크를 클릭하자. 그러면 다음과 같이 새로운 탭에서 XML 실행 계획이 표시될 것이다.

[그림 1] XML 예상 실행 계획 1

그래픽 실행 계획과는 달리 XML 데이터는 한 눈에 쏙 들어오지 않을 것이다.

창 왼쪽을 보면 “+”, “-“ 버튼들이 위치되어 있는데, 각각의 XML element를 접었다 폈다 하면서 해석해 나가도록 하자.

XML 실행 계획 해석에 대한 기본적인 내용은 5. 실행 계획 기초 - XML 실행 계획 사용하기 에서 이미 살펴보았기 때문에 중복되는 내용은 건너뛰도록 하겠다.

먼저, BatchSquence > Batch > Statements > StmtSimple element 다음에 위치한 QueryPlan의 물리적인 속성을 보자.
<QueryPlan CachedPlanSize="50" CompileTime="322" CompileCPU="45" CompileMemory="520">
이는 캐시된 실행 계획의 크기와, 컴파일 하는데 얼마나 시간을 소요하였으며 또, CPU와 메모리는 얼마나 사용하였는지를 보여준다.

다음으로 MissingIndexes element를 보도록 하자. 여기에는 쿼리 옵티마이저가 실행 계획을 작성할 때 해당 쿼리에 관련하여 누락된 것으로 간주된 인덱스 정보를 보여준다.
<MissingIndexes>
  <MissingIndexGroup Impact="30.8535">
    <MissingIndex Database="[AdventureWorks]" Schema="[Sales]" Table="[CustomerAddress]">
      <ColumnGroup Usage="EQUALITY">
        <Column Name="[AddressID]" ColumnId="2" />
      </ColumnGroup>
      <ColumnGroup Usage="INCLUDE">
        <Column Name="[CustomerID]" ColumnId="1" />
      </ColumnGroup>
    </MissingIndex>
  </MissingIndexGroup>
</MissingIndexes>
이어서 RelOP 노드들이 보인다. 이것들은 쿼리 옵티마이저가 연산을 수행하는 동안 예상되는 물리 연산자들을 설명하고 있다. NodeId = 0 는 첫번째 노드로써 가장 마지막 수행된 NestedLoop 연산을 가리킨다.
<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join"
EstimateRows="1.94953" EstimateIO="0" EstimateCPU="6.37415e-005" AvgRowSize="119"
EstimatedTotalSubtreeCost="0.376226" Parallel="0" EstimateRebinds="0"
EstimateRewinds="0">
여기에 표시되는 정보들은 마치 그래픽 실행 계획의 ToolTip 창에서 보여지는 것과 유사하다. 텍스트 실행 계획에서는 단순히 EstimateExecutions만 표시되었던 것과는 달리 XML 실행 계획에서는 예상되는 Rebind와 Rewind 수치도 함께 보여주고 있다.

다음에 이어지는 element들은 ColumnReference element의 리스트인 OutputList element이다. 이는 SELECT에 의해 반환되는 각각의 컬럼 속성을 나타낸다.
<OutputList>
  <ColumnReference Database="[AdventureWorks]" Schema="[Person]"
     Table="[Contact]" Alias="[c]" Column="LastName" />
  <ColumnReference Database="[AdventureWorks]" Schema="[Person]"
     Table="[Address]" Alias="[a]" Column="City" />
  <ColumnReference Table="[cu]" Column="AccountNumber" ComputedColumn="1" />
  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]"
     Table="[SalesTerritory]" Alias="[st]" Column="Name" />
</OutputList>
부연 설명을 하면, 이는 AdventureWorks 데이터베이스의 Person 스키마의 (Alias가 c인) Contact 테이블에서는 LastName 컬럼을, (Alias가 a인) Address 테이블에서는 City 컬럼을, 그리고 Sales 스키마의 (Alias 가 st인) Sales 테이블에서는 Name 컬럼에서 결과를 가져옴을 의미한다. 물론 그래픽 실행 계획의 속성 창 및 ToolTip 에서도 이 내용을 확인할 수 있다.

마지막으로 설명할 내용은 Node 0의 OutputList 아래에 위치하고 있는 Nested Loop 연산이다. 여기에서는 Nested Loop 연산에 참여하고 있는 테이블 및 컬럼 정보 등을 확인할 수 있다.
<NestedLoops Optimized="0">
  <OuterReferences>
    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]"
       Alias="[cu]" Column="TerritoryID" />
  </OuterReferences>

실질 XML 실행 계획

위(예상 XML 실행 계획)에서 사용한 쿼리를 다시 한 번 실행해 보도록 하자. 이번에는 실질 실행 계획을 캡쳐하기 위해 STATISTICS XML 명령어를 사용하겠다.
SET STATISTICS XML ON;
GO
 
SELECT c.[LastName],
       a.[City],
       cu.[AccountNumber],
       st.[Name] AS TerritoryName
  FROM [Person].[Contact] c
  JOIN [Sales].[Individual] i
    ON c.[ContactID] = i.[ContactID]
  JOIN [Sales].[CustomerAddress] ca
    ON i.[CustomerID] = ca.[CustomerID]
  JOIN Person.Address a
    ON [ca].[AddressID] = [a].[AddressID]
  JOIN [Sales].Customer cu
    ON cu.[CustomerID] = i.[CustomerID]
  JOIN [Sales].[SalesTerritory] st
    ON [cu].[TerritoryID] = [st].[TerritoryID]
 WHERE st.[Name] = 'Northeast'
   AND a.[StateProvinceID] = 55 ;
GO
 
SET STATISTICS XML OFF;
GO
이제 실질 실행 계획을 살펴보자.

먼저, QueryPlan element 부분에 DegreeOfParallelism과 쿼리를 실행할 때 필요한 메모리 양을 의미하는 MemoryGrant가 추가적으로 보여짐을 확인할 수 있다.
<QueryPlan DegreeOfParallelism="0" MemoryGrant="82" CachedPlanSize="57"
CompileTime="29293" CompileCPU="6277" CompileMemory="520">
예상 XML 실행 계획과 실질 XML 실행 계획의 가장 큰 차이점은 누가 뭐래도 RunTimeInformation element이다. 말 그대로 쿼리가 실제로 실행되었을 때의 런타임 정보로써 Thread, 실제 행 수, 실제 실행 회수를 확인할 수 있다.
<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1"
    ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="0">…

다음 이야기: 16. 저장 프로시저에서의 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

[MS SQL Server]실행 계획 연산자 리스트 (0)	2011.07.18
16. 저장 프로시저에서의 실행 계획 (0)	2011.07.18
14. 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획 (0)	2011.07.18
13. 그래픽 실행 계획의 기초 - Insert, Update, Delete문의 실행 계획 (0)	2011.07.17
12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds (0)	2011.07.17

텍스트 및 XML 실행 계획의 기초

이번 포스트부터는 텍스트 실행 계획과 XML 실행 계획을 해석하는 방법에 대해 살펴 볼 것이다. 이미 실행 계획의 기초에서 간단히 살펴보았지만 텍스트 및 XML 실행 계획에는 그래픽 실행 계획과는 달리 아이콘이 없다. 다만 그것에 상응하는 연산자들만 포함하고 있다.

이전 버전의 SQL Server에서는 오직 텍스트 기반의 실행 계획만 사용 가능했다. 많이 사람들이 특히, 복잡한 실행 계획을 해석할 때 가독성이 떨어진다는 불평을 쏟아내었으며, 이에 마이크로소프트는 그래픽 실행 계획을 제공하기 시작했다. 확실히 그래픽 실행 계획은 텍스트 실행 계획보다 보기 편했다. 개발자는 물론 DBA들은 그래픽 실행 계획을 환영하였으며, 누구나 쉽게(?) 접근할 수 있는 실행 계획 해석 도구의 등장으로 텍스트 실행 계획은 이제 퇴물로써 역사의 뒤안으로 사라질 예정이다.

SQL Server 2005부터는 텍스트 기반의 실행 계획의 대안으로 XML 실행 계획을 제공하고 있다.

하지만 XML 실행 계획 또한 읽기 어려운 건 마찬가지이다. 특히, 실행 계획에서 XML 코드를 분리해 내기란 여간 쉬운 일이 아니다. 그렇다면 마이크로소프트가 이 불편하기 짝이 없는 XML 실행 계획을 텍스트 실행 계획의 대안으로 내세운 이유는 무엇일까?

여기에는 몇 가지 이유가 있다.

먼저, XML 실행 계획은 텍스트 기반의 실행 계획과는 달리 프로그램적으로 공용되고 있는 파일 형식이라는 점을 첫 번째 이유로 들 수 있다. 그리고 XML로는 텍스트과는 비교할 수 없을 정도로 풍부한 정보를 기록 할 수 있다는 장점도 있다. 또한 저장된 XML 실행 계획은 타인과 공유하기에도 용이하다. 예를 들어, 내가 XML 실행 계획을 다른 DBA에게 보내어 분석을 의뢰하면, 그 DBA는 SSMS에서 그래픽 실행 계획 포맷으로 열어 쉽게 분석할 수 있다.

텍스트 실행 계획

"이제 없어질 놈인데 텍스트 실행 계획을 공부할 필요가 있을까?"

아마 한 번쯤 이런 의문을 가졌을 것이다. 이 질문의 답은 여러분에게 있다. 만일 여러분의 환경에서 SQL Server 2005을 주로 사용하고 있다면 이 내용은 스킵하고 그래픽 실행 계획이나 XML 실행 계획에 포커스를 두고 공부해도 될 것이다. 하지만 이전 버전의 SQL Server를 여전히 사용하고 있다면 텍스트 실행 계획을 공부하기 싫어도 공부해야만 할 것이다.

이해를 돕기 위해 8. 그래픽 실행 계획의 기초 - Single Table Query 에서 사용한 쿼리를 작성해 보겠다.

SELECT ContactID,
LastName,
Phone
FROM Person.Contact
WHERE EmailAddress LIKE 'sab%'

이 쿼리의 그래픽 실행 계획을 상기시켜 보자.

[그림 1] 예제 쿼리의 그래픽 실행 계획

이제 텍스트 실행 계획을 생성해 보자. SHOWPLAN_ALL을 ON해야 하는 것, 그리고 실행 계획을 생성해야 한 후에는 반드시 OFF해야 한다는 것을 잊지 말자.

SET SHOWPLAN_ALL ON ;
GO

SELECT ContactID,
LastName,
Phone
FROM Person.Contact
WHERE EmailAddress LIKE 'sab%'

GO

SET SHOWPLAN_ALL OFF ;
GO

예상 실행 계획이 생성되었다.

[그림 2] 예제1 쿼리의 텍스트 실행 계획 1

첫 번째 행은 부모 노드로써 StmtText 열에는 실행된 T-SQL문이 저장되어 있다.

오른쪽으로 스크롤을 하면 아래의 그림처럼 Type이라는 열을 찾을 수 있을 것이다. Type은 해당 노드의 타입을 의미한다.

[그림 3] 예제1 쿼리의 텍스트 실행 계획 2

첫 번째 행의 Type에는 실행된 T-SQL문의 타입이 저장되어 있다. 여기서는 SELECT이다. 나머지 행들의 타입은 PLAN_ROW이다. 각 PLAN_ROW 노드의 StmtText 열에는 연산자가 저장되어 있다. 각각 Nested Loop Inner Join, Index Seek, Clustered Index Seek이다.

텍스트 실행 계획도 그래픽 실행 계획과 마찬가지로 "오른쪽에서 왼쪽으로, 위에서 아래로" 순으로 읽는다. 마치 오라클의 그것과 흡사하다. NodeID와 Parent 행과 함께 파이프 라인(|) 을 이용하면 연산자의 흐름을 이해하는데 도움이 될 것이다.

현재 노드의 ID는 무엇이며 그 부모 노드는 무엇인지 확인해 가며 파이프 라인을 따라가기만 하면 된다.

같은 부모 노드를 가진 행은 "위에서 아래로"의 순서대로 연산자가 실행된다. 이 예제에서는 Index Seek를 한 다음 Clustered Index Seek를 하고 있다.

이 실행 계획은 가장 움푹하게 들여져 보이는 세 번째 행(NodeId 3)인 Index Seek 연산에서 부터 시작한다. StmtText 열이나 Argument 열을 펼쳐보면 [Person].[Contact] 테이블에 대해 Index Seek를 하고 있음을 확인할 수 있다.

OBJECT:([AdventureWorks].[Person].[Contact].[IX_Contact_EmailAddress]),
SEEK:([AdventureWorks].[Person].[Contact].[EmailAddress] >= N'sab' AND
[AdventureWorks].[Person].[Contact].[EmailAddress] < N'saC'),
WHERE:([AdventureWorks].[Person].[Contact].[EmailAddress] like N'sab%') ORDERED
FORWARD

DefinedValues 행에는 주어진 PLAN_ROW에 연산자에 전달된 값들이 콤마 구분된 리스트로 나열되어 있다.

[AdventureWorks].[Person].[Contact].[ContactID],
[AdventureWorks].[Person].[Contact].[EmailAddress]

이 값들은 쿼리를 수행하기 위해 쿼리 프로세서가 필요로 하는 내부 값들로써, DefinedValues열은 연산자를 수행하기 위해서 쿼리 옵티마이저에 의해 사용되는 값들을 저장한 임시 저장소라고 보면 된다.

마지막으로 EstimateRows를 보면 Index Seek 연산으로 인해 얻어진 행 수는 19.8행이다.

다음 연산인 4행(NodeID 5)로 이동해 보자. [Person].[Contact] 테이블에 대해 Clustered Index Seek를 하고 있다. 텍스트 형식이기 때문에 금방 확인하기는 힘들지만, 이 연산은 Key Lookup 연산이다. 이 사실은 StmtText 열을 살펴보면 확인할 수 있다.

|--Clustered Index
Seek(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]),
SEEK:([AdventureWorks].[Person].[Contact].[ContactID]=[AdventureWorks].[Person].[
Contact].[ContactID]) LOOKUP ORDERED FORWARD)

EstimateRows 열을 확인해 보자. 이 번에는 1 행이 얻어졌다. 하지만 마지막 열인 EstimateExecutions 열을 보면, 쿼리가 실행되는 동안 이 연산자가 19.8번 호출되었음을 알 수 있다. 이는 선행 연산인 Index Seek(NodeID 3)과  19.8번 Nested Loop Inner Join 연산을 했기 때문이다. 하지만, 이 쿼리의 그래픽 실행 계획을 캡쳐한 후 Key LookUp 연산의 등록정보를 보면 이 보다 덜 실행되었음을 알 수 있다. 예제의 경우, Rewind는 18.8, Rebind는 0이다. Rewind와 Rebind에 대해서는 12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds 를 참조하기 바란다.

다음 연산은 NodeID 2이다. 앞서 설명하였다시피 선행 연산 결과를 Nested Loop Inner Join 연산을 하고 있다. 여기서 Defined Values가 Null인 것은 이 연산으로 어떤 새로운 값을 생성하지 않았기 때문이다. 그리고 OutputList를 보면 이 쿼리에서 요구한 결과 집합인 [ContactID], [LastName], [Phone] 컬럼이 보인다.

이 밖에, EstimateRows, EstimateIO, TatalSubTreeCost 와 같은 행에 대한 설명은 이미 그래픽 실행 계획의 기초에서 다루웠던 내용이므로 생략하도록 하겠다.

지금까지 살펴본 예제는 아주 단순한 쿼리로써 순서대로 쭉 읽어나가기만 하면 되었다. 이제 조금 복잡한 쿼리를 작성하여 다음 이야기를 진행하도록 하겠다.

SET SHOWPLAN_ALL ON ;
GO

SELECT c.[LastName],
a.[City],
cu.[AccountNumber],
st.[Name] AS TerritoryName
FROM [Person].[Contact] c
JOIN [Sales].[Individual] i ON c.[ContactID] = i.[ContactID]
JOIN [Sales].[CustomerAddress] ca ON i.[CustomerID] =
ca.[CustomerID]
JOIN Person.Address a ON [ca].[AddressID] = [a].[AddressID]
JOIN [Sales].Customer cu ON cu.[CustomerID] = i.[CustomerID]
JOIN [Sales].[SalesTerritory] st ON [cu].[TerritoryID]
= [st].[TerritoryID]
WHERE st.[Name] = 'Northeast'
AND a.[StateProvinceID] = 55 ;
GO

SET SHOWPLAN_ALL OFF ;
GO

이 쿼리를 실행하면 예상 실행계획이 결과 창에 표시된다.

[그림 4]는 실행 계획 결과의 StmtText 컬럼의 내용을 보여주고 있다.

[그림 4] 예제2 쿼리의 텍스트 실행 계획 1

역시 파이프 라인을 따라서 가장 움푹하게 들어가 있는 부분부터 읽어나가도록 하자.

이 쿼리에서는 가장 먼저, [Person].Address] 테이블의 [IX_Address_StateProvinceId] 인덱스를 Index Seek를 하고 있다.

[그림 5] 예제2 쿼리의 텍스트 실행 계획 2

이 연산을 통해 WHERE절에 조건으로 기술한 내용(a.[StateProvinceID] = 55)을 걸려낸다. 걸러진 결과 집합은 이어지는 모든 연산들이 사용하게 되므로 전체 작업량에 큰 영향을 미치게 된다. 따라서 이 연산에서 검색 범위를 얼마나 축소를 시키는냐가 성능 관점에서 중요한 포인트가 될 것이다. 이 연산으로 부터 얻어진 결과 값은 [AddressId] 컬럼이다. [AddressId]는 SELECT 리스트에는 포함되어 있지 않지만 다음 연산에서 필요로 한다.

--Index Seek(
OBJECT:([AdventureWorks].[Person].[Address].[IX_Address_StateProvinceID] AS [a]),
SEEK:([a].[StateProvinceID]=(55)) ORDERED FORWARD)

이어지는 연산은 Clustered Index Seek 연산이다.

공급받은 [AddressId]를 사용하여 [Person].[Address] 테이블의 [PK_Address_AddressID] 인덱스를 검색한다.

StmtText 컬럼을 보면 확인할 수 있듯이 이 연산은 Key Lookup 연산이다.

--Clustered Index Seek
(OBJECT:([AdventureWorks].[Person].[Address].[PK_Address_AddressID] AS [a]),
SEEK:([a].[AddressID]=[AdventureWorks].[Person].[Address].[AddressID] as
[a].[AddressID]) LOOKUP ORDERED FORWARD)

다음 연산자를 해석하기 위해 파이프 라인의 한 스텝 바깥(8행)으로 가보자.

여기서는 선행된 두 개의 연산자의 결과를 Nested Loop Join을 하고 있다.

[그림 6] 예제2 쿼리의 텍스트 실행 계획 3

이제, 파이프 라인을 쭉 타고 밑으로 내려가 보자. 11행에 도달할 것이다.

[Sales].[CustomerAddress] 테이블의 [AK_CustomerAddress_rowguid] 인덱스 전 영역에 대해 Index Scan을 하고 있다.

--Index Scan(OBJECT:([AdventureWorks].[Sales].[CustomerAddress].[AK_CustomerAddress_rowguid]AS [ca]))

이 연산에 의해 약 19,000 행의 결과 집합이 생성되고, 생성된 결과는 다음 연산인 Hash Match Join으로 전달된다.

[그림 7] 예제2 쿼리의 텍스트 실행 계획 4

마찬가지로 Hash Match 연산자의 파이프 라인을 따라가 보자. 12행인 Compute Scalar 연산에 도달할 것이다.

여기서는 선행 연산 즉, [Sales].[Customer] 테이블의 PK_Customer_CustomerID 인덱스를 Clustered Index Seek 연산한 결과에 대해 Compute Scalar 연산을 수행하고 있다.

그런데, 좀 이상하지 않는가?

실행된 쿼리에는 Scalar 연산이 포함되어 있지 않는데 Compute Scalar 연산을 하고 있다.

도대체 왜???

그 답을 얻기 위해 다시 한 번 실행 계획을 살펴보자.

StmtText열을 보면 SELECT 리스트에 있는 [AccountNumber] 열을 조회하기 위해, isnull.. 이라는 스칼라 함수를 사용하고 있다.

-- Compute Scalar

(DEFINE:([cu].[AccountNumber]=isnull('AW'+[AdventureWorks].[dbo].[ufnLeadingZeros]

([AdventureWorks].[Sales].[Customer].[CustomerID] as [cu].[CustomerID]),'')))

[Sales].[Customer] 테이블의 DDL문을 추출해 보면 보다 명백해 진다.

[AccountNumber] 열이 (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')) 로 정의되어 있기 때문에 [AccountNumber] 값을 얻기 위해 Compute Scalar 연산을 수행한 것이다.

Scalar 연산의 결과와 Hash Match 연산의 결과는 다음 이어지는 Nested Loop 연산에 의해 결합된다.

[그림 8] 예제2 쿼리의 텍스트 실행 계획 5



그 다음 연산을 확인하기 위해 다시 한 스텝 밖으로 이동하자.

[AccountNumber] 열을 다시 Compute Scalar 연산을 하고 있다.

앞서 살펴본 Compute Scalar 연산은 함수식을 이용해서 [Sales].[Customer].[CustomerID] 컬럼을 계산한 것이고, 이 스텝에서의 Compute Scalar 연산은 [AccountNumber] 을 정의한 것이다.

[그림 9] 예제2 쿼리의 텍스트 실행 계획 6



파이프 라인을 따라 같은 레벨을 찾아가 보면, PK_Individual_CustomerId 인덱스에 대해 Clustered Index Seek 연산을 하고 있는 것이 보인다. 이 두 결과는 아래의 그림의 4행에서 Nested Loop Join을 통해 결합된다.

[그림 10] 예제2 쿼리의 텍스트 실행 계획 7

이 결과는 다시, 15행의 [Person].[Contact] 테이블에 대해 Clustered Index Seek 연산을 한 결과와 Nested Loop Join으로 3행에서 결합되고,

[그림 11] 예제2 쿼리의 텍스트 실행 계획 8

결합된 결과는 2행에서, [Sales].[SalesTerritory] 테이블을 Clustered Index Seek 연산을 한 결과와 마지막으로 Nested Loop Join을 한 후, 최종 결과 집합을 1행에서 보여주게 된다.

[그림 12] 예제2 쿼리의 텍스트 실행 계획 9

다음 이야기 : 15. 텍스트 및 XML 실행 계획의 기초 - XML 예상 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

16. 저장 프로시저에서의 실행 계획 (0)	2011.07.18
15. 텍스트 및 XML 실행 계획의 기초 - XML 실행 계획 (0)	2011.07.18
13. 그래픽 실행 계획의 기초 - Insert, Update, Delete문의 실행 계획 (0)	2011.07.17
12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds (0)	2011.07.17
11. 그래픽 실행 계획의 기초 - GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획 (0)	2011.07.17

Insert, Update, Delete문의 실행 계획

드디어, 그래픽 실행 계획의 기초의 마지막 이야기이다. 지금까지 우리는 SELECT문에 대해서만 살펴보았다. 이번 포스트에서는 다른 DML 즉, INSERT, UPDATE 그리고 DELETE문의 실행 계획을 살펴 보도록 하겠다.

Insert문

먼저, 아래와 같이 간단한 INSERT문을 작성해 보자.

INSERT INTO [AdventureWorks].[Person].[Address]
(
[AddressLine1],
[AddressLine2],
[City],
[StateProvinceID],
[PostalCode],
[rowguid],
[ModifiedDate]
)
VALUES (
'1313 Mockingbird Lane',
'Basement',
'Springfield',
'79',
'02134',
NEWID(),
GETDATE()
) ;

이 쿼리의 실행 계획은 다음과 같다.

[그림 1] INSERT문의 실행 계획

가장 먼저 보이는 것은 Constant Scan 연산자이다. 이 연산자는 입력(INSERT)할 상수 행을 작성하여 쿼리에 전달한다. 그 뒤로 Constant Scan 연산자에 의해 생성된 행에 열을 추가하기 위해 두 개의 Compute Scalar 연산자가 따라오고 있다. 이 예제의 경우, NEWID()로 생성된 uniqueidentifier와 GETDATE()로 부터 얻은 날짜 값을 행에 추가하고 있다.

생성된 행은 Clustered Index Insert 연산자를 통과한다. 이 연산자가 가장 많은 비용을 사용하고 있다. 이 때, INSERT문으로 부터 얻어진 출력 값은 [Person].[Address].[StateProvinceId]이다.

이어지는 연산은 Nested Loop Join 이다. [Person].[StateProvince] 테이블을 Clustered Index Seek 연산한 결과와 Join하고 있다. 이는 StateProvinceId 컬럼의 참조 무결성을 체크하기 위함이다. 참조 무결성의 실질적인 체크는 이어서 보이는 Assert 연산자가 수행한다. Assert 연산자는 참조 무결성 뿐만 아니라, 기타 제약 조건 및 스칼라 하위 쿼리의 유효성 검사도 한다. Assert 연산자가 계산한 식이 NULL이면 Assert 연산자를 통과하여 쿼리 실행을 계속하고, 이 식이 NULL이 아니면 해당 오류를 발생시킨다.

Update문

아래와 같은 UPDATE문을 작성해 보자.

UPDATE [Person].[Address]
SET [City] = 'Munro',
[ModifiedDate] = GETDATE()
WHERE [City] = 'Monroe' ;

이 쿼리의 예상 실행 계획은 다음과 같다.

[그림 2] UPDATE문의 실행 계획

그럼, 실행 계획을 살펴보도록 하자.

가장 먼저 실행되는 것은 Non-clustered Index Scan 이다. 이 연산자는 이 쿼리에서 UPDATE할 대상 행을 조회하고 있다. 즉, WHERE [City] = 'Monroe' 조건에 해당하는 행을 조회하여 연산 결과를 다음에 이어지는 TOP 연산자에게 전달한다. TOP 연산자는 UPDATE할 행 수를 제한한다. 하지만, 이 쿼리의 경우 UPDATE문에 TOP 구문을 기술하지 않았으므로 행을 제한하지 않는다.

다음으로 보이는 것은 Eager Spool 연산자이다. 이 연산자는 Table Spool의 한 형태로써, UPDATE할 각 행을 tempdb 데이터베이스에 저장된 숨겨진 임시 개체에 저장한다. 이는 Nested Loops 연산자 등으로 연산자를 rewind를 할 경우, 다시 바인딩할(rebind) 필요가 없다면 데이터를 다시 스캔하는 대신 -여기서는 Non-clustered Index Scan을 반복하는 것을 의미한다. - 스풀된 데이터를 재사용하기 위함이다. 하지만 다시 바인딩을 해야 하는 경우에는 이미 스풀된 데이터를 삭제하고 다시 검색하여 스풀 개체를 다시 작성한다.

이어지는 연산자는 Compute Scalar 연산자 3개이다. 여기서는 GETDATE() 함수와 같이 쿼리 내의 Scalar 값을 계산하고 있다.

마지막으로 UPDATE문의 가장 핵심적인 부분인 Clustered Index Update 연산을 하고 있다. 이 예제의 경우 Clustered Index의 한 부분을 갱신하고 있으므로 Clustered Index Update 연산을 통해 갱신할 행을 식별하고 해당 행을 갱신한다.

성능적인 이슈를 살펴보면, 갱신할 행을 어떻게 검색을 하느냐?가 가장 중요하다. 이 예제의 경우, Non-clustered Index Scan을 하고 있다. 앞서 설명하였다시피 이 연산은 효율적이지 못하다. 예제의 경우 16개의 행을 갱신하기 위해 19615행에 대해 Non-clustered Index 전체를 읽어야만 한다. 따라서 Clustered Index Seek나 Non-Clustered Index Seek를 유도하는 것이 바람직할 것이다.

Delete문

DELETE문에서는 어떤 실행 계획이 만들어 질까? 아래의 쿼리를 작성한 후 실행 계획을 살펴보도록 하자.

DELETE FROM [Person].[Address]
WHERE [AddressID] = 52;

[그림 3] DELETE문의 실행 계획

실행 계획을 살펴보자. 언제나 그렇듯이 오른쪽에서 시작하여 위에서 아래로…

가장 먼저 실행되는 것은 Clustered Index Delete 연산자이다.

우리는 여기서 다른 DML문에서의 실행 계획에서는 볼 수 없었던 재미있는 사실, 두 가지를 확인할 수 있다.

첫 번째는 DELETE 연산이 프로세스의 첫 부분에서 실행되고 있다는 것이고,

두 번째는 Clustered Index Delete 연산의 Seek Predicate :

Prefix: [AdventureWorks].[Person].[Address].AddressID = Scalar

Operator(CONVERT_IMPLICIT(int,[@1],0)).

이다.

@1 파라미터는 [AddressId]를 의미한다. 우리는 이 파라미터를 사용한 적이 없다. 단지, [AddressId]의 상수 값으로 52만 제공한 것 뿐이었다. 그렇다면 이 파라미터는 도대체 어디에서 온 것인가?

이는 실행 계획의 재사용을 목적으로 Realtional Engine이 쿼리를 재작성(simple parameterization)하였기 때문이다.

이것은 마치,

DELETE FROM [Person].[Address]
WHERE [AddressID] = @1;

와 같이 앞으로 [AddressId] 에 어떤 값이 바인딩되던간에 같은 실행 계획을 사용할 수 있도록 SQL Server가 알아서 쿼리를 재구성한 것이다. 어떤가? 정말 똑똑하지 않은가?

[그림 4] DELETE문의 실행 계획 - Clustered Index Delete

DELETE 후 Nested Loop Join 연산을 통해 일련의 Index Seek, Clustered Index Seek, Cluastered Index Scan 연산 결과가 결합하고 있다. 특히 여기서는 Left Semi Join을 하고 있다. 그리고 마지막으로 각 Join 연산으로 부터 얻어진 결과 값을 Assert 연산자에 통과시키고 있다. 이는 앞서 설명하였듯이 삭제하고자 하는 데이터와 관련된 모든 테이블에 대해 참조 무결성을 체크하기 위함이다.

[그림 5] DELETE문의 실행 계획 - Assert

요약

지금까지 우리는 그래픽 실행 계획을 해석하는 방법을 살펴보았다. 특히 수십여 개의 실행 계획 중에서 가장 많이 사용되는 것들 중심으로 아주 단순한 쿼리를 통해 살펴보았다. 물론 지금까지의 내용을 다 숙지하였다고 해서 지금 당장 수십, 수백 라인에 이르는 복잡한 쿼리의 실행 계획을 순식간에 해석할 수는 없을 것이다. 하지만 연습보다 훌륭한 선생이 없다. 실행 계획을 꾸준히 읽고 해석하는 연습을 반복하다가 보면 언젠가는 통! 하지 않을까?

다음 이야기 : 14 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

15. 텍스트 및 XML 실행 계획의 기초 - XML 실행 계획 (0)	2011.07.18
14. 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획 (0)	2011.07.18
12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds (0)	2011.07.17
11. 그래픽 실행 계획의 기초 - GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획 (0)	2011.07.17
10. 그래픽 실행 계획의 기초 - WHERE절의 추가 (0)	2011.07.17

Rebinds와 Rewinds

그래픽 실행 계획의 기초를 진행해 오던 동안 다음과 같은 물리 연산자를 몇 번 보았을 것이다.

Actual Rebinds 또는 Estimated Rebinds
Actual Rewinds 또는 Estimated Rewinds

혹시 어디에서 보았는지 기억나는가?

대부분의 경우 rebinds와 rewinds의 이 두 값은0이었다. 하지만 11.그래픽 실행 계획의 기초 – GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획 의 [그림 2] Sort의 ToolTip을 다시 살펴보면 Actual Rebinds는 1이고 Actual Rewinds는 0이라는 것을 확인할 수 있을 것이다.

이 값의 의미를 이해하기 위해서는 물리 연산자에 대한 약간의 배경 지식이 필요하다. 실행 계획 내에서 Sort 연산자와 같은 물리 연산이 발생할 때마다 다음과 같은 일들이 일어난다.

첫째, 물리 연산자가 초기화되고 필요로 하는 데이터 구조가 생성된다. 이를 Init() 메소드라고 한다. 이 메소드는 여러 번 호출하는 것이 가능하긴 하지만 연산자당 오직 한 번만 호출된다.
둘째, 물리 연산자가 데이터 행을 취득한다. 이를 GetNext() 메소드라고 한다. 이 메소드는 연산자 타입에 의존하며, 한 번도 호출되지 않을 수도 있고, 혹은 여러 번 호출될 수도 있다.
셋째, 연산자의 처리가 끝나면 생성된 데이터 구조를 말끔히 비우고 종료시켜야 한다. 이를 Close() 메소드라고 하며, 연산자당 오직 한 번만 호출된다.

Rebind 또는 Rewind는 연산자에 의해 Init() 메소드가 호출된 회수를 의미한다. Rebind와 Rewind 모두 Init() 메소드가 호출될 때 카운트를 하지만 호출 시기는 조금 다르다. Rebind count는 Join과 관련된 파라미터가 하나 이상 바뀌어 내측 집합(Inner set혹은 내측 루프)이 재평가 될 때 발생하며, Rewind count는 파라미터는 바뀌지 않았으나 이전의 내측 집합이 재사용될 때 발생한다.

지금까지의 내용을 종합해 볼 때, 모든 물리 연산자의 Rebind와 Rewind 값은 ToolTip이나 속성 창에서 반드시 1 이상의 값으로 채워져 보일 것으로 예상할 수 있을 것이다. 하지만 실제로는 그렇지 않다. 이상하게도 아래와 같은 특정 물리 연산자에서만 Rebind와 Rewind 값이 채워진다.

Non-clustered Index Spool

Remote Query

Row Count Spool

Sort

Table Spool

Table-Valued Function

그리고 다음의 물리 연산자들은 해당 물리 연산자의 StartupExpression이 True로 설정되는 경우에만 그 값이 채워지는데, 이는 쿼리 옵티마이저가 쿼리를 어떻게 평가하느냐에 달라진다. 이것은 마이크로소프트가 코드로 설정한 것으로 여러분이 제어할 수 없다.

Assert
Filter

이 밖의 물리 연산자에서는 Rebind와 Rewind 값이 채워지지 않는다. 이 경우, Rebind와 Rewind는 0으로 표시된다. 0라고 해서 Rebind와 Rewind가 한 번도 발생하지 않았다고 의미하는 것은 아니다. 단지 그 값이 채워지지 않았음을 의미한다.

그렇다면, 위의 8개 연산자들에서 Rebind와 Rewind 에 채워진 값은 무엇을 의미하는가?

이쯤에서 11.그래픽 실행 계획의 기초 – GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획 의 [그림 2] Sort의 ToolTip을 다시 한 번 보도록 하자. 이 연산자에서 Rebind는 1, Rewind는 0이다. 즉, 물리 연산자에 의해 Init() 메소드가 한 번 호출되었으며, 이 연산자는 루프 조인의 내측 집합에 위치하고 있지 않다는 것을 의미한다. 만일 물리 연산자가 루프 조인의 내측 집합에 위치하게 되면, Rebind와 Rewind의 합계는 연산자에 의해 사용된 외측 집합의 행 수와 같게 된다.

그렇다면 이 값을 어떻게 이용해야 할까? Rebind와 Rewind 는 그 값이 작을수록 좋다. 이 값이 커질수록 디스크 I/O가 보다 많이 발생하고 있음을 의미한다. 이 값이 크다는 것은 특정 연산자가 필요 이상으로 힘들게 일하고 있음을 의미하므로 성능에 나쁜 영향을 준다. 이 경우 쿼리 재작성(Query Rewrite)이나 쿼리가 사용 중이었던 인덱스가 수정되었을 가능성을 의심해 보아야 한다.

다음이야기 : 13. 그래픽 실행 계획의 기초 - Insert, Update, Delete문의 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

14. 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획 (0)	2011.07.18
13. 그래픽 실행 계획의 기초 - Insert, Update, Delete문의 실행 계획 (0)	2011.07.17
11. 그래픽 실행 계획의 기초 - GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획 (0)	2011.07.17
10. 그래픽 실행 계획의 기초 - WHERE절의 추가 (0)	2011.07.17
9. 그래픽 실행 계획의 기초 - Table Joins (0)	2011.07.17

GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획

정렬(Sort)

아래와 같이 ORDER BY절을 포함한 간단한 쿼리를 작성해 보자.

SELECT *
FROM [Production].[ProductInventory]
ORDER BY [Shelf]

실행 계획은 다음과 같다.

[그림 1] Sort

Clustered Index Scan 연산의 결과 집합이 Sort 연산에 공급되고 있다. Sort 연산은 문자 그대로 쿼리 옵티마이저가 데이터를 정렬할 때 나타난다. ORDER BY절에 오름차순(ASC)으로 정렬할지 내림차순(DESC)으로 정렬할지 명시하지 않으면 기본적으로 오름차순(ASC)으로 정렬된다.

[그림 2] Sort의 ToolTip

Sort 연산의 ToolTip을 보면 Sort 연산으로 1,069행이 통과함을 확인할 수 있다. Sort 연산자는 선행 연산인 Clustered Index Scan 연산으로 부터 1,069 행의 집합을 공급받아, 이를 정렬한 후, 정렬된 1,069 행을 반환하고 있다. 여기서 흥미롭게 지켜봐야 할 것 하나! 그것은 Sort 연산이 이 쿼리의 전체 비용 중 76%를 차지하고 있다는 점이다. 여러분의 쿼리에서 Sort의 비용이 전체 비용의 50% 이상을 차지하고 있다면 최적화의 여지가 있는지 주의깊게 살펴볼 필요가 있다.

다음을 생각해 보자.

정렬이 정말로 필요한가?
만일 필요하지 않다면 정렬로 인한 오버헤드를 제거할 수 있을 것이다.
우리가 원하는 순서대로 이미 데이터가 정렬되어 있으면 어떨까?
예를 들어, 우리가 원하는 정렬 순서대로 Clustered Index를 만들면 Clustered Index의 특성상 정렬 연산이 필요 없어질 것이다. 하지만 이 방법은 항상 사용 가능한 방법은 아니다. 이유는 간단하다. Clustered Index는 테이블 당 하나만 만들 수 있기 때문이다.
정렬할 데이터가 적으면 정렬 비용도 적게 들텐데...
조건절에서 정렬할 데이터를 제한하면 정렬 비용도 당연히 줄어들 것이다. 예제의 쿼리에서 WHERE [ProductID] = 1의 조건을 통해 선행 집합을 3행으로 줄이면, 정렬할 데이터도 3행으로 줄어들게 되어 정렬 비용도 감소하게 될 것이다.
만일 실행 계획 내에 복수의 Sort 연산이 나타난다면...
그 정렬들이 다 필요한 것인지 또는, 정렬을 줄일 수 있도록 쿼리 튜닝이 가능한지 살펴보자.

여기서 잠깐 두 번째 고려 사항을 증명해 보겠다.

예제의 쿼리를 아래와 같이 바꾸면

SELECT *
FROM [Production].[ProductInventory]
ORDER BY [ProductID]

다음과 같은 실행 계획을 얻을 수 있다.

앞서 작성한 쿼리에서 달라진 부분은 ORDER BY절의 정렬 컬럼이 [Shelf]에서 [ProductID]로 바뀐 것 뿐이다.

[그림 3] Clustered Index를 이용하여 Sort 연산 제거하기

눈치챘는가? 실행 계획에서 Sort 연산이 사라졌다!

왜냐하면 정렬 기준인 [ProductID]컬럼이 Clustered Index([ProductID] + [LocationID]) 내에 포함되어 있고, 데이터는 이미 [ProductID] + [LocationID] 순으로 정렬되어 있기 때문이다. 쿼리 옵티마이저는 데이터가 이미 정렬되어 있음을 알고 있을 만큼 영리한 것이다. 한 번 쓰다듬어 주자 ^^;

만일 어쩔 수 없이 대량의 데이터를 정렬해야 하는 경우가 발생한다면, SQL Server Profiler를 사용하여 Sort Warning 이 벤트가 표시되는지 반드시 확인할 필요가 있다. SQL Server는 정렬의 성능을 높이기 위해 디스크보다는, 그보다 훨씬 빠른 메모리를 사용하려 한다. 그러나 정렬하고자 하는 데이터가 메모리가 감당할 수 없을 정도로 터무니 없이 크면, 어쩔 수 없이 디스크 즉, tempdb 데이터베이스를 이용해서 정렬한다. 아무튼 디스크 정렬이 발생하면 SQL Server는 Sort Warning 이벤트를 쓰는데, 여러분의 서버 내에서 정렬 작업이 자주 수행되고 있고 Sort Warning 이벤트가 많이 발생되고 있는 것이 확인되면, 메모리를 증설하거나 tempdb의 읽기 쓰기 속도를 높이는 방안을 모색해야 한다.

Hash Match(Aggregate)

우리는 앞서 9. 그래픽 실행 계획의 기초 - Table Joins에서 Hash Match 연산을 살펴본 바 있다. Hash Match연산은 비단 조인에서 뿐만 아니라 단일 쿼리에서도 발생한다. 자 그럼, COUNT 연산자를 사용해서 하나의 쿼리를 집계하는 쿼리를 가정해 보도록 하자.

SELECT [City],
COUNT([City]) AS CityCount
FROM [Person].[Address]
GROUP BY [City]

[그림 4] Hash Match (Aggregate)

쿼리 옵티마이저는 먼저 Index Scan 연산을 시작한다. 왜냐하면 우리가 원하는 집계 데이터를 얻기 위해서는 테이블 내의 모든 행을 필요로 하기 때문이다. 행을 제한하는 그 어떤 조건문도 없다. 그런 다음 선행 연산에서 공급받은 행들에 대해 요청받은 COUNT 집계 연산을 수행한다. 쿼리 옵티마이저가 도시별로 등록된 주소 수를 집계하기 위해서는 반드시 Hash Match 연산을 수행해야만 한다. 여기서 Hash Match 연산자 아래에 괄호로 (Aggregate) 라고 표기되어 있음을 주목하기 바란다. 이런 식으로 조인에서의 Hash Match 연산과 구별하고 있다. Hash Match Join에서와 마찬가지로, SQL Server는 GROUP BY 컬럼에 해당되는 행 수를 집계하기 위해 메모리상에 Hash Table을 생성한다. 여기서는 [City] 컬럼이다. 집계가 완료되면 그 결과를 우리에게 반환한다.

정렬과 마찬가지로 집계는 상당히 비용이 비싼 연산이다. 이 경우 성능 개선을 위해 WHERE절에서 집계할 집합을 충분히 줄여서 집계 처리 비용을 줄이는 방법 밖에 없다.

Filter

위에서 작성한 쿼리에 다음과 같이 간단한 HAVING절을 추가해 보도록 하자.

SELECT [City],
COUNT([City]) AS CityCount
FROM [Person].[Address]
GROUP BY [City]
HAVING COUNT([City]) > 1

[그림 5] Filter 연산

HAVING절을 추가함에 따라 실행 계획 내에 Filter 연산자도 함께 추가되었다. Filter 연산자는 우리가 공급한 조건 즉 [City]컬럼을 집계한 행 수가 1이상인 것만 걸러낸다. HAVING절의 내용은 COUNT 집계 연산이 완료되기 전까지 적용되지 않는다. 즉, HAVING절은 집계에 아무런 영향을 주지 못하며 집계가 끝나고 나서야 그 내용이 적용된다. 이는 Hash Match 연산자와 Filter 연산자의 ToolTip을 보면 금방 확인할 수 있다. Hash Match 연산 결과 575 행이, Filter 연산 결과 348행이 반환되고 있다.

[그림 6] Filter연산과 Hash Match 연산의 ToolTip

결과적으로 HAVING절을 추가함으로써 반환되는 결과 집합은 줄일 수 있으나 반대로, 원하는 결과 집합을 생성하기 위한 추가적인 비용(Filter 연산)이 발생한다. 간단하게 이야기해서 성능에 좋지 않다는 것이다.

다음 이야기 : 12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

13. 그래픽 실행 계획의 기초 - Insert, Update, Delete문의 실행 계획 (0)	2011.07.17
12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds (0)	2011.07.17
10. 그래픽 실행 계획의 기초 - WHERE절의 추가 (0)	2011.07.17
9. 그래픽 실행 계획의 기초 - Table Joins (0)	2011.07.17
8. 그래픽 실행 계획의 기초 - Single Table Query (0)	2011.07.17

WHERE절의 추가

설명에 앞서 아래의 쿼리를 실행해보자. 앞서 9. 그래픽 실행 계획 기초 - Table Joins 에서 사용된 쿼리에서 WHERE절이 추가 되었다.

SELECT e.[Title],
a.[City],
c.[LastName] + ',' + c.[FirstName] AS EmployeeName
FROM [HumanResources].[Employee] e
JOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID] =
ed.[EmployeeID]
JOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID]
JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID]
WHERE e.[Title] = 'Production Technician - WC20' ;

실행 계획은 다음과 같다.

[그림 1] WHERE절을 추가했을 때의 실질 실행 계획

먼저, 쿼리 옵티마이저는 Primary Key인 PK_Employee_EmployeeID를 Clustered Index Scan하기 위해 WHERE절을 통해 판단하고 있다. Clustered Index Scan 연산자의 ToolTip을 보면 WHERE절에 의해 예상 행 수가 22행으로 제한되고 있다.

[그림 2] Clustered Index Scan의 ToolTip

9. 그래픽 실행 계획 기초 - Table Joins에서의 실행 계획과 비교해 보도록 하자.

우선, WHERE조건에 의해 22행으로 선행 집합이 상당히 좁혀졌고, [Person].[Contact]에서도 좋은 인덱스를 선택하였기 때문에, 이번에는 쿼리 옵티마이저가 Hash Match Join보다 유용한 Nested Loops Join을 이용할 수 있다. 조인 방식이 바뀜에 따라 Scalar 연산 또한 이 조인의 바로 다음으로 이동되었다. Scalar 연산을 통해 얻어진 결과 - 22행만 Scalar 연산을 하면 된다 - 는 다시, [HumanResources].[EmployeeAddress] 테이블과 [Person].[Address] 테이블을 차례로 Nested Loops Join을 하여 최종 결과를 얻어낸다. 이 때 각각의 테이블에서는 Clustered Index Seek 연산을 하였다.

어떤가? 9. 그래픽 실행 계획 기초 - Table Joins에서의 실행 계획보다 상당히 효율적이지 않은가? 이는 모두 WHERE 절에서 초기 집합을 줄였기 때문에 가능한 것이다.

다음 이야기 : 11. 그래픽 실행 계획의 기초 - GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds (0)	2011.07.17
11. 그래픽 실행 계획의 기초 - GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획 (0)	2011.07.17
9. 그래픽 실행 계획의 기초 - Table Joins (0)	2011.07.17
8. 그래픽 실행 계획의 기초 - Single Table Query (0)	2011.07.17
7. 그래픽 실행 계획의 기초 - 그래픽 실행 계획의 언어 (0)	2011.07.17

Table Joins

지금까지 우리는 하나의 테이블만 이용해서 실행 계획들을 살펴보았다. 지금부터는 두 개 이상의 테이블을 연결하기 위해 조인이라는 것을 해 보도록 하겠다. 아래는 HumanResources 스키마의 테이블들을 이용해서 사원 정보를 조회하는 쿼리이다.

SELECT e.[Title],
a.[City],
c.[LastName] + ', ' + c.[FirstName] AS EmployeeName
FROM [HumanResources].[Employee] e
JOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID] = ed.[EmployeeID]
JOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID]
JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID];

실행 계획은 다음과 같다.

[그림 1] Table Join

이 쿼리는 실행 계획에서도 파악할 수 있듯이 상당히 많은 비용을 사용하는 연산들을 하고 있다. 여기에서 가장 많은 비용을 사용하는 연산은 다음의 3가지로 압축할 수 있다.

[Person].[Address] 테이블에 대한 Index Scan 연산 : 45%
[HumanResource].[EmployeeAddress] 테이블과 [Person].[Address] 테이블 사이의 Hash Match 조인 연산 : 28%
[Person].[Contact] 테이블에 대한 Clustered Index Seek 연산 : 18%

이미 설명한 바 있듯이 비용은 실행 트리의 오른쪽에서 왼쪽으로 이동해 가며 누적 표시된다. 상대 비용은 각각의 실행 계획 아이콘에서 확인할 수 있다.

자! 그럼 하나 하나 살펴보기로 하자. 그래픽 실행 계획을 읽는 순서는 "오른쪽에서 왼쪽으로, 위에서 아래로"라는 것을 상기시키고...

가장 먼저 오른쪽 상단에 [HumanResources].[EmployeeAddress] 테이블에 대한 Index Scan이 보인다. 그리고 그 아래로 [Person].[Address] 테이블에 대한 또 하나의 Index Scan이 보인다. 이 연산은 가장 비용이 높은 연산이다. ToolTip을 열어보도록 하자. IX_Address_AddressLine_AddressLine2_City_StateProvinceId_PostalCode 인덱스를 스캔하여 우리가 필요로 하는 데이터를 얻기 위해 19,614 행을 엑세스하고 있음을 금방 확인할 수 있을 것이다.

[그림 2] Table Join - Index Scan 연산 ToolTip

그리고, Output List에서 보여지듯이 이 연산을 통해 [AddressID]와 [City] 컬럼을 얻었다. 쿼리 옵티마이저는 인덱스를 통해 해당 데이터를 취득하기 위한 최적의 방법을 해당 테이블의 인덱스와 컬럼의 선택도를 기반으로 계산한다. 19,614행을 얻기 위해 총 쿼리 비용의 45%가 소요되며, 예상 연산 비용(Estimated Operator Cost)은 0.180413이다. 예상 연산 비용은 해당 연산을 수행하기 위해 필요한 비용을 옵티마이저가 내부적으로 계산된 수치로써, 이 값은 작으면 작을수록 좋다.

Hash Match(Join)

계속해서 위의 예제를 살펴보자. 위의 두 Index Scan을 통해 얻어진 결과를 Hash Match Join을 통해 결합하고 있다. 이 연산은 이 쿼리에서 두 번째로 비싼 비용을 사용하는 연산으로 ToolTip에서 보여지는 내용은 다음과 같다.

[그림 3] Table Join - Hash Match 연산 ToolTip

Hash Match Join을 이해하기 위해서는 먼저, hashing과 hash table이라는 두 가지 컨셉을 알아야 한다.

Hashing(이하 해싱)은 데이터를 좀 더 빨리 검색하기 위해 고안된 프로그램적인 기술로써, 데이터가 저장되어 있는 주소를 함수로 계산한 후, 계산된 주소로 직접 접근 가능하게 하는 기법이다. 이 때 계산에 사용된 함수를 hashing function(이하 해시 함수)이라고 한다. 이 방법을 통해 테이블 내의 특정 행은 그 행을 나타내는 어떤 유일한 값으로 변환할 수 있다. 변환된 값은 암호화되며, 반대로 암호화된 해시 값은 원래의 데이터로 되돌릴 수도 있다.

Hash table(이하 해시 테이블)은 해시 함수에 의해 계산된 함수 값(주소)를 저장하고 있는 표 형태의 데이터 구조이다. 일반적으로 bucket과 slot의 배열로 선언되어 있는데, 계산된 함수 값들은 각각의 bucket에 균등하게 나뉘어져 bucket내의 slot에 저장된다.

자 이제 Hash Match Join의 메카니즘에 대해 알아보자.

먼저, 조인에 참여하는 두 개의 테이블 중 상대적으로 크기가 작은 테이블을 해시 함수에 통과시켜 그 결과 값을 해시 테이블에 저장한다. 그런 다음, 큰 테이블을 한 행, 한 행, 해시 테이블에 매칭을 시켜가며 값을 찾아내 간다. 여기서 기억해야 할 중요한 사실 하나! 해시 테이블에는 실제 데이터가 아닌 해시 값이 저장되기 때문에, 작은 테이블이 해싱되어야 해시 테이블의 크기를 작게 유지하여 비교 속도가 빨라지게 된다. 따라서, 해시되는 테이블이 작으면 작을수록 속도가 빨라지게 된다.

Hash Match Join은 아주 작은 테이블과 아주 큰 테이블이 조인할 때 다른 조인들(Nested Loop Join, Merge Join)보다 월등한 성능을 발휘한다. 조인 컬럼에 대한 정렬이 필요없거나, 쓸만한 인덱스가 없을 경우에도 유용하다. Hash Match Join이 발생했다는 것은 다른 조인 방법이 마땅히 없다는 것을 의미한다. 따라서 실행 계획 내에 Hash Match Join 연산이 있다면 다음 사항을 유심히 살펴볼 필요가 있다.

인덱스가 없거나 인덱스가 잘못 작성되어 있는가?
쿼리에서 조건(WHERE)절을 빼먹지는 않았는가?
조건절에 변형이 일어나 인덱스를 사용하지 못하고 있지는 않은가?

만일, 조인이 되는 테이블에 적절한 인덱스를 추가하거나, 조건절을 제한하여 반환되는 데이터 양을 줄인다던지 혹은, 조건절의 변형을 제거하는 방법 등을 통해 다른 조인 방식을 유도해도 먹히지 않는다면, 쿼리 옵티마이저가 판단하기에 Hash Match Join이 가장 이상적인 조인 방법일지도 모른다.

이 예제에서 예상 행 수(282.216)와 실제 행 수(290) 는 약간의 차이를 보이고 있다. 이 차이가 크지 않다면 별 문제 없으나, 큰 차이를 보인다면 통계 정보가 오래되었다는 것을 의미하므로 UPDATE STATISTICS문이나 sp_updatestats 시스템 저장 프로시저를 이용해서 통계 정보를 갱신해 주어야 한다. 그렇지 않으면 예상 실행 계획과 실질 실행 계획가 달라지는 결과를 초래할 수도 있다.

이렇게 첫번째 Hash Match Join로 얻어진 결과는 [HumanResources].[Employee] 테이블에 대해 Clustered Index Scan한 결과와 다시 Hash Match Join을 하고 있다.

Clustered Index Seek

☞ 주인장의 다른 포스트도 있어요!!!

Hash Match Join 다음으로 [Person].[Contact] 테이블에 대해 Clustered Index Seek 연산을 수행하는 것이 보인다.

PK_Contact_ContactId 인덱스를 사용하고 있는데, 이 인덱스는 primary key이자 clustered index이다. 이 연산은 세 번째로 많은 비용을 사용하고 있다. 이 연산의 ToolTip은 다음과 같다.

[그림 4] Table Join - Clustered Index Seek 연산 ToolTip

여기서 Seek Predicates 섹션을 주목하길 바란다. [HumanResources].[Employee] 테이블의 [ContactID] 컬럼과 [Person].[Contact] 테이블을 직접 조인하고 있다.

Nested Loops Join

Clustered Index Seek 다음으로 Hash Match로 얻어진 결과와 Nested Loops Join을 통해 조인을 하고 있다.

[그림 5] Table Join - Nested Loops Join 연산 ToolTip

Nested Loops Join은 nested iteration이라 불리기도 한다. 이 연산은 두 개의 데이터 집합을 공급받아,

내측 집합(inner set)의 각각의 행에 대해 외측 집합(outer set)을 스캔해나가며 비교하는 조인 방식이다. 일반적으로 내외측 데이터 집합 모두 크기가 작을 경우 최적의 성능을 낸다. 데이터가 아주 크지 않은 이상 실행 계획에서 가장 많이 보이는 조인 방식이다.

Compute Scalar

☞ 주인장의 다른 포스트도 있어요!!!

이 쿼리의 실행 계획에서 마지막으로 설명할 내용은 Select 연산 바로 오른쪽에 있는 Compute Scalar 연산이다. 역시 ToolTip을 함께 보며 설명하도록 하겠다.

[그림 6] Table Join - Compute Scalar 연산 ToolTip

이는 단순히 계산으로 부터 얻어진 하나의 값, 즉 스칼라 값을 보여주고 있다.

예제에서는 [Contact.LastName] 컬럼과 [Contact.FirstName] 컬럼을 콤마(,)로 연결하여 [EmployeeName]이라는 별칭으로 표시하고 있다.

Merge Join

이번에 소개할 내용은 Hash Match Join, Nested Loops Join에 이어 마지막 조인 방법인 Merge Join 연산이다. Merge Join을 확인하기 위해 다른 쿼리를 작성해 보도록 하자.

SELECT c.CustomerID
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID = oh.SalesOrderID
JOIN Sales.Customer c
ON oh.CustomerID = c.CustomerID

이 쿼리의 실행 계획은 다음과 같다.

[그림 7] Table Join - Merge Join 연산

실행 계획에 따르면 쿼리 옵티마이저는 먼저, [Customer] 테이블에 대해 Clustered Index Scan 연산을, [SalesOrderHeader] 테이블에 대해서는 Non-clustered Index Scan 연산을 수행한다. WHERE 조건절에 아무 것도 정의하지 않았기 때문에 각각의 테이블에서 모든 행을 반환한다.

그런 다음, Merge Join 연산을 통해 반환된 모든 행을 조인한다. Merge 즉, 병합을 위해서는 조인된 양쪽 컬럼이 반드시 정렬되어 있어야 한다. 그렇기에 Merge Join을 Sort Merge Join 이라고 부르기도 한다. 하지만 이 예제에서는 선행 연산들에 의해 조인 컬럼들이 이미 정렬되어 있기 때문에 별도의 정렬 작업이 필요없다. Merge Join은 예제처럼 조인 컬럼들이 정렬이 되어 있을 경우 매우 빠르지만 정렬 작업이 필요한 경우에는 비용이 많이 드는 방법이 될 수 있다.

정렬 작업이 필요한 경우 쿼리 옵티마이저는 다음의 두 가지 옵션을 생각한다.

조인 컬럼을 정렬한 후 Merge Join을 한다.
별로 내키지는 않지만 Hash Match Join을 한다. Hash Match 연산을 하는 것이 최적이었다면 벌써 선택했겠지만...

최종적으로 쿼리 옵티마이저는 그 밖의 모든 경우의 수를 고려해서 둘 중 보다 적은 리소스를 사용하는 조인 방법을 선택한다.

[그림 7] Table Join - Merge Join 연산 ToolTip

요컨대, Merge Join의 성능의 핵심은 조인될 컬럼이 정렬이 되어 있느냐이다. 만일 그렇지 않다면 쿼리 옵티마이저는 Merge Join을 수행하기 전에 반드시 정렬을 해야만 하며, 정렬이 발생했다는 것은 Merge Join은 이미 이상적인 조인 방법이 아니라는 것을 의미한다. 이 경우 인덱스 설계를 재고할 필요가 있다.

다음 이야기 : 10. 그래픽 실행 계획의 기초 - WHERE절의 추가

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

11. 그래픽 실행 계획의 기초 - GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획 (0)	2011.07.17
10. 그래픽 실행 계획의 기초 - WHERE절의 추가 (0)	2011.07.17
8. 그래픽 실행 계획의 기초 - Single Table Query (0)	2011.07.17
7. 그래픽 실행 계획의 기초 - 그래픽 실행 계획의 언어 (0)	2011.07.17
6. 실행 계획 기초 - SQL Server Profiler로 실행 계획 캡처하기 (0)	2011.07.17

Single Table Query

하나의 테이블(Single table)를 질의했을 때 나타나는 아주 단순한 실행 계획들이다.

Clustered Index Scan

☞ 주인장의 다른 포스트도 있어요!!!

AdventureWorks 데이터베이스의 Person.Contact 테이블에 대해 아래의 쿼리를 실행해 보자

SELECT *
FROM Person.Contact

이 쿼리의 실행 계획은 아마 아래와 같이 나타날 것이다.

[그림 1] Clustered Index Scan의 예

필요한 데이터를 조회하기 위해 Clustered Index Scan 연산을 수행하였음을 확인할 수 있다. Clustered Index Scan 아이콘 위에 마우스를 갖다 대어 ToolTip 창을 확인해 보자. PK_Contact_ContactID라는 Clustered Index를 사용했으며 이 연산을 통해 얻어지는 예상 행 수는 19972행이다.

SQL Server의 인덱스는 B-tree 구조 인덱스이다. 혹자는 B-tree의 B가 Binary라고도 하지만 B-tree의 B는 Balanced 라는 것이 정설이다. Clustered Index도 다른 인덱스(Nonclustered Index)와 마찬가지로 인덱싱을 위해 인덱스의 키 구조를 저장한다. 하지만 Clustered Index는 Nonclustered Index와 달리 인덱스의 최하위 리프 레벨(leaf level)에 인덱스의 키 값 순으로 실제 데이터를 함께 저장한다. 따라서 Clustered Index는 테이블 당 하나만 만들 수 있다.

Clustered Index Scan은 쿼리에서 요청한 데이터를 조회하기 위해 인덱스 전 영역(혹은 대부분)을 스캔해 나가는 방식으로 Table Scan과 같은 컨셉 - Clustered Index의 최하위 리프 레벨(leaf level)에는 실제 데이터가 저장되어 있다는 것을 잊지 말자! - 이라고 생각해도 무방하다.

Index Scan은 주로 넓은 범위의 인덱스 영역을 조회할 때 발생한다. 이 경우 아래의 그림에서 처럼 인덱스의 키 값을 이용하여 하나 하나 랜덤 엑세스(Index Seek)를 하는 것보다 인덱스를 그냥 쭉 스캔(Index Scan)하는 것이 더 유리하다고 쿼리 옵티마이저는 판단한 것이다.

[그림 2] Index Seek과 Index Scan의 비교

Clustered Index Seek

☞ 주인장의 다른 포스트도 있어요!!!

앞서 실행한 쿼리에 다음과 같이 WHERE 절을 추가해 보자.

SELECT *
FROM Person.Contact
WHERE ContactID = 1

그리고 이 쿼리의 실행 계획을 보도록 하자

[그림 3] Clustered Index Seek의 예

Index Seek는 데이터를 조회하는 방식에 있어 Index Scan과는 명백하게 다르다. 앞서 설명하였다시피 Index Scan은 인덱스의 전 영역 혹은 대부분의 영역을 순차적으로 스캔해 나가며 필요한 데이터를 찾아내 가는 반면, Index Seek는 주어진 인덱스의 키 값을 근거로 하여 필요한 데이터만 콕!콕! 집어낸다. 이는 우리가 책에서 특정 단어를 찾을 때 색인을 이용하는 것과 같은 맥락이다. 따라서 Clustered Index Seek는 Clustered Index Scan보다 훨씬 더 적은 비용이 든다. 또한 인덱스의 최하위 리프레벨에 실제 데이터가 저장되어 있으므로, 인덱스을 검색한 후 별도로 데이터 페이지에 엑세스할 필요도 없다.

위의 예제에서는 Person.Contact 테이블에 대해 PK_Contact_ContactId라는 Clustered Index를 이용하여 Clustered Index Seek 연산을 통해 ContactId = 1인 것만 콕! 집어서 가져오고 있다. 그리고 중요한 사실 하나! Clustered Index Seek의 ToolTip 창을 확인해 보면 Ordered 속성이 True라는 것을 확인할 수 있을 것이다. 이는 쿼리 옵티마이저에 의해 데이터가 정렬되었음을 의미한다.

Nonclustered Index Seek

이번에는 Nonclustered Index를 사용하기 위해 조금 다른 쿼리를 작성해 보도록 하자.

SELECT ContactID
FROM Person.Contact
WHERE EmailAddress LIKE 'sab%'

이번에는 IX_Contact_EmailAddress 라는 Nonclustered Index를 사용하였다. 여담이지만 Nonclustered Index Seek의 ToolTip 창에는 Index Seek라고 표시되어 있다. 마이크로소프트의 실수인듯 하다.

[그림 4] Nonclustered Index Seek의 예

Nonclustered Index Seek는 Clustered Index Seek와 마찬가지로 인덱스의 키 값을 이용하여 찾고자 하는 행에 직접 엑세스한다. 용어 자체의 의미에서도 알 수 있듯이 Clustered Index를 사용하는 대신 Nonclustered Index를 사용한다는 점을 제외하고는 Clustered Index Seek와 메카니즘은 같다. Clustered Index를 사용할지, 혹은 Nonclustered Index를 사용할지는 요청한 쿼리와 인덱스의 구조에 따라 쿼리 옵티마이저가 알아서 결정한다. 뒤에서 다시 설명하겠지만, Non-lustered Index를 사용하면 추가적인 I/O가 발생하는 경우도 있으므로 일반적으로 Clustered Index가 훨씬 효율적이다.

Key LookUp

앞에서 작성한 쿼리에 몇 개의 컬럼을 더 조회하도록 쿼리를 수정해 보겠다.

SELECT ContactID,
LastName,
Phone
FROM Person.Contact
WHERE EmailAddress LIKE 'sab%'

실행 계획은 다음과 같다.

[그림 5] Key LookUp의 예

드디어 우리는 단일 연산이 아닌 복합 연산 실행 계획을 만나게 되었다. 실행 계획을 읽는 순서는 이미 설명하였다. "오른쪽에서 왼쪽으로" 그리고 "위에서 아래로". 첫 번째 연산은 IX_Contact_EmailAddress 인덱스를 이용한 Index Seek 연산이다. 이 인덱스는 Non-unique, Nonclustered 인덱스로써 EmailAddress 컬럼을 포함하고 있다. 이 예제의 경우 IX_Contact_EmailAddress 인덱스는 Covering Index가 아니다.

그럼 Covering Index란 무엇인가? Covering Index란 조회하고자 하는 컬럼이 모두 인덱스 내에 포함되어 있는 Nonclustered Index를 의미한다. 따라서 이 쿼리의 경우 IX_Contact_EmailAddress 인덱스를 읽어야 할 뿐만 아니라, IX_Contact_EmailAddress 인덱스에 없는 다른 데이터를 조회하기 위해 Clustered Index인 PK_Contact_ContactID도 함께 읽어야만 한다.

Index Seek의 ToolTip를 보도록 하자. Output List에 EmailAddress와 ContactID 컬럼이 있음을 확인할 수 있다.

[그림 6] Key LookUp - Index Seek의 ToolTip

sp_helpindex로 Person.Contact 테이블의 인덱스를 확인해 보면, IX_Contact_EmailAddress 인덱스의 인덱스 키에는 EmailAddress 컬럼 밖에 없다. 하지만 이 연산의 Output List에는 ContactID 컬럼도 포함되어 있다. 왜일까?

간단히 설명하자면, Clustered Index가 있는 테이블에 Nonclustered Index를 작성하게 되면 Nonclustered Index의 리프레벨은 Clustered Index의 키 값(ContactID)을 가리키기 때문 - 혹은, Nonclustered Index의 리프레벨에 Clustered Index의 키 값(ContactID)을 포함시키기 때문 - 이다.

이 키 값은 PK_Contact_ContactID Clustered Index를 Key Lookup하는데 사용된다. 아래의 그림에서 확인할 수 있듯이 Seek Predicates에서 Index Seek를 통해 공급받은 키인 Scalar Operator([AdventureWorks].[Person].[Contact].[ContactID])로 해당되는 행을 찾는다. Output List는 LastName과 Phone 컬럼이다.

[그림 7] Key LookUp - Key Lookup의 ToolTip

Key LookUp은 Clustered Index가 있는 테이블에서의 Bookmark Lookup(책갈피 조회)이다.

Key LookUp은 쿼리 옵티마이저가 단일 연산으로 행을 조회할 수 없어서, Clustered Key를 사용하여 Clustered Index에서 해당하는 데이터를 가져오는 과정을 의미한다. 실행 계획 내에 Key LookUp 연산이 있다는 것은 Covering Index 또는 Included Index로 성능을 개선할 여지가 있음을 나타낸다. Covering Index 및 Included Index는 쿼리에서 필요한 모든 컬럼을 포함하고 있기 때문에 Key LookUp 연산이 별도로 필요하지 않는다.

Key LookUp은 두 개의 연산으로 부터 얻어진 결과를 병합하기 위해 항상 Nested Loop Join 연산을 동반한다.

[그림 8] Key LookUp - Nested Loops 의 ToolTip

Nested Loops Join은 조인의 가~~~장 기본적인 형태이다. 예제의 경우 Key LookUp 연산이 필요하기 때문에, Nested Loops 연산을 통해 Index Seek와 Key LookUp 연산의 결과를 결합하고 있다. 만일 여기서 Key LookUp 연산이 필요없다면, 당연히 Nested Loops 연산 또한 더 이상 필요없게 된다.

Table Scan

이 연산자는 MS SQL Server 실행 계획 깨부수기를 진행하는 동안 수도 없이 보아 왔다. 따라서 여기서는 복습의 의미로 간단히 살펴보기로만 하겠다.

Table Scan 연산자는 테이블 - Heap, 혹은 Clustered Index가 없는 테이블이라는 표현이 더 명확하겠다. - 을 쭉 읽어나가며 필요한 행을 조회하는 연산이다.

SELECT *
FROM [dbo].[DatabaseLog]

[그림 9] Table Scan

Table Scan은 다음과 같은 몇 가지 요인으로 인해 발생한다.

테이블에 인덱스가 없거나 쓸만한 인덱스가 없는 경우
위 예제처럼 테이블의 모든 행을 조회하는 경우
상식적으로 생각해 봐도 전체 행을 조회하기 위해 인덱스를 사용한다는 것은 비효율적임이 틀림없다. 인덱스를 사용한다는 것은 결국 Bookmark LookUp이라는 추가적인 연산을 동반하기 때문이다.
쿼리 옵티마이저가 인덱스를 사용하는 것보다 Table Scan을 하는 것이 효율적이라고 판단하는 경우
예를들어, 테이블에 유용한 인덱스가 있긴 하지만, 1페이지 내에 모든 데이터가 들어갈 정도로 테이블이 작을 경우, 쿼리 옵티마이저는 Index Scan보다는 Table Scan을 선택할 수도 있다.

RID LookUp

☞ 주인장의 다른 포스트도 있어요!!!

앞서 작성한 쿼리에서, Primary Key를 이용하여 [dbo].[DatabaseLog] 테이블을 조회하도록 아래와 같이 쿼리를 조금 수정해 보았다.

Index Seek와 RID LookUp 연산에서 얻어진 결과를 Nested Loops 연산으로 결합하는 실행 계획 결과를 얻을 수 있을 것이다.

SELECT *
FROM [dbo].[DatabaseLog]
WHERE [DatabaseLogID] = 1

[그림 10] RID LookUp

결과를 얻기 위해, 쿼리 옵티마이저는 먼저 Primary Key로 설정된 PK_DatabaseLog_DatabaseLogID 인덱스에 대해 Index Seek 연산을 수행한다. 이 인덱스에서 WHERE 절에서 요구한 행을 콕! 집어서 찾을 수 있기 때문에 PK_DatabaseLog_DatabaseLogID는 아주 똑똑한 인덱스이다. 하지만 이 인덱스에서 우리가 필요한 컬럼을 모두 얻을 수는 없다. 이 사실은 다음과 같이 확인할 수도 있다.

[그림 11] RID LookUp - Index Seek

Index Seek의 ToolTip을 열어보면, Output List에서 Bmk1000 라는 부분을 찾을 수 있을 것이다. 쿼리 옵티마이저는 이 Bmk1000이라는 표식을 통해 "이 Index Seek 연산은 실제로는 Bookmark LookUp 연산을 가진 실행 계획의 일부분"이라는 것을 우리에게 알려준다.

이어서 쿼리 옵티마이저는 RID LookUp 연산을 수행한다. RID LookUp 연산은 Clustered Index가 없는 Heap table에서 발생하는 책갈피 조회(Bookmark LookUp)의 일종으로써 Row Identifier(RID)를 이용하여 행을 조회한다. 바꾸어 말하면, Clustered Index가 없는 테이블에서는 RID로 인덱스와 힙을 연결한다는 것을 의미한다. 따라서, RID LookUp 연산에서도 Key LookUp 연산과 마찬가지로 추가적인 디스크 I/O가 발생한다.

[그림 12] RID LookUp - RID LookUp

요컨데 Key LookUp 연산은 Clustered Index가 있는 테이블에서의 Bookmark LookUp이고, RID LookUp 연산은 Clustered Index가 없는 Heap에서의 Bookmark LookUp이라고 생각하면 간단할 것이다.

다음 이야기 : 9. 그래픽 실행 계획의 기초 - Table Joins

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

10. 그래픽 실행 계획의 기초 - WHERE절의 추가 (0)	2011.07.17
9. 그래픽 실행 계획의 기초 - Table Joins (0)	2011.07.17
7. 그래픽 실행 계획의 기초 - 그래픽 실행 계획의 언어 (0)	2011.07.17
6. 실행 계획 기초 - SQL Server Profiler로 실행 계획 캡처하기 (0)	2011.07.17
5. 실행 계획 기초 - XML 실행 계획 사용하기 (0)	2011.07.17

그래픽 실행 계획(Graphical Execution Plan)의 기초

이번 포스트에서는 아주 단순한 SELECT, UPDATE, INSERT, DELETE문과 몇 가지 조인을 통해 아래의 토픽을 진행할 것이다.

Operators(연산자)
Join(조인)
Where 절
Aggregates(집계)
Insert, Update, Delete문의 실행 계획

그래픽 실행 계획의 언어

그래픽 실행 계획을 배우는 것은 마치 아이콘 기반의 새로운 언어를 배우는 것과 같다. 각각의 아이콘은 실행 계획 내의 특정 연산자를 의미하는데, MS SQL Server 2005에는 이러한 실행 계획 연산자가 총 79개가 있다. 그렇다고 이것들을 다 외울 필요는 없다. 왜냐하면 대부분의 쿼리가 이들 중 극히 일부분만 사용하기 때문이다. 따라서 우리는 앞으로 자주 사용되는 실행 계획 연산자를 중심으로 살펴볼 것이다. 혹시 여기서 다루는 내용 이외의 것에 대해 좀 더 알고 싶으면 주인장의 다른 포스트인 [MS SQL Server]실행 계획 연산자 리스트를 참고하길 바란다.

그래픽 실행 계획 연산자는 다음 4가지 타입이 있다.

논리 및 물리 연산자(Logical and Physical Operators) : iterator라고 불리기도 한다. 파란색 아이콘으로 표시되며, 쿼리의 실행 또는 DML문을 나타낸다.
커서 논리 및 물리 실행 계획 연산자(Cursor Logical and Physical Showplan Operators) : 노란색 아이콘으로 표시되며, T-SQL문의 커서 연산을 나타낸다.
병렬 실행 계획 연산자(Parallelism Showplan Operator) : 논리 및 물리 연산자와 마찬가지로 파란색 아이콘으로 표시되며, 병렬 연산을 나타낸다.
언어요소(Language element) : 녹색 아이콘으로 표시되며, Assign, Declare, If, Select(Result), While 등의 T-SQL 언어요소를 나타낸다.

이 내용 역시 [MS SQL Server]실행 계획 연산자 리스트에 일목요연하게 정리해 두었으니 병행하여 참고하길 바란다. 이번 포스트에서는 위 4가지 타입 중 병렬 실행 계획 연산자를 포함한 아래의 몇 가지 논리 및 물리 연산자들을 살펴볼 것이다. 참고로 아래에 나열된 순서는 이야기의 진행 순서와 무관하다.

Select(Result)
Sort
Clustered Index Seek
Clustered Index Scan
Non-clustered Index Scan
Non-clustered Index Seek
Table Scan
RID Lookup
Key Lookup
Hash Match
Nested Loops
Merge Join
Top
Compute Scalar
Constant Scan
Filter
Eager Spool

그럼 다음 포스트로 이동하여 하나씩 살펴보기로 하자.

다음 이야기 : 8. 그래픽 실행 계획의 기초 - Single Table Query

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

9. 그래픽 실행 계획의 기초 - Table Joins (0)	2011.07.17
8. 그래픽 실행 계획의 기초 - Single Table Query (0)	2011.07.17
6. 실행 계획 기초 - SQL Server Profiler로 실행 계획 캡처하기 (0)	2011.07.17
5. 실행 계획 기초 - XML 실행 계획 사용하기 (0)	2011.07.17
4. 실행 계획 기초 - 텍스트 실행 계획 사용하기 (0)	2011.07.17

SQL Server Profiler로 실행 계획 캡처하기

지금까지 우리는 실행 계획을 캡처하는 몇 가지 방법들을 살펴보았다. 이 방법들은 모두 실행 계획 캡처 활성화 > 쿼리 실행 > 실행 계획 캡처 비활성화 의 순으로 실행 계획을 캡처할 수 있도록 하고 있다.

하지만, 이미 서비스되고 있는 서버나 테스트 서버에서 문제해결(troubleshooting)를 하는 경우라면 이야기는 달라진다. 서버에는 수십 개에서 수 백 개의 세션이 물려있을 것이고, 다양한 파라미터와 다양한 실행 계획을 가진 수많은 쿼리가 처리되고 있기 때문에, 각각의 쿼리들에 대해 실행 계획 캡처 활성화 > 쿼리 실행 > 실행 계획 비활성화 한다는 것은 무모하다고 하지 않을 수 없다. 이 경우에는 자동으로 실행 계획을 캡처해야 할 필요가 있는데, SQL Server 2005 이상에서는 SQL Server Profiler를 이용해서 실행되고 있는 쿼리의 XML 실행 계획을 자동으로 캡처할 수 있는 기능을 제공하고 있다. 이렇게 수집된 실행 계획을 바탕으로 고비용의 쿼리에는 어떤 것들이 있는지, 혹은 단순히 Table scan을 하는 쿼리는 어떤 것이 있는지 등 여러가지 조사를 할 수 있다.

SQL Server Profiler는 SQL Server내에서 발생되고 있는 T-SQL이나 저장 프로시저와 같은 여러가지 이벤트에 대해 데이터를 수집할 수 있는 강력한 도구이다. Profiler는 GUI 인터페이스를 통해 수동으로 이벤트를 추적(이하 trace 혹은 추적)하거나, T-SQL이나 GUI 인터페이스를 통해 추적 내용을 정의하고 특정 시간이나 기간 내에 자동으로 수집하도록 할 수도 있다.

뿐만 아니라 이렇게 수집된 추적은 화면 창에 보여주거나 파일이나 테이블에 떨어뜨릴 수 있다. Profiler에 대한 내용은 본 포스트의 주제에서 벗어나는 내용이므로, BOL의 SQL Server 프로파일러 참조 를 참조하기 바란다.

실행 계획 이벤트

추적에 포함할 수 있는 실행 계획 이벤트에는 다음과 같은 것들이 있다.

Showplan Text : 실행되는 쿼리 각각에 대해 이벤트를 추적하여 SHOWPLAN_TEXT T-SQL문과 같은 방법으로 예상 실행 계획을 생성한다. Showplan XML에서 얻을 수 있는 정보의 일부분만 보여지므로 상대적으로 얻을 수 있는 정보가 빈약하다. 텍스트 실행 계획의 단점에 대해서는 이미 살펴본 바 있으니 참고하기 바라며, 이 이벤트는 다음 버전에서 제거될 예정이다.
Showplan Text (unencoded) : 바이너리가 아닌 스트링으로 정보를 보여주는 것 이외에는 Showplan Text와 같다. 이 이벤트 역시 다음 버전에서 제거될 예정이다.
Showplan All : 실행되는 쿼리 각각에 대해 이벤트를 추적하여 SHOWPLAN_ALL T-SQL문과 같은 방법으로 예상 실행 계획을 생성한다. Showplan Text와 같은 단점을 가지고 있다. 이 이벤트 역시 앞으로 제거될 예정이다.
Showplan All for Query Compile : Showplan All 이벤트와 같은 데이터를 생성한다. 그러나 쿼리가 컴파일될 때만 이벤트가 발생한다. 이 이벤트도 앞으로 제거될 예정이다.
Showplan Statistics Profile : STATISTICS PROFILE T-SQL문과 같은 방법으로 실질 실행 계획을 생성한다. 이것 역시 텍스트 결과의 단점을 가지고 있으며, STATISTICS XML문이나 Showplan XML Statistics Profile 이벤트에서 얻을 수 있는 데이터의 일부만 포함하고 있다. 이 이벤트도 앞으로 제거될 예정이다.
Showplan XML : 실행되는 쿼리 각각에 대해 이벤트를 추적하여 SHOWPLAN_XML과 같은 방법으로 예상 실행 계획을 생성한다.
Showplan XML For Query Compile : 컴파일되는 쿼리만 대상으로 한다는 것 이외에는 Showplan XML과 같다.
Performance Statistics : Showplan XML For Query Compile과 유사하나, 이 이벤트는 실행 계획 뿐만 아니라 말 그대로 쿼리의 성능 메트릭을 함께 캡처한다는 점이 다르다. 이 이벤트는 실행 계획이 처음으로 캐시되거나, 처음으로 컴파일 혹은 재컴파일되는 경우, 또는 캐시에서 제거될 경우 발생한다.
Showplan XML Statistics Profile : 이 이벤트는 각각의 쿼리에 대해 실질 실행 계획을 생성한다.

당연한 이야기이겠지만 Showplan XML 또는 Showplan XML Statistics Profile을 사용하여 모든 실행 계획을 캡처하게 되면, 서버에 상당한 부하를 주게 되므로 서비스 중인 서버에서는 특히 주의해야 한다.

Showplan XML Trace

SQL Server Profiler의 Showplan XML 이벤트는 쿼리를 실행할 때 쿼리 옵티마이저에 의해 사용된 XML 실행 계획을 캡처한다. SQL Server Profiler로 XML 실행 계획을 캡처하기 위해서는 다음과 같은 절차를 따른다.

SQL Server Profiler에 접근하는 방법이 여러가지 있겠지만 여기서는, 시작(Start) 메뉴 > 모든 프로그램(All Programs) > MS SQL Server 2005(혹은 2008) > 성능도구(Performance Tools) > SQL Server 프로파일러(SQL Server Profiler) 를 클릭하여 SQL Server Profiler 를 기동한다.
파일(File) 메뉴 > 새 추적(New Trace) 를 선택한 후, 추적하고자 하는 데이터베이스에 연결한다.
프로파일러 속성(Profiler Properties) 대화 상자에서 이벤트 선택(Event Selection) 탭으로 이동하여, 모든 이벤트 보기(Show all events) 체크 박스에 체크한다.
Showplan XML 이벤트는 성능(Perpormance) 섹션에 있다. 성능(Perpormance) 섹션에서 플러스(+) 사인을 클릭하면 해당 섹션이 확장되고 우리가 추적하고자 하는 Showplan XML 이벤트가 보일 것이다. 이 섹션에는 이 밖에도 RPC:Completed, SQL:BatchStarting, SQL:BatchCompleted 와 같은 이벤트도 있는데 Showplan XML 이벤트와 함께 추적하면 더욱 유용하다. 예를 들어, 해당 이벤트 바로 전후에 어떤 일들이 일어났는지 확인할 수 있다면 많은 도움이 될 것이다.

[그림 1] 추적 속성 대화 상자 - 이벤트 선택 탭
Showplan XML을 선택하거나, 혹은 다른 XML 이벤트를 선택하면 이벤트 추출 설정(Events Extraction Settings) 이라는 세번째 탭에 나타날 것이다. 이 탭에서는 추적한 XML 실행 계획 이벤트를 별도의 파일로 저장할 수 있다. 또한 모든 XML 실행 계획을 단일 파일에 저장하거나, 배치별로 개별 파일로 저장할 수도 있다.

[그림 2] 추적 속성 대화 상자 - 이벤트 추출 설정 탭
실행(Run) 버튼을 클릭하면 아래의 예제 그림처럼 추적이 시작된다.

[그림 3] 추적 실행 예

Showplan XML 이벤트를 클릭하면 TextData 컬럼에 XML 실행 계획 코드가 나타나고, 아래의 두번째 창에 그래픽 실행 계획이 나타남을 확인할 수 있을 것이다. 이 때 마우스 오른쪽 버튼을 클릭해서 나타나는 메뉴인 이벤트 데이터 추출(Extract Event Data) 를 선택함으로써 Showplan XML 이벤트를 별도의 파일로 저장할 수 있다.

[그림 4] 이벤트 데이터 추출

대화상자가 나타나면 XML 코드를 저장하고자 하는 경로와 파일 명을 입력하고 저장한다. 전형적인 XML확장자로 저장하는 대신에 .sqlplan 확장자로 저장해도 된다. .sqlplan 확장자로 저장한 경우, 탐색기 내에서 저장된 파일을 더블 클릭하면 SSMS에 그래픽 실행 계획으로 보여진다.

요약

우리는 지금까지 실행 계획 기초 시리즈를 통해 먼저, 쿼리가 실행될 때 어떤 일이 발생하는지 SQL Server DBMS Engine의 메카니즘을 살펴보았고, 또 이런 과정이 예상 실행 계획과 실질 실행 계획에서 어떻게 표현되는지도 살펴보았다. 또한 텍스트 실행 계획, 그래픽 실행 계획, 그리고 XML 실행 계획의 각각의 특성과 장단점 및 실행 계획을 취득하는 방법도 함께 살펴보았다. 그래픽 실행 계획이든 XML 실행 계획이든 우리가 취득하고자 하는 실행 계획 데이터는 모두 취득할 수 있다. 하지만 어떤 방식을 사용하고 어떻게 활용할지는 여러분의 몫이다.

다음 이야기 - 7. 그래픽 실행 계획의 기초 - 그래픽 실행 계획의 언어

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

8. 그래픽 실행 계획의 기초 - Single Table Query (0)	2011.07.17
7. 그래픽 실행 계획의 기초 - 그래픽 실행 계획의 언어 (0)	2011.07.17
5. 실행 계획 기초 - XML 실행 계획 사용하기 (0)	2011.07.17
4. 실행 계획 기초 - 텍스트 실행 계획 사용하기 (0)	2011.07.17
3. 실행 계획 기초 - 그래픽 실행 계획 사용하기 (0)	2011.07.17

XML 실행 계획 사용하기

XML 실행 계획은 SQL Server 2005 부터 새롭게 제공되고 있는 실행 계획 표시 방법이다.

예상 실행 계획과 실질 실행 계획 표시하기

XML 형식의 예상 실행 계획 보기를 활성화 또는 비활성화하기 위해서는 아래의 옵션을 사용한다.

SET SHOWPLAN_XML ON;

...

SET SHOWPLAN_XML OFF

SHOWPLAN_XML 명령어는 SHOWPLAN_ALL과 마찬가지로 T-SQL문을 실행하지 않는다. 단지 실행 계획을 XML 문서형식으로 보여주는 것 이외에는 기본적으로 SHOWPLAN_ALL과 같다고 생각해도 무방하다. 그리고, 재차 강조하는 내용이지만 실행 계획을 수집했으면 이 옵션을 OFF하는 것을 잊지 말아야 할 것이다.

XML 형식의 실질 실행 계획 보기를 활성화 또는 비활성화하기 위해서는 아래의 옵션을 사용한다.

SET STATISTICS XML ON;
...
SET STATISTICS XML OFF;

XML 실행 계획 해석하기

마찬가지로, 이해를 돕기 위해 실행 계획 기초(4) 텍스트 실행 계획 사용하기 에서 사용한 쿼리를 다시 실행해 보도록 하겠다.

SET SHOWPLAN_XML ON;

GO

SELECT *
FROM [dbo].[DatabaseLog];

실행 결과는 아래와 같은 모습으로 결과창에 표시될 것이다.

참고로 결과는 텍스트 형식이나 표 형식으로 표시하거나, 파일로도 저장할 수 있다. 결과 형식을 바꾸기 위해서는 메뉴 > 쿼리(Query) > 결과 표시(Result To) 에서 원하는 결과 표시 형식을 선택하면 된다. SQL 편집기 도구 모음에서 해당 아이콘을 클릭해도 된다. 혹은, 단축키 CTRL + T(결과를 텍스트로 표시), CTRL + D(결과를 표 형식으로 표시), CTRL + SHIFT + F(결과를 파일로 출력)를 사용해도 된다.

[그림 1] XML 실행 계획 - SHOWPLAN_XML

결과 창의 하이퍼링크는 아래의 경로에 위치한 XML 스키마 정의 파일을 가리킨다.

\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2003\03\showplan\showplanxml.xsd

자! 하이퍼링크를 클릭해 보자. SQL Server Management Studio(이하 SSMS)의 브라우저 창에 XML 형식의 실행 계획이 표시될 것이다. 우리가 질의한 T-SQL문은 아주 단순하지만 상당히 많은 양의 정보가 보여짐을 느낄 수 있을 것이다. 참고로, XML의 각각의 element(요소)와 attribute(속성)의 풀셋은 아래에서도 확인 가능하다.

http://schemas.microsoft.com/sqlserver/2004/07/showplan/.

실행 계획의 내용을 위에서 부터 차근차근 살펴보도록 하자.

가장 먼저 보이는 것은 BatchSequence, Batch 그리고, Statement element이다. 예제의 경우 single Batch, single Statement이므로 이 element는 반복해서 나타나지 않고 한 번만 나타났다. 다음으로 보여지는 것은 StmtSimple element이다. 이 element에서는 우리가 질의한 쿼리를 볼 수 있으며, statement의 속성들과 Query Plan의 몇가지 물리적인 속성도 함께 확인할 수 있다.

<BatchSequence>
    <Batch>
        <Statements>
            <StmtSimple StatementText="
 

SELECT *
FROM [dbo].[DatabaseLog];


" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.108154"

StatementEstRows="389" StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false"

ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="9">

쿼리 옵티마이저는 예상대로 trivial plan을 선택했다. 이는 StatementOptmLevel 속성에서 확인할 수 있다. CachedPlanSize와 같은 속성 정보는 아주 유용하다. 예를 들어 쿼리가 한 페이지를 초과하여 LeaveBehind 메모리 영역으로 보내어 졌는지 측정하는데 큰 도움이 된다.

다음으로 보이는 것은 RelOp element이다. 여기서는 해당 구문을 위해 어떤 물리적 연산(PhysicalOp)과 논리적 연산(LogicalOp)을 할 것인지, 그리고 예상 행 수(EstimateRows), 예상 CPU(EstimateCPU) 처럼 이미 우리가 살펴 본 친숙한 내용들을 확인할 수 있다.

<RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="389"

EstimateIO="0.107569" EstimateCPU="0.0005849" AvgRowSize="8569"

EstimatedTotalSubtreeCost="0.108154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

XML 실행 계획은 다른 실행 계획 보기들보다 더 많은 정보를 제공할 뿐만 아니라 가독성도 좋다. 예를 들어, OutputList element의 리스트 정보의 ColumnReference element경우 다른 실행 계획 보기들 보다 훨씬 보기가 편하다는 것을 느낄 수 있을 것이다.

<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="DatabaseLogID" />
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="PostTime" />
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="DatabaseUser" />
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="Event" />
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="Schema" />
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="Object" />
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="TSQL" />
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

Column="XmlEvent" />
</OutputList>

그 밑으로 TableScan element가 있다. 이 element의 속성에 대해서는 이미 앞에서 살펴본 바가 있으므로 설명을 생략하겠다.

이어지는 리스트들은 컬럼의 레이아웃을 정의한 값들로 연산에 의해 참조된다.

                              <DefinedValues>
                                  <DefinedValue>
                                       <ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

                                         Column="DatabaseLogID" />
                                  </DefinedValue>
..................< output cropped > ...............................
                                  <DefinedValue>

<ColumnReference Database="[AdventureWorks]" Schema="[dbo]" Table="[DatabaseLog]"

                                         Column="XmlEvent" />
                                  </DefinedValue>
                             </DefinedValues>

..................< output cropped > ...............................

XML 실행 계획을 그래픽 실행 계획으로 저장하기

결과 창에서 실행 계획을 포함하는 Microsoft SQL Server XML Showplan을 마우스 오른쪽 버튼으로 클릭한 다음 다른 이름으로 결과 저장(Save As) 을 클릭한다. 그런 다음 <표 형태 또는 텍스트> 결과 저장 대화 상자의 파일 형식에서 모든 파일(*.*)을 클릭한다. 그리고 파일 이름 입력란에 <이름>.sqlplan 형식으로 이름을 입력하고 저장 버튼을 클릭하여 파일을 저장한다. 이렇게 저장된 실행 계획은 파일 열기로 그래픽 실행 계획 형태로 볼 수 있다.

이 방법은 상당히 유용하다. 예를 들어 XML 형식으로 몇 개의 실행 계획을 작성하고 각각 파일로 저장한 다음 이 파일을 열어 그래픽 형식으로 비교 분석할 수 있다. 또는 문제가 되는 쿼리라든지 성능이 나쁜 쿼리을 이 방법으로 저장해 두었다가, 개발자라면 DBA에게 해당 파일을 공유하여 조언을 구할 수도 있고, DBA라면 개발자에게 파일을 제시하여 쿼리 수정을 권고할 수 있을 것이다.

만일, XML 형식으로 XML 실행 계획을 저장하고자 한다면, 먼저 결과를 XML 창에 표시한 후 CTRL + S 단축키를 눌러 XML로 파일을 저장하면 된다. 만일 결과 창에서 XML를 직접 저장하면 단지 결과 창에 무엇이 표시되었는지만 저장된다.

다음 이야기 : 6. 실행 계획 기초 - SQL Server Profiler로 실행 계획 캡처하기

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

7. 그래픽 실행 계획의 기초 - 그래픽 실행 계획의 언어 (0)	2011.07.17
6. 실행 계획 기초 - SQL Server Profiler로 실행 계획 캡처하기 (0)	2011.07.17
4. 실행 계획 기초 - 텍스트 실행 계획 사용하기 (0)	2011.07.17
3. 실행 계획 기초 - 그래픽 실행 계획 사용하기 (0)	2011.07.17
2. 실행 계획 기초 - 실행 계획 깨부수기를 함께하기 위해 먼저 해야 할 일 (0)	2011.07.17

본 포스트의 내용은 MS SQL Server2005 이상에 국한된 내용입니다.

퍼가실 때는 덧글을 달아주시는 센스를...

텍스트 실행 계획 사용하기

그래픽 실행 계획은 가독성이 좋기 때문에 매우 유용하다. 그러나 실행 계획에 대한 많은 정보를 한 번에 볼 수 없다는 단점이 있다. 어떤 정보는 ToolTip에서만 보여지고, 또 어떤 정보는 그래픽 실행 계획의 노드 속성에서만 확인 할 수 있다. 만일 이 모든 정보를 한 번에 볼 수 있는 방법이 있으면 얼마나 좋을까?

놀랄만큼 복잡한 실행 계획을 가진 쿼리라든지 수십 개의 쿼리를 내포하고 있는 배치의 경우, "Table scan을 하고 있지는 않을까?" 혹은 "가장 비용을 많이 차지하고 있는 쿼리는 무엇일까?" 등 찾아 내기는 그리 쉽지만은 않을 것이다.

이에 그 대안으로 텍스트 실행 계획과 XML 실행 계획을 소개하고자 한다.

이 중에서 텍스트 실행 계획을 먼저 살펴보겠다.

※ 마이크로소프트는 2008 이후 버전부터 텍스트 실행 계획을 기능에서 제거할 예정이다. 따라서 여기서는 그리 비중있게 다루지는 않겠다.

예상 실행 계획 표시하기

텍스트 형식으로 예상 실행 계획을 표시하는 방법은 아주 간단하다. 그냥 실행할 쿼리 앞에 아래의 SET문을 실행하기만 하면 된다.

참고로 BOL에서는 이런류의 SET문을 실행 계획 SET 옵션이라고 부르고 있다.

SET SHOWPLAN_ALL ON;

여기서 한 가지 중요한 사실을 기억하기 바란다. 한 번 SHOWPLAN_ALL 옵션을 ON으로 설정하면, 이하 실행되는 모든 T-SQL문의 실행 정보를 취득할 수 할 수 있다. 하지만, 이 때 '실제로 구문이 실행되는 것은 아니다'. 따라서, 필요한 실행 계획 정보를 취득한 다음에는 반드시 이 옵션을 OFF 해줘야 한다. SHOWPLAN_ALL 옵션을 ON 으로 설정했다는 것을 잊어버렸다가는 큰 낭패를 당할 것이다.

"난 분명히 테이블을 작성했는데 테이블이 없다" 라든지, "중요한 고객 정보가 갱신되지 않았습니다" 라든지 말이다.

SHOWPLAN_ALL 옵션을 끄는 방법

SET SHOWPLAN_ALL OFF;

SHOWPLAN_ALL 이외에 SHOWPLAN_TEXT라는 옵션도 있다. 이는 osql.exe와 같은 툴에서 간단하게 실행 계획 정보를 조회할 때 유용하다. 이 또한 SHOWPLAN_ALL 옵션과 마찬가지로 실제로 구문이 실행되지 않으므로 주의하기 바란다.

실질 실행 계획 표시하기

텍스트 형식으로 실질 실행 계획을 취득하기 하기 위해서는 아래의 SET문을 실행하면 된다.

SET STATISTICS PROFILE ON;

반대로 비활성화하기 위해서는 아래의 SET문을 실행하면 된다.

SET STATISTICS PROFILE OFF;

텍스트 실행 계획 해석하기

이해를 돕기 위해 실행 계획 기초(3) 그래픽 실행 계획 사용하기 에서 사용했던 T-SQL문을 아래와 같이 실행해보자 .

SET SHOWPLAN_ALL ON;

GO

SELECT *
FROM [dbo].[DatabaseLog];

위 구문을 실행하면 결과창에 아래와 같이 예상 실행 계획이 표시될 것이다.

오라클 유저라면 마치 오라클의 그것과 모습이 유사하다는 것을 느낄 수 있을 것이다.

[그림 1] 텍스트 실행 계획 - SHOWPLAN_ALL

텍스트 실행 계획은 그래픽 실행 계획을 옆으로 뉘인 형태라고 생각하면 된다. 첫 번째 행에는 우리가 실행한 구문이 보여지고 있다. 그리고 그 밑으로 어떠한 과정으로 해당 구문을 실행할지 Physical Opreator(이하 물리 연산자) 들이 표시된다. 여기서는 Table Scan을 하고 있다.

앞으로 이야기를 진행하면서 우리는 좀 더 복잡한 실행 계획을 다루게 될 것인데, 앞에서 언급했듯이 텍스트 실행 계획이 그래픽 실행 계획에 비해 가독성이 떨어진다는 사실을 점차 느낄 수 있을 것이다. 하지만 오라클 유저라면 어쩌면 텍스트 실행 계획이 더 편할 수도...

각설하고, 텍스트 실행 계획을 읽는 방법은 그래픽 실행 계획을 읽는 방법과 조금 다르다. 그래픽 실행 계획을 읽는 순서는 "오른쪽에서 왼쪽으로, 위에서 아래로" 이지만, 텍스트 실행 계획은 "안쪽에서 바깥쪽으로, 위에서 아래로" 읽어야 한다. 각 노드의 부모 자식 관계를 연결하는 파이프라인 ( | )을 이용하면 읽기에 용이할 것이다.

첫 번째 컬럼에는 앞서 실행 계획 기초(3) 그래픽 실행 계획 사용하기 에서 살펴보았던 ToolTip이나 그래픽 실행 계획 노드 속성 창의 내용이 표시된다. 아마 여러분이 확인하고자 하는 대부분의 내용이 이 컬럼에 나타날 것이다. 그래픽 실행 계획에서 NodeId는 해당 노드의 고유한 속성이기 때문에 유용하지만, 해당 노드의 부모노드(Parent)의 정보는 화살표의 방향으로도 확인할 수 있으므로 표시되지 않았다. 하지만, SHOWPLAN_ALL에서는 Parent NodeId 정보도 확인할 수 있다. 결과 창을 오른쪽으로 스크롤하면 TotalSubTreeCost, EstimasteRows 등 우리에게 이미 친숙한(? ^^;) 내용들을 확인할 수 있을 것이다. 또한 콤마구분자로 리스트된 Defined Values와 같은 생소한 컬럼도 확인할 수 있을 것이다. Defined Values란 해당 연산으로 인해 결과치나 컬럼의 데이터 스트림이다.

다음 이야기 : 5. 실행 계획 기초 - XML 실행 계획 사용하기

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

6. 실행 계획 기초 - SQL Server Profiler로 실행 계획 캡처하기 (0)	2011.07.17
5. 실행 계획 기초 - XML 실행 계획 사용하기 (0)	2011.07.17
3. 실행 계획 기초 - 그래픽 실행 계획 사용하기 (0)	2011.07.17
2. 실행 계획 기초 - 실행 계획 깨부수기를 함께하기 위해 먼저 해야 할 일 (0)	2011.07.17
1. 실행 계획 기초 - SQL을 질의하면 어떤일이 발생할까? (0)	2011.07.17

본 포스트의 내용은 MS SQL Server2005 이상에 국한된 내용입니다.

퍼가실 때는 덧글을 달아주시는 센스를...

그래픽 실행 계획 사용하기

먼저 예상 실행 계획과 실질 실행 계획을 표시하는 방법을 살펴보도록 하겠다.

MS SQL Server Management Studio를 열고 쿼리 창에서 아래의 스크립트를 작성하자.

SELECT *
FROM [dbo].[DatabaseLog];

예상 실행 계획(Estimated Execution Plan) 표시하기

그래픽 예상 실행 계획을 표시하기 위해 실제로 쿼리를 실행할 필요는 없다.

아래의 방법 중 하나를 통해 예상 실행 계획을 표시할 수 있다.

툴바의 "예상 실행 계획 표시(Display Estimated Execution Plan)"를 클릭한다.
쿼리 창에서 마우스 오른쪽 버튼을 클릭 후 나타난 메뉴에서 "예상 실행 계획 표시(Display Estimated Execution Plan)"를 선택한다.
"쿼리" 메뉴에서 "예상 실행 계획 표시(Display Estimated Execution Plan)"를 선택한다.
CTRL + L 단축키를 사용한다.

아래는 위에서 작성한 T-SQL의 예상 실행 계획을 표시한 예이다.

[그림 1] 예상 실행 계획

실질 실행 계획(Actual Execution Plan) 표시하기

실질 실행 계획은 예상 실행 계획과 달리 쿼리 옵티마이저가 계산한 결과를 보여주는 것이 아니라, 쿼리가 실행되었을 때 어떤 일이 발생했는지는 보여준다. 이 두 가지는 때때로 다르게 보여지기도 하는데, 이는 storage engine이 만들어진 실행 계획을 변경했기 때문이다.

아래는 실질 실행 계획을 표시하는 방법이다.

툴바에서 "실질 실행 계획 포함(Include Actual Execution Plan)"를 클릭한다.
쿼리 창에서 마우스 오른쪽 버튼을 클릭 후 나타난 메뉴에서 "실질 실행 계획 포함(Include Actual Execution Plan)"를 선택한다.
"쿼리" 메뉴에서 "실질 실행 계획 포함(Include Actual Execution Plan)"를 선택한다.
CTRL + M 단축키를 사용한다.

이 옵션들을 ON 설정하면 강제로 OFF를 하기 전까지 계속된다.

아래는 위에서 작성한 T-SQL의 실질 실행 계획을 표시한 예이다.

[그림 2] 실질 실행 계획

그래픽 실행 계획 해석하기

SQL Server의 실행계획에는 약 78개의 실행 계획 연산자(Showplan operator)가 있다. 그 중에서 가장 기본적인 2개의 연산자를 그림 1, 2을 통해 먼저 살펴보기로 하자. 왼쪽에 SELECT 아이콘(혹은 노드)이 있다. 이는 relational engine으로 부터 반환된 최종 결과를 나타낸다. 그리고 오른쪽에는 Table Scan 이라는 아이콘이 있다. Table Scan에 대해서는 다시 설명하겠지만, 간단히 설명하자면 storage engine이 전체 테이블을 순차적으로 쭉 읽어내는 것을 의미한다. 예제의 경우 WHERE 조건도 없고 Covering Index가 존재하지 않기 때문에 Table Scan이 나타난 것이다.

☞ Covering Index : 쿼리를 충족시키는데 필요한 모든 컬럼들이 포함되어 있는 Nonclustered Index를 의미한다.

Covering Index의 예 - 쿼리 포함되어 있는 col2, col1, col3 가 모두 인덱스에 포함되어 있다.

select col1, col3 from Table1 where col2 = 'value';
create Index Indexname1 on Table1 (col2, col1, col3);

그래픽 실행 계획을 읽는 순서는 "오른쪽에서 왼쪽으로, 위에서 아래로" 이다. 이 순서로 읽어가다 보면 두 아이콘 사이에 화살표가 있음을 확인 할 수 있을 것이다. 화살표는 아이콘으로 표시된 실행 계획 연산자의 실행 순서 혹은 데이터의 흐름을 의미한다. 따라서, 예제의 실행계획은 "Table Scan을 통해 얻은 최종 결과를 표시한다" 라고 해석할 수 있다. 화살표의 두께는 데이터량과 상관관계가 있다. 화살표가 두께가 두꺼우면 두꺼울수록 통과하는 데이터가 많다는 것을 의미한다. 마우스 포인터를 화살표 위로 가져가면 화살표를 통과하는 행 수가 표시된다. 예를 들어 쿼리를 실행해서 얻어지는 최종 결과는 2행이지만, 실행 계획에서 두꺼운 화살표가 표시되어 많은 행을 처리함이 보여진다면 조사해 볼 여지가 있는 것이다.

각각의 아이콘 아래에는 퍼센트가 표시되어 있다. 이 숫자는 각 실행 계획 연산자의 상대 비용을 나타낸다. 예제의 경우, SELECT는 0%, Table Scan은 100% 로써 Table Scan에 모든 비용이 소요되었다. 하지만 이는 비율을 의미하지 실제 숫자를 의미하지 않음을 명심해야 한다. SELECT가 0%라고 해서 SELECT 연산을 수행하는데 든 비용이 0인 것은 아니다.

아이콘 위 쪽에는 "SELECT * FROM [dbo].[DatabaseLog];" 라고 쿼리 스트링이 표시되어 있다. 그리고 그 위로는 "cost (relative to batch): 100%" 라고 표시되어 있다. 만일, 하나의 배치 안에 복수 개의 쿼리가 실행된다면, "cost (relative to batch): xxx%" 부분에는 "cost (relative to batch)" 라고 표시되어 있듯이 배치 내의 쿼리 간의 상대 비용이 표시된다.

ToolTips(도구 설명)

각 아이콘이나 화살표 위로 마우스 포인터를 옮기면 ToolTip라 불리는 팝업 원도우가 나타난다.

먼저, 예제의 SELECT 실행 계획 연산자 위로 마우스 포인터를 옮겨보도록 하자.

아마 아래와 같은 ToolTip 윈도우가 표시될 것이다.

[그림 3] SELECT 실행 계획 연산자

Cached Plan size - 해당 쿼리가 실행 계획을 생성하기 위해 Plan Cache에서 얼마만큼의 메모리를 사용하였는지를 보여준다. 어떤 실행계획이 보다 메모리를 많이 사용하는지 확인할 수 있기 때문에, 캐시 성능을 조사할 때 유용한 수치로 활용할 수 있다.
Estimated Operator Cost (예상 비용) - 쿼리 옵티마이저가 해당 연산을 실행하기 위한 비용이다. 이는 총 쿼리 비용의 백분율로 표시되며, 이는 이미 [그림 1]에서 확인하였다.
Estimated Subtree Cost (예상 하위 트리 비용) - 쿼리 옵티마이저가 해당 연산과 이전의 모든 연산을 실행하기 위한 비용의 누계이다. 시간 비용의 누계로 표시된다.
Estimated number of rows (예상 행 수) - 연산자에서 생성한 행 수이다.

아래는 Table Scan 연산자에 마우스를 가져다 놓았을 때 나타나는 ToolTip이다.

[그림 4] Table Scan 실행 계획 연산자

SELECT 실행 계획 연산자과 사뭇 다른 모습을 보여준다. 좀 더 많은 정보가 보여지는데 위에서 부터 하나 하나 살펴보기로 하자.

먼저, 맨 위에 나와 있는 Physical Operation (물리적 연산)과 Logical Operation (논리적 연산)을 설명하자면, Logical Operation (논리적 연산) 은 쿼리를 실행했을 때 어떤 일이 발생할지 쿼리 옵티마이저가 연산한 결과를 논리적으로 표현한 것이고, Physical Operation (물리적 연산)은 실제로 어떤 연산을 수행하는지 보여준다. 일반적으로 Logical Operation (논리적 연산)과 Logical Operation (논리적 연산)는 같지만 항상 그런 것은 아니다.

그 아래로 Estimated I/O Cost (예상 I/O 비용), Estimated CPU Cost (예상 CPU 비용), Estimated Operator Cost (예상 비용), Estimated Subtree Cost (예상 하위 트리 비용) 가 보인다. 여기서 Subtree(하위 트리)란 해당 실행 연산자의 화살표를 역추적했을 때 나타나는 모든 실행 노드(혹은 연산자)의 가지를 의미한다. 예를 들어 노드 B의 Subtree는 노드 D + 노드 E + 노드 H 이다.

[그림 5] 하위 트리(Subtree)

Estimated I/O Cost (예상 I/O 비용)와 Estimated CPU Cost (예상 CPU 비용)는 연산자가 아니다. 이는 쿼리 옵티마이저가 비용 계산을 수행하는 동안 할당된 비용 수치이다. 이 숫자가 크면 클수록 보다 많은 처리를 한다는 것을 의미한다. 따라서, 우리는 이 수치를 통해 대부분의 비용이 I/O에서 발생하는지(예제가 그러하다), 혹은 CPU에 부하가 발생하는지 예측할 수 있다. 다시 한 번 말하지만 이 숫자는 절대적인 수치가 아니라 해당 노드에서 실제 비용이 어디에서 발생하는지 측정하는데 도움을 주는 조언자라고 생각하는 것이 옳다.

예제에서는 Table Scan 연산 밖에 없기 때문에 Estimated Operator Cost (예상 비용)와 Estimated Subtree Cost (예상 하위 트리 비용)가 같다. 하지만 보다 많은 연산자가 나타나는 복잡한 실행 계획 트리에서는 Estimated Subtree Cost (예상 하위 트리 비용)이 각 하위 트리에서 발생한 비용의 누계로 나타난다. 실행 계획의 총 비용은 마지막 노드(연산자)에서 확인할 수 있다. 여기서는 SELECT 연산자이다.

Estimated number of rows (예상 행 수)는 각 노드별로 표시된다. 왜냐하면 각 노드별로 처리하는 데이터가 다르기 때문이다. 보다 복잡한 쿼리에서는 각 노드 통과하는 동안 다양한 연산자가 처리를 수행함에 따라 이 수치가 바뀌는 것을 쉽게 확인할 수 있다. 우리는 이 수치를 통해 쿼리가 실행되는 동안 각각의 노드에서 행이 어떻게 추가되고 걸러지는지 알 수 있다.

또 하나의 중요한 정보로 Ordered (순서)가 있는데 불리언(Boolean) 값으로 표현된다. 이는 성능 튜닝시 중요한 이슈인데, 해당 노드에서 데이터를 정렬하느냐 안 하느냐를 표시한다. 예를 들어 SELECT 구문에 ORDER BY 구를 사용했을 경우 ORDER BY에 나열된 특별한 순서로 정렬한 필요가 있다. 이 때 Ordered (순서) 상태 값을 통해 어떤 노드에서 정렬이 발생하는지 확인할 수 있다.

마지막으로 Node ID (노드 ID)가 있다. 이는 단순히 각 노드의 순서를 의미한다. 사실 이 값은 실행 계획을 읽는 순서대로 오른쪽에서 왼쪽으로 값이 매겨지는 것이 타당하겠지만, (재미있다고 해야 하나?) 애석하게도 왼쪽에서 오른쪽으로 값이 매겨진다.

이상 설명한 모든 내용은 여러분이 쿼리를 실행할 때 어떤 일이 발생하는지 이해하는데 큰 도움을 줄 것이다. 하위 트리에서 비용이 어떻게 산출되었는지, 각 노드별로 행 수가 어떻게 바뀌는 지 등을 다양한 연산자를 통해 관찰할 수 있을 것이며, 또한 얼마만큼 CPU를 사용하는지, 테이블에 인덱스가 더 필요한지 혹은 필요없는지 등 여러가지 튜닝의 요소를 판단하는 근거로 사용할 수 있다.

Operator Properties(그래픽 실행 계획 노드 속성)

ToolTip보다 더 상세한 정보를 확인하는 방법이 있다. 그래픽 실행 계획내의 특정 아이콘을 마우스 오른쪽 버튼 클릭을 하면 나타나는 "Properties(속성)" 메뉴를 선택하면 해당 연산자와 관련된 상세 정보가 나타난다.

아래는 Table Scan의 등록정보를 표시한 내용이다.

[그림 6] Table Scan의 그래픽 실행 계획 노드 속성

몇 가지 새로운 정보를 제외하고는 ToolTip에 나타난 내용과 비슷하다. 그럼 새로운 정보에 대해 위에서 부터 하나씩 살펴보도록 하자.

먼저 Defined Values 라는 것이 보인다. Defined Values는 해 당 연산자가 추가한 프로세스 정보를 표시한다. 이는 기본 쿼리의 일부분이 될 수도 있고 - 예제의 경우 SELECT된 컬럼이다 -, 참조 무결성을 확인하기 위한 플래그로 사용될 수 있으며, 또는 집계 함수를 위한 자리 표시자( 개체 틀)가 될 수도 있다.

Defined Values 속성 밑으로 연산자에 대한 설명과 예상 비용에 관련된 아래와 같은 내용을 추가적으로 볼 수 있다.

Estimated Rebinds & Estimated Rewind - 이 값은 실행계획 내에서 Init() 연산자가 얼마나 호출되었는지 나타내는 수치이다.
Forced Index - 이 값이 True라면 특정 인덱스 사용을 강제하도록 쿼리 힌트(Query hint)를 사용했음을 의미한다. SQL Server는 쿼리의 실행을 어느 정도 제어할 수 있도록 쿼리 힌트(Query hint)로써 그 기능을 제공하는데, 이는 나중에 다시 살펴보도록 하겠다.
NoExpandHint - 어떤 의미에서는 Forced Index와 같은 컨셉이지만 indexed view에는 적용되지 않는다.

다음 이야기 : 4. 실행 계획 기초 - 텍스트 실행 계획 사용하기

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

5. 실행 계획 기초 - XML 실행 계획 사용하기 (0)	2011.07.17
4. 실행 계획 기초 - 텍스트 실행 계획 사용하기 (0)	2011.07.17
2. 실행 계획 기초 - 실행 계획 깨부수기를 함께하기 위해 먼저 해야 할 일 (0)	2011.07.17
1. 실행 계획 기초 - SQL을 질의하면 어떤일이 발생할까? (0)	2011.07.17
실행계획 (0)	2011.07.17

본 포스트의 내용은 MS SQL Server2005 이상에 국한된 내용입니다.

퍼가실 때는 덧글을 달아주시는 센스를...

실행 계획 깨부수기를 함께하기 위해 먼저 해야 할 일

본 토픽, 실행 계획 깨부수기를 함께하기 위해 먼저 테스트 환경을 구성하도록 하겠다.

예제 데이터베이스 설치

앞으로 우리는 SQL Server 2005의 예제 데이터베이스인 AdventureWork 를 사용할 것이다. 만일 AdventureWork 데이터베이스가 설치되어 있지 않다면 아래의 링크에서 다운로드 받아 설치하기 바란다. 데이터베이스 설치는 본 토픽의 주제에서 벗어난 내용이기 때문에 따로 설명하지 않을 것이므로 아래의 링크나 BOL 등의 도움을 받아 설치하기 바란다.

http://www.codeplex.com/MSFTDBProdSamples

그리고 여러분과 필자의 환경이 다르기 때문에 복잡한 실행 계획의 경우 실행 계획이 다르게 표시될 수 있다는 점을 유의하기 바란다. SQL Server의 버전이 무엇이냐에 따라 달라질 수 있다. 그리고, AdventureWork 데이터베이스 카피가 얼마나 오래되었는지에 따라서 데이터베이스의 통계가 다르거나, 인덱스가 다르게 생성되어 있을 수도 있으며, 스키마의 구조나 데이터도 다를 수도 있다.

실행 계획을 보기 위해 필요한 권한 설정

실행 계획을 보기 위해서는 sysadmin, dbcreator와 같은 server role에 속해 있거나, db_owner 등의 database role에 속해 있어야 한다. 만일 그렇지 않다면 아래의 쿼리를 실행하여 실행 계획을 볼 수 있도록 특정 유저에게 권한을 부여하기 바란다.

GRANT SHOWPLAN TO [username]

다음 이야기 : 3. 실행 계획 기초 - 그래픽 실행계획 사용하기

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

4. 실행 계획 기초 - 텍스트 실행 계획 사용하기 (0)	2011.07.17
3. 실행 계획 기초 - 그래픽 실행 계획 사용하기 (0)	2011.07.17
1. 실행 계획 기초 - SQL을 질의하면 어떤일이 발생할까? (0)	2011.07.17
실행계획 (0)	2011.07.17
MS-SQL 2005 실행계획 (3) (0)	2011.07.17

본 포스트의 내용은 MS SQL Server2005 이상에 국한된 내용입니다.

퍼가실 때는 덧글을 달아주시는 센스를...

실행 계획(Execution Plan)의 기초

실행 계획(Execution plan, 이하 실행 계획)이란 간단히 말해서 T-SQL 쿼리를 가장 최적화된 방법으로 실행하기 위해 Query optimizer(이하 쿼리 옵티마이저)의해 계산하여 만들어진 결과이다.

쿼리 옵티마이저는 RDBMS에 있어 마치 인간의 두뇌와 같은 부분으로써, 사용자가 질의한 T-SQL이 어떻게 실행되었는지, 혹은 T-SQL을 어떻게 실행할지 순식간에 계산하여 우리에게 알려준다. 따라서, 실행 계획은 악성 쿼리를 튜닝시 가장 중요한 도구로 사용된다. 예를 들어 해당 테이블에 인덱스가 적절하게 설계되어 있지 않다면, 원하는 데이터만 엑세스하지 못하고 테이블 전체를 스캔을 하는 경우가 발생할 수 있을 것이며, 이를 실행 계획을 통해 확인할 수 있는 것이다.

본 포스트에서는 실행 계획 이야기 중 아래의 토픽에 대해 먼저 다루어 보도록 하겠다.

쿼리 옵티마이징(쿼리 최적화)의 작동원리
예상 실행 계획과 실질 실행 계획
실행 계획을 확인 방법
SQL Server Profiler를 이용하여 실행 계획을 캡쳐하는 방법

T-SQL을 질의하면 어떤일이 발생할까?

SQL Server에는 많은 프로세스들이 존재한다. 이 프로세스들은 데이터 질의에 대한 처리, 데이터 저장, 데이터 유지보수 등과 같이 시스템 관리를 위해 다양한 일들을 수행하는데, 우리가 T-SQL을 질의하면 SQL Server은 해당 쿼리를 수행하기 위해 몇 가지 프로세스를 동작시킨다.

이 중에서 T-SQL에 관련된 프로세스는 크게 아래의 두 개로 요약할 수 있다.

Relational engine 관련 프로세스
Storage engine 관련 프로세스

Relational engine은 보내어진 쿼리를 파싱한 후, 실행 계획을 작성하기 위해 쿼리 옵티마이저에게 해당 쿼리를 보낸다. 이 단계에서 작성된 실행 계획은 다시 Storage engine에 보내어 지게 되며, Storage engine은 실행 계획을 바탕으로 데이터를 검색하거나 입력, 갱신, 삭제하는 등 실질적인 데이터 처리를 수행한다. 그리고, Storage engine에는 잠금(lock), 인덱스 유지보수(index maintenance), 트랜잭션과 같은 프로세스들이 있다.

따라서, 앞으로 우리는 이 두 개의 engine 중 Relational engine에 포커스를 맞추어 이야기 할 것이다.

쿼리 파싱(Query parsing)

위에서 살펴보았듯이, T-SQL을 실행의 첫 단계는 Relational engine에서 쿼리를 파싱하는 일이다. 사용자가 T-SQL을 질의하면 먼저 T-SQL문이 정확한지 구문 검사를 하는데 이를 파싱(Parsing)이라고 한다. 그리고, 파싱 프로세스(Parse process)의 결과물을 파스 트리(parse tree), 쿼리 트리(query tree) 혹은 시퀀스 트리(squence tree)라고 하며 쿼리 실행을 위한 논리적 단계를 의미한다.

만일, 질의한 T-SQL이 DML(Data Manipulation Language)문이 아니라면 최적화(Optimizing) 단계는 건너 뛰게 된다. 예를들어 CREATE TABLE문은 단순히 "테이블을 이렇게 이렇게 작성해 주세요" 라는 의미이기 때문에 최적화할 수 있는 여지가 없는 것이다. 반대로 질의한 T-SQL이 DML인 경우에는 algebrizer라고 불리는 프로세스를 호출하여 파스 트리(parse tree)를 통과시킨다. algebrizer 프로세스에서는 질의된 T-SQL에 관련된 모든 오브젝트(테이블, 컬럼 등)을 체크하고, 이와 동시에 aggregate binding 이라고 불리는 프로세스를 함께 호출하여 GROUP BY, MAX와 같은 집계위치를 체크한다. 이렇게 쿼리 옵티마이저를 통과하여 algebrizer 프로세스가 처리한 결과물을 query processor tree라고 한다.

쿼리 옵티마이저(Query optimizer)

쿼리 옵티마이저는 Database relational engine이 동작하기 위한 길(혹은 모델)을 제시하는 역할을 하는 소프트웨어 조각이라고 말할 수 있다. 쿼리 옵티마이저는 query processor tree와 통계정보를 바탕으로 질의된 쿼리를 실행하기 위한 가장 최적화된 방법을 계산한다. 예를 들어, 해당 T-SQL을 실행하기 위해 인덱스를 사용할지, 사용한다면 어떤 인덱스를 사용할지, 혹은 조인을 한다면 어떤 조인 방법을 사용할지 등 많은 것을 결정한다. 그리고 이는 CPU 사용, I/O 그리고 실행 방법에 대한 비용(Cost) 등 다양한 계산 결과에 근거한다. 그래서 우리는 이를 비용 기반 최적화기(Cost-based optimizer)라고 하는 것이다.

퀴리 옵티마이저는 - 실행 계획이 캐쉬되어 있지 않다면 - 해당 T-SQL을 실행하기 위해 여러가지 경우의 수(실행 계획)를 생각하고, 이 중에서 가장 적은 리소스를 사용하는 실행 계획을 선택한다. 그렇다고 퀴리 옵티마이저가 항상 최적의 실행 계획을 제시하는 것은 아니라는 것을 유의하기 바란다.

만일 아주 심플한 쿼리 - 예를 들어 인덱스를 사용하지 않으며 집계나 계산이 없는... 단순히 하나의 테이블을 쿼리하는 경우 - 인 경우, 실행 계획이 이미 명백하기 때문에 실행 계획을 계산하기 위해 시간을 보내기 보다는 그냥 실행 계획 하나를 적용한다. 그래서 이를 Trivial plan (명백한 플랜) 이라고 한다. 반대로 non-trival일 경우, 퀴리 옵티마이저는 실행 계획을 선택하기 위해 비용 기반(cost-based) 연산을 실행하며, 이를 위해 SQL Server 스스로가 유지·관리하는 통계정보를 적용한다.

통계정보는 데이터베이스 내의 컬럼과 인덱스 등에서 수집되며, 데이터의 분포도, 유일성, 선택도 등을 나타낸다. 이 정보는 히스토그램 형태로 표현되는데, 전체 데이터에서 200 데이터 포인트에서 추출한 특정 값의 발생 빈도표이다. 이는 "데이터의 데이터" - 흔히들 메타데이터라고 하는 - 로써 쿼리 옵티마이저가 계산하는데 필요한 정보로 제공되어 진다.

만일, 상응하는 컬럼이나 인덱스의 통계정보가 이미 존재한다면 쿼리 옵티마이저는 그것을 사용할 것이다. 기본적으로 WHERE절이나 JOIN ON절의 한 부분으로 선언된 모든 컬럼과 인덱스에 대한 통계정보는 자동으로 생성 및 갱신된다. 반대로 통계정보가 한번도 생성되지 않은 테이블에 대해서는 그 테이블의 실제 크기를 무시하고 하나의 로우만 있다고 쿼리 옵티마이저는 가정한다. 또한, 통계정보가 생성되어 있는 임시 테이블의 경우 마치 영구 테이블처럼 히스토그램을 저장하여 사용한다.

이렇게 수집된 통계정보와 함께 앞에서 설명한 Query processor tree를 기반으로 쿼리 옵티마이저는 최적의 실행 계획을 선택하게 된다. 인생사에서도 그렇듯이 선택이라는 것은 참으로 힘든 과정이다. 이것은 쿼리 옵티마이저도 마찬가지이다. 최적의 실행 계획을 도출해 내기 위해, 일련의 실행 계획들에 대해 다양한 조인(Join) 전략을 테스트하기도 하고, 조인 순서도 바꿔보기도 하고, 때로는 다른 인덱스를 이용해 보는 등 많은 고민을 한다. 이러한 연산이 수행되는 동안 실행 계획 내의 각 스텝에 일련의 숫자가 할당하는데, 이 숫자는 해당 스텝에 수행되는데 필요한 예상 소요 시간을 의미한다. 이를 예상비용(이하 Estimated cost) 라고 하며, 결국 각 스텝의 비용의 누적치가 해당 플랜 전체의 비용이 되는 것이다.

여기서 우리는 중요한 사실을 기억해야 한다. Estimated cost는 말 그대로 예상치 라는 것을 말이다. 쿼리 옵티마이저에게 충분한 시간을 주고 매일 통계정보를 갱신시켜 준다면 쿼리를 실행하기 위한 완벽한 실행 계획을 찾을 것이다. 그러나 현실은 그렇지 못하다. 쿼리 옵티마이저가 하나의 T-SQL만 처리하는 것도 아닐 뿐더러, 제한된 통계정보를 바탕으로 수천 혹은 수만 분의 일초 안에 최적의 실행 계획을 찾아야 한다. 따라서 Estimated cost는 도구일 뿐이지 현실을 반영한 것이 아니라는 것을 유의해야 할 것이다.

쿼리 옵티마이저가 실행 계획을 작성하면 실질 실행 계획(Actual execution plan)을 만들고, 이와 똑같은 실행 계획이 캐시에 없다면 Plan cache이라고 알려져 있는 메모리 영역에 저장된다. 이는 아래의 실행 계획 재사용(Execution plan reuse)에서 다시 설명하도록 하겠다.

쿼리 실행(Query execution)

실행 계획이 작성되면 Storage engine이 쿼리를 실행한다. 앞에서 언급하였다시피 Storage engine에 대한 이야기는 본 포스트의 주제에서 벗어나기 때문에, 앞으로는 아래의 예처럼 작성된 실행 계획과 다르게 쿼리가 실행되는 경우와 같은 주의사항만 언급하기로 한다.

병렬 실행(Parallel execution)을 할 경우 ( 병렬 실행에 대해서는 다음에 살펴보도록 하겠다.)
최초 실행 계획이 작성된 당시의 통계정보가 오래되어 더 이상 무의미할 경우, 혹은 통계정보가 바뀌었을 경우

쿼리 실행 결과는 요청한 포맷에 맞추어 Relational engine이 처리하여 반환된다.

실행 계획 재사용(Execution plan reuse)

SQL Server가 실행 계획을 작성하는데 드는 비용과 부하는 상당하다. 따라서 SQL Server는 한 번 작성된 실행 계획을 어딘가에 보관해 두었다가 기회가 되면 다시 사용하려고 한다. 그 어딘가가 plan cache (이전에는 procedure cache) 라 불리는 곳이고, 기회라는 것운 똑같은 쿼리가 들어올 때이다.

SQL Server에 쿼리를 질의하면 쿼리 옵티마이저는 예상 실행 계획(Estimated Execution Plan)을 작성한다. 그리고 storage engine을 통과하기 전에, 작성된 예상 실행 계획과 plan cache에 저장되어 있는 실질 실행 계획(Actual Execution Plan) 을 비교하여 일치하는 실행 계획이 존재한다면 이를 사용한다. SQL Server는 실행 계획을 재사용 함으로써 아주 크고 복잡한 쿼리, 혹은 단순하지만 1분에 수 천, 수 만 번 실행되는 작은 쿼리에 대해 실질 실행 계획을 작성해야 하는 오버헤드를 피할 수 있는 것이다.

쿼리 옵티마이저는 각 실행 계획이 병렬 실행(Parallel execution) 했을 때 더 좋은 성능을 낼 수 있다고 판단하지 않는 이상 실행 계획을 단 한 번만 저장한다. 하지만 쿼리 옵티마이저가 병렬 실행을 선택한다면, 병렬 실행을 지원하는 또 다른 실행 계획을 작성하고 저장한다. 즉 이 순간 하나의 쿼리에 대해 두 개의 실행 계획이 존재하게 되는 것이다.

한 번 저장된 실행 계획은 메모리에 영원히 저장되는 것은 아니다. SQL Server는 저장된 실행 계획이 얼마나 자주 실행되는지 주기적으로 지켜보고 있다가 다음과 같은 공식에 의거하여 자주 사용하지 않는 실행 계획을 메모리에서 비운다. 이를 age out 이라고 한다.

age = [estimated cost for the plan] X [the number of used times]

이 작업은 lazywriter라는 내부 프로세스가 담당하는데 비단, plan cache 뿐만 아니라 모든 종류의 캐시를 비우는 일을 한다.

Lazywriter는 아래와 같은 이슈가 발생했을 때 메모리에서 실행 계획을 비운다.

시스템 메모리가 부족하여 시스템에게 메모리를 양보해야 할 경우
실행 계획의 "age"가 0에 도달하여 실행 계획을 더 이상 사용하지 않을거라 판단할 경우
SQL Server에 연결되어 있는 현재 커넥션들이 더 이상 실행 계획을 참조하지 않을 경우

실행 계획은 어떤 이벤트나 액션에 의해 재컴파일 되기도 한다. 실행 계획 재컴파일은 매우 비용이 높은 작업이므로 아래의 내용을 잘 숙지해야 한다.

실행 계획 재컴파일을 유발하는 액션들

쿼리가 참조하는 테이블의 스키마나 구조가 바뀌었을 경우
쿼리에서 사용된 인덱스가 바뀌었을 경우
쿼리에서 사용된 인덱스가 삭제되었을 경우
쿼리에서 사용된 통계정보가 갱신되었을 경우
sp_recompile 함수가 호출되었을 경우
대량의 insert나 delete를 하는 쿼리를 할 때 (특히 해당 쿼리가 테이블의 key가 참조될 경우)
트리거로 인해 inserted 테이블과 deleted 테이블의 사이즈가 급격히 증가하였을 경우
하나의 쿼리 안에 DDL과 DML이 함께 존재하는 경우(deferred compile)
쿼리 내의 SET 옵션을 변경하는 경우
쿼리에서 사용된 임시 테이블의 스키마나 구조가 변경되었을 경우
쿼리에서 사용된 동적 뷰(dynamic view)가 바뀌었을 경우
쿼리 내의 커서 옵션을 변경한 경우
Distributed partitioned view와 같은 remote rowset 을 변경한 경우
FOR BROWSE 옵션이 변경되어 클라이언트 측 커서(clinet side cursor)를 사용할 경우

경우에 따라서는 캐시를 비워야 할 필요가 있을 수도 있다. 예를 들어 실행 계획을 컴파일할 때 얼마나 시간이 걸리는지, 혹은 튜닝 전후의 실행 계획을 비교하고자 할 때 아래의 명령어를 사용하여 캐시를 비울 수 있다.

DBCC FREEPROCCACHE

※ Plan cache를 이 전에는 procedure cache라고 했기때문에 명령어가 이렇지 않은가 싶다.

그리고 아래와 같이 몇 가지 동적 관리 뷰(dynamic management view)와 동적 관리 함수(dynamic management function)를 조회하는 T-SQL문을 작성하여, SQL Server내에서 실행된 쿼리와 해당 쿼리가 실행되었을 때의 실행 계획을 조회할 수 있다.

SELECT [cp].[refcounts]
      ,[cp].[usecounts]
      ,[cp].[objtype]
      ,[st].[dbid]
      ,[st].[objectid]
      ,[st].[text]
      ,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp;

이 쿼리를 사용하면 호출된 T-SQL과 해당 T-SQL이 실행될 때 작성된 XML 실행 계획을 모두 확인 할 수 있다. 그리고 XML 실행 계획은 XML 형식으로 직접 확인하거나 그래픽 실행 계획으로 파일을 열어서 확인할 수 있다.

왜 "예상 실행 계획"과 "실질 실행 계획"이 차이를 보이는 것일까?

일반적으로, 여러분은 예상 실행 계획과 실질 실행 계획의 차이점을 발견하기 힘들 것이다. 하지만, 아래와 같이 어떤 환경적인 요인으로 인해 예상 실행 계획과 실질 실행 계획이 달라지는 경우도 발생한다.

실행 계획이 오래된 경우

두 개의 실행 계획이 달라지게 되는 가장 큰 요인은 수집된 통계정보와 실 데이터가 다른 경우이다. 그렇다면 이 두 가지가 달라지는 원인은 무엇일까?

서비스가 시작되고 시간이 경과함에 따라 데이터의 추가, 갱신, 삭제가 끊임없이 반복될 것이다. 이 과정에서 데이터나 인덱스의 키 값의 분포도가 달라지게 된다. 예를 들어 처음에는 'A'라는 데이터가 전 데이터의 대부분을 차지 했지만 시간이 흐를수록 'B"라는 데이터의 비중이 높아질 수도 있다. 이 경우 통계정보를 갱신시켜야 할 것인데, 일반적으로 통계정보 자동 갱신 옵션이 'ON'으로 설정되 어 있다면, SQL Server는 통계정보를 주기적으로 자동 갱신한다. 하지만 통계정보 자동 갱신에는 한계가 있다. 왜냐하면, SQL Server는 통계정보의 갱신 비용을 줄이기 위해 전 데이터가 아닌 일부 데이터를 샘플링하여 통계정보를 계산하기 때문이다. 이는 곧, 시간이 지나면 지날수록 통계정보와 실 데이터의 차이는 점점 더 커진다는 것을 의미한다.

예상 실행 계획이 Invalid일 경우

어떤 상황에서는 예상 실행 계획이 제대로 동작하지 않을 수도 있다. 간단한 테스트를 위해 아래의 T-SQL 스크립트를 작성한 후 예상 실행 계획을 보도록 하자. (예상 실행 계획을 보는 방법은 실행 계획 기초(3) 그래픽 실행계획 사용하기 를 참고하기 바란다.)

CREATE TABLE [TempTable]
    (
        Id int IDENTITY (1,1)
       ,Dsc NVARCHAR(50)
    );

INSERT INTO TempTable (Dsc)
SELECT [Name]
FROM [Sales].[Store];

SELECT *
FROM [TempTable];

DROP TABLE [TempTable];

이 스크립트의 예상 실행 계획을 표시하면 아마 아래와 같은 에러를 확인할 수 있을 것이다. 왜일까?

Msg 208, Level 16, State 1, Line 7
Invalid object name 'TempTable'.

쿼리 옵티마이저는 먼저, 과거에 어떤 예상 실행 계획이 작성되었는지 확인하려 할 것이다. 이 때 앞에서 살펴 보았듯이 해당 구문을 algebrizer 프로세스에 통과시킨다. 하지만 algebrizer 프로세스를 통과하는 시점에는 TempTable 이라는 테이블은 아직 존재하지 않는다. 따라서 'TempTable이라는 테이블이 없습니다.' 라고 에러를 발생시킨다. 그러나 이 스크립트가 실질 실행 계획을 통해 실행되면 - 그냥 해당 T-SQL 스크립트를 실행하면 -, 문제없이 잘 실행되는 것을 확인할 수 있다.

병렬실행이 요청되었을 경우

병렬실행의 임계치를 만나게 되면 두 개의 실행 계획이 만들어진다. 이 중에서 어떤 실행 계획을 사용할지는 query engine이 판단한다. 따라서 예상 실행 계획에서 parallel operator가 보일 수도, 혹은 보이지 않을 수도 있다. 또한 쿼리가 실제로 실행되었을 때에도 query engine이 병렬실행을 선택하느냐 마느냐에 따라 전혀 다른 실행 계획을 볼 수도 있다.

실행 계획 형식

SQL Server는 실행 계획을 볼 수 방법으로 아래의 3가지 형식을 지원한다.

그래픽 실행 계획(Graphical Plans)
텍스트 실행 계획(Text Plans)
XML 실행 계획(XML Plans)

얼마나 상세한 정보를 보기를 원하는지 자신의 기호에 따라 선택해서 사용하면 된다.

그래픽 실행 계획

실행 계획을 보는 방법 중 가장 빠르고 직관적인 방법일 것이다. 실행 계획의 자세한 내용까지 확인 가능하며 예상 실행 계획과 실질 실행 계획 모두 그래픽 실행 계획으로 확인할 수 있다.

텍스트 실행 계획

그래픽 실행 계획보다는 가독성이 좋지 못하다. 그러나 보다 많은 정보를 즉시 활용할 수 있다는 장점을 가지고 있다.

텍스트 실행 계획에는 다음의 3가지 형식이 있다.

SHOWPLAN_ALL : 해당 쿼리에 대한 예상 실행 계획을 보여준다.
SHOWPLAN_TEXT : osql.exe 와 같은 툴에서 사용할 수 있도록 매우 제한된 실행 계획 데이터를 보여준다. 역시 예상 실행 계획만 볼 수 있다.
STATISTICS PROFILE : 실질 실행 계획을 보여준다는 것을 제외하고는 SHOWPLAN_ALL과 흡사하다.

XML 실행 계획

XML 실행 계획은 XML 형식으로 실행 계획 내의 대부분의 데이터를 보여준다.

XML 실행 계획에는 다음의 2가지 형식이 있다.

SHOWPLAN_XML : 해당 쿼리에 대한 예상 실행 계획를 XML 형식으로 보여준다.
STATISTICS_XML : 해당 쿼리에 대한 실질 실행 계획을 XML 형식으로 보여준다.

다음 이야기 : 2. 실행 계획 기초 - 실행 계획 깨부수기를 함께하기 위해 먼저 해야 할 일

저작자표시

'연구개발 > 실행계획' 카테고리의 다른 글

3. 실행 계획 기초 - 그래픽 실행 계획 사용하기 (0)	2011.07.17
2. 실행 계획 기초 - 실행 계획 깨부수기를 함께하기 위해 먼저 해야 할 일 (0)	2011.07.17
실행계획 (0)	2011.07.17
MS-SQL 2005 실행계획 (3) (0)	2011.07.17
MS-SQL 2005 실행계획 (2) (0)	2011.07.17