in
Return to the SuperSite home pageFrequently asked questionsProduct reviewsTechnology showcases, commentaries, and other articlesHow to contact PaulWindows Vista Activity CenterXbox 360 Activity CenterMicrosoft Zune Activity CenterBlogsForumsForums   Jump to Paul Thurrott's Internet Nexus blogJump to Paul Thurrott's WinInfo Daily News
Microsoft Technet

IT Pro Tips


Plan Freezing with SQL Server 2008

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.

Comments

No Comments
SPONSORED LINKS FEATURED LINKS

Become a Response Point SpecialistEarn more with the small biz phone solution from Microsoft. Get Started with Oracle on Windows DVDLearn how Oracle gives you the power to grow by providing a scalable, easy-to-use platform for running your business at a price you can afford. Agent-less Remote Backup Service, Free 30 Day Trial Award winning remote backup service at a competitive price with no min GB/month. Sign up Now! EXCHANGE 2007 Mastery Series – May 29, 20083 Info-packed eLearning seminars for only $99! Learn the pros and cons of your mailbox high availability options, see real-world examples of Transport Rules, and get started with basic PowerShell commands with Mark Arnold, MCSE+M and Microsoft MVP. Windows IT Pro Master CD: Take the Experts with You!Find the solutions you need in thousands of searchable articles, helpful bonus content, and loads of expert advice with the Windows IT Pro Master CD. Order comes with a 1-year subscription to the new, online articles posted every day! SQL Server Magazine Master CD: Take the Experts with You!Find the solutions you need in thousands of searchable articles, helpful bonus content, and loads of expert advice with the SQL Server Magazine Master CD. Order comes with a 1-year subscription to the new, online articles posted every day! Attention User Group Leaders...Announcing the eNews Generator—a FREE HTML e-newsletter builder for user group leaders. Build your HTML and text e-newsletters in minutes. And add Windows IT Pro & SQL Server Mag articles alongside your own message!. Become a fan of Windows IT Pro on FacebookJoin the Windows IT Pro fan club on Facebook. Chat with other IT Pros, upload your pictures, check out what's up n' coming in the next issue and more! Tech·Ed 2008 Developer and IT ConferencesDon't miss out on the biggest event of the year. Be a part of the Microsoft Tech·Ed North America 2008 experience, starting June 3, 2008


Windows IT Pro   |   SQL Magazine   |   Microsoft Training and Certification   |   Connected Home   |   JSI FAQ   |   IT Library/eBooks   |   Supersite for Windows   |   Windows FAQ
WinInfo News   |   Windows IT Pro Europe   |   MSD2D   |   Windows Excavator

Subscribe / Register   |   About Us   |   Contact Us / Customer Service   |   Affiliates / Licensing   |   Press Room   |   Media Kit

Copyright © 2007 Penton Media, Inc., All rights reserved. Legal | Terms and Conditions