[MSSQL] sp_prepexec 사용법과 실행계획 재사용 여부 확인

sp_prepexec 란 

---

매개변수가 있는 트랜잭션을 실행할 수 있게 해줍니다.

미리 컴파일 된 실행계획을 재사용할 수 있습니다.

자세한 사용법은 아래 URL 을 참고해주세요.

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

실행계획 재사용 테스트

---

테스트 SP 생성합니다.

@isvar 매개인자값에 따라 결과가 달라집니다.

CREATE PROCEDURE [dbo].[nxp_test01]
(
@isvar tinyint,
@result varchar(10) = null OUTPUT
)
AS
set nocount on
set rowcount 0
 
if @isvar = 1
begin
  SELECT TOP 1 @result = C1 FROM dbo.T1 WITH(NOLOCK) where C1 is not null
end
else
begin
  SELECT TOP 2 @result = C1 FROM dbo.T1 WITH(NOLOCK) where C1 is not null
end
set nocount off
GO

실행계획을 비워줍니다.

dbcc freeproccache

로컬서버에서 sp_prepexec 를 사용하여 SP 를 호출합니다.

- @isvar 의 매개인자값을 0 과 1로 구분지어 반복 호출합니다.

- 여러 세션에서 실행계획을 재사용 할 수 있는지 수행해봅니다.

DECLARE @out INT
DECLARE @p1 VARCHAR(10)
EXEC sp_prepexec @out output, N'@p0 tinyint, @p1 VARCHAR(10)', N'EXEC dbo.nxp_test01 @p0, @p1 OUTPUT; SELECT @p1', 0, null
SELECT @out
EXEC sp_unprepare @out
go
 
 
DECLARE @out INT
DECLARE @p1 VARCHAR(10)
EXEC sp_prepexec @out output, N'@p0 tinyint, @p1 VARCHAR(10)', N'EXEC dbo.nxp_test01 @p0, @p1 OUTPUT; SELECT @p1', 1, null
SELECT @out
EXEC sp_unprepare @out
go
 

실행계획을 확인합니다.

실행계획이 재사용 되었습니다.

SELECT TOP 50
  databases.name,
  dm_exec_sql_text.text AS TSQL_Text,
  dm_exec_query_stats.creation_time,
  dm_exec_query_stats.last_execution_time,
  dm_exec_query_stats.execution_count,
  dm_exec_query_stats.total_worker_time AS total_cpu_time,
  dm_exec_query_stats.total_elapsed_time,
  dm_exec_query_stats.total_logical_reads,
  dm_exec_query_stats.last_logical_reads,
  dm_exec_query_stats.total_logical_writes,
  dm_exec_query_stats.last_logical_writes,
  dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats WITH(NOLOCK)
  CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
  CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
  INNER JOIN sys.databases WITH(NOLOCK)
  ON dm_exec_sql_text.dbid = databases.database_id
WHERE databases.database_id = DB_ID('AdventureWorks')
ORDER BY dm_exec_query_stats.creation_time desc
OPTION (MAXDOP 1)

다른 세션과 원격 서버에서 sp_prepexec 를 사용하여 SP 호출합니다.

- 여러 세션에서 실행계획을 재사용 할 수 있는지 수행해봅니다.

- 다른 서버에서 접속하여 수행해봅니다. (로컬서버에서 수행하지 않고)

동일하게 실행계획은 재사용 되었습니다.