1 개요

2 구현해보기

Query Plan은 수시로 변경된다. SP도 데이터가 변경됨에 따라서 가끔씩 재컴파일[1]을 한다. 또한 옵티마이저가 대부분은 최적화된 실행계획을 세워준다. 하지만 관리자의 개입이 필요한 경우가 종종있다. 만약 관리자가 개입을 해도 계속적으로 쿼리 플랜이 변경된다면 규칙기반 옵티마이저처럼 아예 실행계획을 고정시켜 놓는 기능이 SQL Server 2005에 추가되었다. 이걸 'Query Plan Freezing' 이라고 하는 것 같다. 도움말에는 '계획 지침'으로 풀이되고 있다. Edition별 제약사항도 있다. 다음은 도움말의 내용이다.

계획 지침은 SQL Server Standard, Developer, Evaluation 및 Enterprise 버전에서만 사용할 수 있지만 보기는 모든 버전에서 가능합니다. 계획 지침이 포함된 데이터베이스를 모든 버전에 추가할 수 있습니다. 업그레이드된 버전의 SQL Server 2008에 데이터베이스를 복원하거나 첨부해도 계획 지침은 그대로 유지됩니다.

ㅡㅡ;; 씨발 먼소리야?? 보기는 모든 버전에서 가능하다?

먼저 쿼리를 실행하여 캐시에 플랜을 만들도록 하자.

USE AdventureWorks2008
GO

--쿼리실행
SELECT * FROM Person.Address
WHERE City = 'Bothell'

--확인
SELECT * FROM sys.dm_exec_query_stats qs
	CROSS APPLY sys.dm_exec_sql_text(sql_handle)
	CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp
WHERE text LIKE N'SELECT * FROM Person.Address
WHERE City = ''Bothell''%'
GO

다음으로 플랜 가이드를 작성해 보자.

DECLARE @plan_handle varbinary(64)
DECLARE @offset int

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats qs
	CROSS APPLY sys.dm_exec_sql_text(sql_handle)
	CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp
WHERE text LIKE N'SELECT * FROM Person.Address
WHERE City = ''Bothell''%'
	
EXEC sp_create_plan_guide_from_handle --CTP버전에는 sp_create_plan_guide_from_cache로 나온다.
@name = N'CustomerSales',
@plan_handle = @plan_handle,
@statement_start_offset = @offset	
GO

활성화/비활성화 시키기

EXEC sp_control_plan_guide N'DISABLE', N'CustomerSales'
GO

SELECT * FROM sys.plan_guides
WHERE is_disabled = 1 --disable된 것들..
GO

EXEC sp_control_plan_guide N'ENABLE', N'CustomerSales'
GO