Detailed SQL Server 2008 Migration Query plan

In most cases, upgrading your database to SQL Server 2008 can improve query performance. However, if you have mission-critical queries that have been carefully optimized for performance, it is a good idea to create a planning guide for each query before you upgrade to keep the query plans for those queries. If, after the upgrade, the query optimizer has selected a less efficient plan for one or more queries, you can enable these planning guides and force the query optimizer to use the pre-upgrade schedule.

To create a planning guide before you upgrade, follow these steps:

Record the current schedule for each task-critical query by using the sp_create_plan_guide stored procedure and specifying the query plan in the use plans query prompt.

Verify that the plan guide is applicable to this query

Upgrade the database to SQL Server 2008.

Plan guides that are scheduled to remain in the upgraded database will be used as fallback plans if the planned performance has regressed after the upgrade.

It is recommended that you do not enable the Planning Guide after the upgrade because the statistics are updated, and you may miss the benefits of better planning or recompilation in the new version.

If you select a less efficient plan after the upgrade, you can activate all planning guides or part of the Planning Guide to replace the new plan.

Example

The following example shows how to create a plan guide to record the schedule before the upgrade for a query.

Step 1: Collect the Plan

The query plan that is documented in the Planning Guide must be in XML format. You can generate a query plan in XML format in the following ways:

SET　SHOWPLAN_XML　
SET　STATISTICS　XML

Query sys.dm_exec_query_plan Query_plan columns for dynamic management functions.

SQL Server Profiler Showplan XML, Showplan XML Statistics profile and Showplan XML for Query Compile event classes.

The following example collects statements by querying dynamic management views

SELECT　City,　StateProvinceID,　PostalCode　FROM　Person.Address　ORDER　BY　PostalCode　DESC;

的查询计划。

USE　AdventureWorks;
GO
SELECT　query_plan
FROM　sys.dm_exec_query_stats　AS　qs
CROSS　APPLY　sys.dm_exec_sql_text(qs.sql_handle)　AS　st
CROSS　APPLY　sys.dm_exec_text_query_plan(qs.plan_handle,　DEFAULT,　DEFAULT)　AS　qp
WHERE　st.text　LIKE　N'SELECT　City,　StateProvinceID,

PostalCode from person.address to PostalCode desc;% ';

Go