temp table 사용시 recompile 발생 없이 사용하기

- 아래와 같이 sp 내에서 TEMP 테이블을 포함하는 동적 쿼리 실행시 생성 주체와 실행 주체가 달라 COMPILE 이 발생합니다.

  (compile은 서버 비용중 큰 비중을 차지하고, 쿼리 성능 저하의 원인중 하나 입니다.)

 

alter proc test_recompile_in
as

CREATE TABLE #TEST
(
A INT
)
exec SP_EXECUTESQL N'
INSERT INTO #TEST
SELECT TOP 10 SEQ FROM TEST1M

 

 

* 실행할때마다 compile 발생

- 아래와 같이 동적 쿼리 바로 위에 insert 문을 넣어주면 동적 쿼리내에서 스키마 변경이 없어지므로 compile 발생이 없어 지고, 기존 플랜을 재활용 하므로 

   서버 리소스도 절약하고, 쿼리 성능도 향상 됩니다.

 

ALTER proc [dbo].[test_recompile]
as

CREATE TABLE #TEST
(
A INT
)
INSERT INTO #TEST
exec SP_EXECUTESQL N'
SELECT TOP 10 SEQ FROM TEST1M

 

[recompile 방법]

1. sp_recompile '프로시저명'

2. with recompile

4. option(recompile)

exec USP_ORDERS_RECOMPILE_PROC WITH RECOMPILE -- 플랜이 재생성 되지 않음

CREATE PROC USP_ORDERS_RECOMPILE_PROC
(
  @PRODUCT_ID CHAR(6)
)
WITH RECOMPILE -- 프로시저가 매번 다시 컴파일 되도록 합니다.
AS
  SET NOCOUNT ON;

  SELECT ORDER_DATE, QUANTITY
    FROM dbo.T_ORDER
    WHERE PRODUCT_ID = @PRODUCT_ID;
GO

CREATE PROC USP_ORDERS_RECOMPILE_QUERY
(
  @PRODUCT_ID CHAR(6)
)
AS
  SET NOCOUNT ON;

  SELECT ORDER_DATE, QUANTITY
    FROM dbo.T_ORDER
    WHERE PRODUCT_ID = @PRODUCT_ID
  OPTION (RECOMPILE); -- 이 쿼리에 대해서만 실행계획을 재생성 합니다.
GO

 

컴파일된 플랜 확인하기

select creation_time
,       last_execution_time
,       (total_worker_time +0.0)/ 1000 as total_worker_time
,       (total_worker_time +0.0)/( execution_count*1000 ) as [AvgCPUTime]
,       total_logical_reads as [LogicalReads]
,       total_logical_writes as [logicalWrites]
,       execution_count
,       total_logical_reads+ total_logical_writes as [AggIO]
,       (total_logical_reads +total_logical_writes)/( execution_count + 0.0) as [AvgIO]
,   case when sql_handle IS NULL
                then ''
                else ( substring(st .text,( qs.statement_start_offset +2)/ 2,(case when qs.statement_end_offset = -1     
                                     then len(convert (nvarchar( MAX),st .text))* 2    
                                     else qs .statement_end_offset    end - qs .statement_start_offset) /2  ) )
        end as query_text
,       db_name( st.dbid ) as database_name
,       st. objectid as object_id
,       object_name( st.objectid )
,           p.query_plan
from sys .dm_exec_query_stats  qs
cross apply sys. dm_exec_sql_text(sql_handle ) st
CROSS APPLY SYS. DM_EXEC_QUERY_PLAN(qs.plan_handle) AS P