안녕하세요 SMILEYOON입니다.
지난 주 DBNuri Study 시간에 언급되었던 내용을 테스트 해 보았습니다.
질문)하나의 프로시저는 하나의 쿼리 계획만 가질까요?
정답) 아닙니다. 한개일 수도 있고 여러개일 수도 있습니다.
이제부터 직접 눈으로 확인해 보도록 하겠습니다.
--TEST DB 생성
CREATE DATABASE TEST
GO
--TEST DB 선택
use test
go
--테이블 생성
CREATE TABLE testtbl
( num int identity,
name varchar(50)
)
go
--데이터 입력
insert testtbl values ('smileyoon')
go 10000
--저장된 프로시져 캐시 삭제
DBCC freeproccache
go
IF object_id('smileyoon') is not null
drop procedure smileyoon
go
CREATE PROCEDURE SMILEYOON
AS
SELECT * FROM [dbo].[testtbl]
go
--2번 실행
EXEC SMILEYOON
go
--재 사용된 쿼리 계획 정보를 확인하는 DMV
--내가 실행했던 프로시저의 Plan_handle을 복사해 놓자
SELECT usecounts, cacheobjtype, objtype, text, plan_handle
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1 AND objtype = 'Proc'
ORDER BY usecounts DESC;
GO
=>쿼리 계획이 2번 반복 사용되었다.
--각 계획이 컴파일될 때 사용한 옵션을 나타내는 값을 확인하는 DMV
SELECT plan_handle, pvt.set_options, pvt.sql_handle
FROM (
SELECT plan_handle, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO
--현재 연결된 SET 옵션값 확인하는 명령어
DBCC USEROPTIONS
go
--SET 옵션 값 변경
SET ansi_warnings off
go
--처음 만들었던 프로시저 실행 (2번 실행하기)
EXEC SMILEYOON
go
--재 사용된 쿼리 계획 정보를 확인하는 DMV
SELECT usecounts, cacheobjtype, objtype, text, plan_handle
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1 AND objtype = 'Proc'
ORDER BY usecounts DESC;
GO
=>동일한 프로시저를 실행했지만, 현재 연결된 SET 옵션 값의 차이에 따라 다른 계획을 만들어 사용하고 있습니다.
질문이 있습니다.!!!
서로 다른 Application 에서 쿼리를 실행하여 데이터를 수집할 경우가 있는데 이럴 경우 명시적으로 SET Option을 지정하는게 맞는 걸까요???? DBA 입장에서 한개의 플랜을 만들어 그것만 사용하게 하고 싶을 것이고 이번 테스트와 같이 SET 옵션 값에 따라 쿼리가 다르게 풀린다면 Application 개발자들에게 SET 옵션을 명시적으로 해야 한다고 가이드 하는게 맞을 것 같은데 고수님들의 생각은 어떠신가요?
[참고 사이트]
sys.dm_exec_cached_plans(Transact-SQL)
http://technet.microsoft.com/ko-kr/library/ms187404.aspx