DB/MSSQL

[MsSQL] sp_executesql 을 사용하여 동적 쿼리의 실행계획을 재사용하기

벨포트조던 2021. 8. 17.
반응형

동적 쿼리를 실행할 수 있는 방법은 EXEC 와 EXEC sp_executesql 을 사용하는 2가지 방법이 있습니다.

 

EXEC 방식은 동적 쿼리의 매개변수 일부만 바뀌더라도 매번 컴파일이 되지만,

sp_executesql 방식은 매개변수를 파라미터화 할 수 있기 때문에 쿼리문 자체의 변경이 없다면 캐싱된 실행계획을 재사용할 수 있습니다.

 

sp_executesql에 관해서는 아래 url을 참고해주세요.

https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

 

 

테스트를 진행해봅니다.

 

1. 테스트에 사용될 테이블을 생성합니다.

CREATE TABLE dbo.A
(
PK_COL  INT PRIMARY KEY
  , COL1 INT
  , COL2 INT
);




CREATE TABLE dbo.B
(
PK_COL INT PRIMARY KEY
  , COL1 INT
  , COL2 INT
); 

 

2. 같은 쿼리에 대해 EXEC 방식과 sp_executesql 방식을 사용해서 테스트를 진행합니다.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL =
N'SELECT A.COL1, A.COL2
 , B.COL1, B.COL2
  FROM dbo.A A
  JOIN dbo.B B
ON B.PK_COL = A.PK_COL
 WHERE A.COL1 = @VAL;';


DECLARE @PARAM_DEF NVARCHAR(MAX);
SET @PARAM_DEF = N'@VAL INT';


DECLARE @I INT;
SET @I = 1;
WHILE (@I <= 5) 
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.A A
  JOIN dbo.B B
ON B.PK_COL = A.PK_COL
 WHERE A.COL1 = ' + @PARAM + N';');
 
 SET @I = @I + 1;
END 

 

3. EXEC와 sp_executesql의 플랜 실행 횟수를 비교합니다.

 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 '%dbo.A%' AND text LIKE '%dbo.B%';

 

 

 

결론

EXEC 방식은 Adhoc, sp_executesql 방식은 Prepared으로 objtype에 등록되었습니다.

EXEC방식은 모든 매개변수에 대해 count가 1. 즉, 실행계획을 재사용하지 못한 반면,

sp_executesql방식은 모든 매개변수에 대해 실행계획을 재사용 하는 것을 확인할 수 있습니다.

반응형

댓글