동일한 프로시저의 Plan이 여러개가 Cach에 올라가는 경우

Problem

동일한 프로시저인데, 어플리케이션에서 호출하여 실행할 때와 SSMS에서 직접 실행할 때 실행계획이 다르게 나타납니다. 원인을 찾기 위해 Plan Cach 에 등록되어 있는 프로시저의 Plan을 확인하고, 동일 프로시저가 왜 다른 Plan으로 올라가는지 확인을 하고자 합니다.

 

Solution

테스트에 사용 될 테이블을 생성을 하고, 해당 테이블에 인덱스를 만듭니다. 그리고 샘플 데이터를 넣습니다. 

 

[쿼리1]

CREATE TABLE TestTable(ID INT, NAME NVARCHAR(100)) 

CREATE CLUSTERED INDEX CLX_ID ON TestTable(ID) 

CREATE INDEX IDX1_NAME ON TestTable(NAME) 

INSERT INTO TestTable VALUES(1,'TEST') 
GO 1000 

INSERT INTO TestTable VALUES(2,'TEST') 
INSERT INTO TestTable VALUES(2,'MESSAGE')

위의 테이블에서 NAME이라는 필드의 값을 조건으로 데이터를 조회하는 프로시저를 샘플로 사용할 것입니다. 프로시저를 생성합니다.

[쿼리2]

CREATE PROC dbo.Plan_Test 
@NAME NVARCHAR(100) 
AS 
SELECT * FROM [dbo].TestTable WHERE NAME=@NAME

그리고 난 후 "MESSAGE"라는 값으로 조회 하도록 하겠습니다.

 

[쿼리3]

EXEC Plan_Test 'MESSAGE'

그리고 아래 쿼리를 이용하여 샘플(Plan_Test) 프로시저의 실행에 사용된 Plan을 조회하도록 하겠습니다.

 

[쿼리4]

SELECT QUERY_PLAN, 
	CP.USECOUNTS, 
	DB_NAME(PS.database_id) AS DBNAME, 
	OBJECT_NAME(ps.object_id) AS SP_NAME, 
	ps.cached_time, 
	ps.last_execution_time as last_run_time, 
	ps.execution_count as exec_cnt, 
	ps.total_worker_time/execution_count as avg_cpu_time, 
	ps.total_logical_reads/execution_count as avg_io_reads, 
	ps.total_elapsed_time/execution_count as avg_exec_time, 
	ps.total_worker_time as tot_cpu_time, 
	ps.total_logical_reads as tot_io_reads, 
	ps.total_elapsed_time as tot_exec_time 
FROM sys.dm_exec_cached_plans CP 
LEFT JOIN SYS.dm_exec_procedure_stats PS ON CP.plan_handle = PS.plan_handle 
OUTER APPLY sys.dm_exec_query_plan(CP.PLAN_HANDLE) TP 
WHERE OBJECT_ID = OBJECT_ID('Plan_Test')

[결과1]

위의 쿼리의 결과로 아래와 같은 데이터가 조회가 될 것입니다.

위의 [결과1]은 SSMS의 도구->옵션->Query Execution -> ANSI 에서 아래와 같은 옵션으로 실행했을 때의 결과 값입니다.

[옵션1]

위의 [옵션1]를 [옵션2]로 변경 후 SSMS에서 새 쿼리창을 실행하여 [쿼리3]를 재 실행하겠습니다.

[옵션2]

[쿼리4]를 통해 Cash에 "Plan_Test" 프로시저의 실행계획이 올라갔는지 보도록 하겠습니다.

 

[결과2]

[결과2]를 보면, 동일한 Procedure를 동일하게 실행했는데, Cash에는 두개의 Plan이 등록 된 것을 볼 수 있습니다.

[쿼리5]

SELECT * FROM sys.dm_exec_sessions WHERE session_id in (56,57) 
-- 쿼리[3]을 실행한 두 세션 번호

[쿼리5]를 통해 두 세션을 비교해 보면 실행계획에 ANSI_PADDING / ANSI_WARNINGS / ANSI_NULLS 이 영향을 미친것을 확인 할 수 있습니다.

이와 같이 실행계획에 영향을 미칠 수 있는 옵션은 다음과 같습니다.

http://msdn.microsoft.com/ko-kr/library/ms175088(v=SQL.90).aspx

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS*

ARITHABORT

CONCAT_NULL_YIELDS_NULL

NUMBERIC_ROUNDABORT

QUOTED_IDENTIFIER

 

위에서 기술했던 것과 같은 문제가 실제 업무에서도 발생했습니다.

발생했던 쿼리의 실행계획의 차이를 보여주는 화면입니다.