Contents

1
2 蟲企慨蠍


1 #

Query Plan 襦 覲蟆暑. SP 一危郁 覲蟆暑 磯殊 螳 貉危[1] . 牛磯伎螳 覿覿 豕 ろ螻 語譴. 讌襷 蟯襴 螳 蟆曙郁 譬譬. 襷 蟯襴螳 螳 企 螻朱 貎朱Μ 覲蟆暑る 蠏豺蠍磯 牛磯伎豌 ろ螻 螻貅 蠍磯レ SQL Server 2005 豢螳. 願姥 'Query Plan Freezing' 企手 蟆 螳. 襷 '螻 讌豺'朱 企螻 . Edition覲 曙 . れ 襷 伎企.

螻 讌豺 SQL Server Standard, Developer, Evaluation 覦 Enterprise 覯襷 讌襷 覲願鍵 覈 覯 螳ロ. 螻 讌豺 一危磯伎るゼ 覈 覯 豢螳 給. 蠏碁企 覯 SQL Server 2008 一危磯伎るゼ 覲旧蟇磯 豌覿企 螻 讌豺 蠏碁襦 讌.
<;; 覦 襾殊襴?? 覲願鍵 覈 覯 螳ロ?

2 蟲企慨蠍 #

襾殊 貎朱Μ襯 ろ 貂 襷る襦 .
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