Database Reference
In-Depth Information
Listing 26-27.
Forcing XML Query Plan
-- Using USE PLAN query hint
select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = 'Germany'
option (use plan N'<?xml version="1.0"?>
<ShowPlanXML>
<!-- Actual execution plan here -->
</ShowPlanXML>');
go
-- Using Plan Guide
declare
@Xml xml = N'<?xml version="1.0"?>
<ShowPlanXML>
<!-- Actual execution plan here -->
</ShowPlanXML>'
declare
@XmlAsNVarchar nvarchar(max) = convert(nvarchar(max),@Xml)
exec sp_create_plan_guide
@type = N'SQL'
,@name = N'xml_plan_guide_demo'
,@stmt = N'select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = ''Germany'''
,@module_or_batch = NULL
,@params = null
,@hints = @XmlAsNVarchar;
While both the query hint and plan guide force SQL Server to use a specific execution plan, in SQL Server
2008 and above, they exhibit different behaviors when the plan becomes incorrect. Query Optimizer will ignore an
incorrect plan guide and generate the plan, as if the plan guide has not been specified. A query with
USE PLAN
hint, on
the other hand, would generate an error. An example of such an error is shown below. SQL Server 2005, however, fails
the query if an invalid plan guide is specified.
Msg 8712, Level 16, State 0, Line 1
Index 'tempdb.dbo.Employees.IDX_Employees_Country', specified in the USE PLAN hint, does not
exist. Specify an existing index, or create an index with the specified name.
■
Be careful when you change the schema of the objects referenced in plan guides and
USE PLAN
hints.
it is entirely possible to invalidate plans even when your changes do not directly affect the indexes and columns used
by a query.
Important
For example, unique indexes or constraints can eliminate some of the assertions in the plan and, therefore, invalidate
a plan when you dropped them. another common example is changes in partition schemas and functions.
