MS SQL Srver 2008 For DBA : Using Plan Freezing -Exercise 4

                              Using Plan Freezing
Scenario
In this exercise, you will create a plan guide and test the effect it has on query plans when you enable and disable it.Sudden and unexpected changes in query plans can cause unpredictable performance. This causes particular problems for independent software vendors and with applications that use SQL Server as their database engine. By creating plan guides and disabling them, you enable queries to benefit from statistics-based recompiles, but you can enable the plan guide at any time if the query regresses in production.
Tasks Detailed Steps Complete the following task on:

1. Run mission critical queries and create a plan guide for them
a. In SQL Server Management Studio, open the OptimizePerformance.sql script from
the C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing folder.
b. Review the code in this script, which runs a query and then creates a plan guide for
the query plan generated by the query processor.
c. Execute the script.
d. Wait until the query has completed, and then open and execute the
ViewPlanGuides.sql script from the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing folder to confirm that a
plan guide with the name MissionCritical has been created and enabled (any
previous plan guides for the same statement are disabled automatically).
USE AdventureWorks
GO
SELECT * FROM sys.plan_guides

2. Inspect the execution plan that the query uses
a. Open the CustomerSales.sql script from the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing folder, click the Include
Actual Execution Plan button, and then click Execute.
b. When the query has completed, click the Execution Plan tab to view the execution plan.
c. Right-click anywhere in the execution plan, and then click Save Execution Plan
As. Save the execution plan as
C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing\FullData.sqlplan.

3. Remove records and confirm that the same execution plan is used
a. Open the DeleteSales.sql script from the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing folder, and then execute
it to delete a substantial number of sales from the database.
b. Return to the CustomerSales.sql script, ensure that the Include Actual Execution
Plan button is clicked, and click Execute.
c. When the query has completed, click the Execution Plan tab to view the execution
plan.
d. Right-click anywhere in the execution plan, and then click Save Execution Plan
As. Save the execution plan as
C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing\ReducedData.sqlplan.
e.
4. Disable the plan
a. Execute the ViewPlanGuides.sql script to verify that the MissionCritical plan
guide is enabled (the is_disabled column should contain the value 0).
b. Open the DisablePlanGuide.sql script from the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing folder, and then execute
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Tasks Detailed Steps it to disable the MissionCritical plan guide.
Note: By using sys.plan_guides and sp_control_plan_guide, you can capture a
history of query plans and revert to a previous plan if your current plan is not performing optimally.
c. Return to the CustomerSales.sql script, ensure that the Include Actual Execution
Plan button is clicked, and click Execute.
d. When the query has completed, click the Execution Plan tab to view the execution plan.
e. Right-click anywhere in the execution plan, and then click Save Execution Plan
As. Save the execution plan as
C:\SQLHOLS\WhatsNewForDBAs\Starter\PlanFreezing\WithoutPlan.sqlplan.
f. Open the three *.sqlplan files that you have saved, and then compare them.
Note: The execution plans for ReducedData.sqlplan and WithoutPlan.sqlplan differ
because ReducedData.sqlplan used the MissionCritical plan guide and
WithoutPlan.sqlplan did not.
g. Close all of the open query windows, but keep SQL Server Management Studio
open for the next exercise.