출처: 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을 사용해서 같은 실행 계획에 계속 생성되는 문제를 피하도록 해야 합니다.

'개발자' 카테고리의 다른 글

[MS-SQL] MS-SQL 2008 SEND MAIL  (1) 2011.06.17
[MS-SQL] SQL Server의 시스템 오류 메시지 정보  (0) 2010.12.27
[MS-SQL] COMMIT TRANSACTION(Transact-SQL)  (0) 2010.12.27
MS-SQL 2008 Server  (0) 2010.09.17
[DB] DB정규화  (0) 2009.12.29
,