DataBaser.Net: Spool

1 Spool의 개념
2 옵티마이저 모드에 따라서 Spool이 생길 수 있다
3 Distinct Count
4 SQL Server의 Spool의 종류
5 Spool이 필요한 이유

문서 작성 중..

1 Spool의 개념 #

Spool은 반복적으로 데이터를 사용할 필요가 있으면 옵티마이저가 tempdb에 데이터를 저장하여 읽는 연산을 말한다. 반복적인 읽기에 Spool 연산이 발생되는 이유는 '반복적 읽기'에 있다. 반복적인 읽기는 반복적인 공유락(S)를 걸게 되고, 이는 블록킹으로 이어져 동시성을 떨어뜨리기 때문이다. 동시성이 떨어진다는 것은 성능이 나빠진다는 것을 의미한다. 즉, 블록킹 또는 데드락이 걸리게 되어 후속 작업들이 대기하게 됨을 말한다. 블록킹 자체가 나쁜 것은 아니나 장시간에 걸쳐 블록킹이 발생하면 많은 작업이 대기하게 된다. 최악의 경우는 블록킹이 풀리면서 대량의 작업이 동시 다발적으로 실행되어 자원의 병목현상도 발생하게 된다. 또한 반복적인 읽기는 논리적이든지 물리적이든지 다량의 I/O를 발생하게 되어 시스템의 자원을 낭비하게 된다.

일반적으로 볼 수 있는 Spool연산자는 Table Spool과 Index Spool이다. 한 가지 예로 SUM()과 같은 다른 집계함수와 COUNT(DISTINCT )을 같이 사용하면 반드시 Spool을 볼 수 있다. SQL Server는 Spool이 발생하게 되면 tempdb를 사용하게 된다. (필자의 개인적인 생각으로는 Memory에서 Distinct 연산을 하던지 tempdb를 이용하던지 선택할 수 있는 옵션이 있었으면 하는 바램이다. COUNT(DISTINCT ColumnName)는 성능이 너무 꾸지다.)

데이터베이스는 공용으로 사용되는 것이기 때문에 Spool은 동시성을 위해서 반드시 필요하다. Spool은 옵티마이저가 해주는 일종의 '스냅샷'이며, '개인화'라고 생각하면 된다. Spool 자체는 I/O가 많이 발생하는 연산이므로 OLTP시스템에서는 Spool자체가 없게 SQL문을 작성하는 것이 좋다.

[edit]

2 옵티마이저 모드에 따라서 Spool이 생길 수 있다 #

오라클의 경우 비용 기반의 옵티마이저 모드(필자가 알기로는 Oracle 10g버전까지는 이렇다)가 3가지가 있다. (3가지인지 2가지라고 해야 하는지 모르겠다)

FIRST_ROWS
FIRST_ROWS_n
ALL_ROWS

FIRST_ROWS는 첫 1행을 클라이언트에 재빨리 반환하는 실행계획을 세우는 동작 방식이고, FIRST_ROWS_n는 첫 n행을 클라이언트에 재빨리 반환하는 실행계획을 세우는 동작 방식이다. ALL_ROWS는 전체 결과집합에 대한 최적화를 수행한다. (PL/SQL로 작성된 프로시저는 옵티마이저 모드가 어떤 것이든 ALL_ROWS 모드로 동작하는 것으로 알고 있다) SQL Server에는 이런 옵티마이저 모드를 선택할 수는 없지만 힌트를 통해 흉내를 낼 수 있다. 문서화 된 것은 아니지만 필자가 실험을 통해 알아본 바로는 SQL Server는 기본적으로 ALL_ROWS 모드처럼 전체 결과집합에 대한 최적화를 수행한다. 다음의 UPDATE 문을 보자.

UPDATE a
SET
	a.AccountKey = b.AccountKey
,	a.BirthDT7 = b.BirthDT7
FROM dbo.Character a
	OUTER APPLY (
		SELECT TOP 1 AccountKey, BirthDT7
		FROM dbo.Dim_Customer 
		WHERE a.AccountID = AccountID
	) b
WHERE a.BirthDT7 IS NULL

위 UPDATE문은 다음과 같이 실행되었다. 중간에 보면 Index Spool을 확인 할 수 있다.

이 실행계획은 전체 결과집합을 재빨리 반환하도록 최적화된 계획이다. 만약 응답시간(클라이언트의 요청 후 첫 행이 반환되는 시간)을 빠르게 하고자 한다면 다음과 같이 힌트 OPTION(FAST 1)를 주거나 또는 FastFirstRow(이건 나중에 없어진다고 한다) 힌트를 사용하여 옵티마이저의 동작을 변경시킬 필요가 있다.

UPDATE a
SET
	a.AccountKey = b.AccountKey
,	a.BirthDT7 = b.BirthDT7
FROM dbo.Character a
	OUTER APPLY (
		SELECT TOP 1 AccountKey, BirthDT7
		FROM dbo.Dim_Customer 
		WHERE a.AccountID = AccountID
	) b
WHERE a.BirthDT7 IS NULL
OPTION(FAST 1) --힌트가 추가됨!!!

옵티마이저 변경 후에 다음의 실제 실행계획처럼 Index Spool이 없어진 것을 볼 수 있다. FIRST_ROWS_n의 흉내는 OPTION(FAST 100)과 같이 흉내내면 된다. Index Spool이 생기는지 생기지 않는지는 통계정보에 기반한 비용에 달려있으므로 환경에 따라 달라진다. 전체처리 비용이 중요한지 응답시간이 중요한지는 여러 가지 환경에 따라 다르다. 물론 옵티마이저가 100% 올바른 판단을 내려주지 않으므로 여러가지 시나리오와 테스트를 해보는 것이 중요하다. (1개의 문제에 1개의 솔루션만이 존재하는 것을 잊지 말자.)

[edit]

3 Distinct Count #

이렇게 하면 spool이 생기지 않는다.


select
    date_key
,   count(distinct id)
--,   sum(amt)
from table_a
group by
    date_Key

하지만, count(distinct ..)와 다른 집계함수가 섞이면 spool이 생긴다.


select
    date_key
,   count(distinct id)
,   sum(amt)
from table_a
group by
    date_Key

메모에서 처리하면 좋겠는데, 이를 CLR로 구현해 본 것이 있다. --> DistinctCount 요거 참고

여기를 보면 CTE를 이용한 솔루션이 distinct 나 group by보다 훨씬 비용이 적게 드는 것을 볼 수 있다. CTE가 훨씬 복잡한데 비용이 적다. 직관적이지는 않지만, 쩌러준다.

DROP    TABLE #test;
GO
CREATE  TABLE 
        #test 
        (
        data            INTEGER NOT NULL,
        );
GO
CREATE  CLUSTERED INDEX c ON #test (data);
GO
-- Lots of duplicated values
INSERT  #test WITH (TABLOCK)
        (data)
SELECT  TOP (5000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM    master.sys.columns C1,
        master.sys.columns C2,
        master.sys.columns C3;
GO





SET     STATISTICS TIME ON;

-- 1591ms CPU
SELECT  DISTINCT 
        data
FROM    #test;


-- 15ms CPU
WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.data)
        FROM    #test T
        UNION   ALL
        SELECT  R.data
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE :)
                SELECT  T.data,
                        rn = ROW_NUMBER() OVER (ORDER BY T.data)
                FROM    #test T
                JOIN    RecursiveCTE R
                        ON  R.data < T.data
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

SET     STATISTICS TIME OFF;
GO
DROP    TABLE #test;

[edit]

4 SQL Server의 Spool의 종류 #

Table Spool (Physical Operator)
Noclustered Index Spool (Physical Operator)
Eager Spool (Logical Operator)
Lazy Spool (Logical Operator)
Row count Spool (Physical Operator)

[edit]

5 Spool이 필요한 이유 #

spool이란 놈은 계륵인 듯..
tempdb에 쓰고, 읽기를 하는 비용이 꽤 많이 들기도 하지만..
반복적인 대량의 읽기가 발생할 경우는 동시성에 영향이 있으므로 spool 필요한데..
하지만 이런 양면성은 운영계(OLTP환경)에서만 발생한다라는 것이지..
정보계(DW)에서는 spool이 필요할까??

test

좋은 정보 감사합니다. -- 타락천사 2020-04-14 10:06:52

Contents

1 Spool의 개념 #

2 옵티마이저 모드에 따라서 Spool이 생길 수 있다 #

3 Distinct Count #

4 SQL Server의 Spool의 종류 #

5 Spool이 필요한 이유 #