By Ronald Barrett
With the advent of SQL Server 2008, many DBAs worry about issues that sometimes arise with updating query plans during the upgrade process. Plan Freezing allows you to store plan guides, which can be reverted to if queries either fail or drain resources after the upgrade. Plan Freezing allows stability to queries and comes with several monitoring features to check if the query succeeded or failed.
In the TechNet Virtual Lab: Plan Freezing with SQL Server 2008 we will see how to prevent query plan regression after a server upgrade, use plan freezing to optimize query performance, and copy query plans between servers. Before proceeding with the lab, Microsoft suggests that you be familiar with the SQL Server Management Studio (SSMS) and with Transact- SQL (T-SQL) Programming.
This virtual lab will teach us to:
Prevent Query Plan Regression
Optimize Query Performance
Copy Query Plans between Servers
TIP: Within the console, there are text instructions for each exercise. These contain copy commands for the T-SQL programming to simplify the tasks. Just copy and choose "paste text from host's clipboard" from the action menu in the console.
Exercise 1
In the first exercise, we will generate a plan script onto a test server, deploy the script to a production server, verify the query plan on the production server, and finally, generate a database clone.
We can see how plans are disabled…
And then enabled again for the CustomerSales Script…
Exercise 2
In the second exercise, we run mission critical queries and create plan guides for them, inspect the execution plan of the guide, remove records and confirm that the same execution plan is being used, and then we will disable the plan.
You can see the results of the execution plan in Exercise 2:
In addition, you will compare the three results to see what has changed in each scenario.
Exercise 3
In our final exercise, we generate a plan script on a test server, deploy it to a production server, verify the query plan is on the production server, and finally, generate a database clone.
We see in Exercise 3 how to copy plans from a test server to a production server.
Finally, we clone the database, and the lab ends with us seeing the successful results of our work.
Plan Freezing with SQL Server 2008 is a great Virtual Lab to see how to optimize query plans, move them from test server to production servers, and finally clone the plans amidst several SQL Servers if needed. I hate to admit it because most times I have to deal with T-SQL it is never much fun, but I enjoyed this lab a lot and would recommend that it is worth the time to get an overview of Plan Freezing whether it is your primary role to handle this job function or not.