[MS-SQL] sp_executesql을 사용해서 동적 쿼리의 실행계획 재사용

출처: http://www.sqler.com/332104#4

동적 쿼리를 실행할 수 있는 방법에는 2가지가 있습니다.

첫번째는 EXEC()함수를 사용하는 것이고 다른 하나는 sp_executesql을 사용하는 방법입니다.

같이 동적 쿼리를 실행하는 방법이지만 약간의 차이가 있습니다.

EXEC()함수는 파라미터화 할 수 없기 때문에 쿼리의 일부가 조금만 바뀌어도 새로운 실행 계획이 캐싱됩니다.

반면 sp_executesql은 매개변수를 파라미터화 할 수 있기 때문에 쿼리문의 변경없이 변경해야 할 값만 파라미터로 만들어서 캐싱된 실행계획을 재사용 할 수 있습니다. 

------------------------------------------------------------

-- 테스트 환경

------------------------------------------------------------

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

------------------------------------------------------------

-- 테스트 테이블

------------------------------------------------------------

CREATE TABLE TAB_A

(

PK_COL INT NOT NULL

  , COL1 INT NOT NULL

  , COL2 INT NOT NULL

);

ALTER TABLE TAB_A ADD CONSTRAINT PK_TAB_A PRIMARY KEY (PK_COL);

GO

CREATE TABLE TAB_B

(

PK_COL INT NOT NULL

  , COL1 INT NOT NULL

  , COL2 INT NOT NULL

);

-- 조인에 사용될 재약 조건.

ALTER TABLE TAB_B ADD CONSTRAINT PK_TAB_B PRIMARY KEY (PK_COL);

ALTER TABLE TAB_B ADD CONSTRAINT FK_TAB_B FOREIGN KEY (PK_COL) 

REFERENCES dbo.TAB_A(PK_COL);

GO

------------------------------------------------------------

-- 테스트 쿼리

------------------------------------------------------------

DECLARE @SQL NVARCHAR(MAX);

DECLARE @PARAM_DEF NVARCHAR(MAX);

SET @SQL = 

N'SELECT A.COL1, A.COL2

 , B.COL1, B.COL2

  FROM dbo.TAB_A A

  JOIN dbo.TAB_B B

ON B.PK_COL = A.PK_COL

 WHERE A.COL1 = @VAL;';

 

SET @PARAM_DEF = N'@VAL INT';

DECLARE @I INT;

SET @I = 0;

-- 같은 쿼리가 캐싱되는지 확인하기 위해서 값만 바꿔가면서 반복 실행한다.

WHILE (@I < 10) 

BEGIN

        EXEC sp_executesql @SQL, @PARAM_DEF, @VAL = @I; -- 값만 바꿔서 넘겨준다.

DECLARE @PARAM NVARCHAR(MAX);

SET @PARAM = CAST(@I AS NVARCHAR(MAX));

EXEC (N'SELECT A.COL1, A.COL2

 , B.COL1, B.COL2

  FROM dbo.TAB_A A

  JOIN dbo.TAB_B B

ON B.PK_COL = A.PK_COL

 WHERE A.COL1 = ' + @PARAM + N';'); -- 값이 바뀌면서 쿼리문 전체가 바뀐다.

 

 SET @I = @I + 1;

END

------------------------------------------------------------

-- 캐싱된 실행 계획 조회.

------------------------------------------------------------

SELECT cp.objtype, st.text, cp.usecounts

  FROM sys.dm_exec_cached_plans cp

 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

 WHERE text LIKE '%TAB_A%' AND text LIKE '%TAB_B%';

결과를 확인하면 3가지로 분석을 할 수 있습니다.

첫째. 캐싱된 샐행 계획. EXEC()함수는 모든 값에 대해서 모두 한개씩 실행계획이 생성 되었습니다. 결국 실행 계획을 재사용 하지 못했습니다.. 반면에 sp_executesql은 하나의 실행 계획만 만들어 지고 나머지는 재사용 되었기 때문에 추가적으로 실행 계획이 생성되지 않았습니다.

둘째. 캐싱종류. EXEC()함수는 Adhoc쿼리로 실행계획이 등록 되었고, sp_executesql은 Prepared로 등록이 되었습니다..

셋째. 실생횟수(usecounts). EXEC()는 모든 값에따라 실행계획이 생성되었기 때문에 usecounts가 모두 1로 나옵니다.  반번에 sp_executesql은 시행 계획은 1개만 생성되고 값만 바꿔가면서 실행 계획이 재사용 되었기 때문에 usecounts가 10로 나옵니다.

결론

자주 실행해야 하는 쿼리중에 정적 쿼리로 만들기 힘든 쿼리를 동적 쿼리로 만들때는 sp_executesql을 사용해서 같은 실행 계획에 계속 생성되는 문제를 피하도록 해야 합니다.