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

동적 쿼리를 실행할 수 있는 방법은 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방식은 모든 매개변수에 대해 실행계획을 재사용 하는 것을 확인할 수 있습니다.