SQL Server 2005 standard query tuning options - plan guide for performance testing how to?

You may be aware that plan guides will help the optimizer to perform better and a general recommendation is to use the relevant USE PLAN query hint when the other standard query tuning options are failed. It is also necessary to test through the results you would expect are not upto the mark, when using indexing tuning wizard or by updating statistics.

Find the best practices in using the plan guides appropriately, also you should be considered about implications and long-term ramificiations in forcing the query plans and their options. By default when a query plan is forced by using either the USE PLAN query hint or a plan guide, it gets locked down and prevents the optimizer from adapting to changing data distributions, new indexes, improved query execution algorithms in successive SQL Server releases, service packs (SPs), and HotFixes (engineering fixes), scary isn't it!

When testing (if possible) try to force/test on the small fraction of query workload results, on a larger result set it might produce limiting performance and insufficient system resources. Also make sure to note down the server & database configuration settings to test it out with various other options. As referred the insufficient system resources includes configuration settings, missing indexes or poorly written queries. Only capture or use the plan produced when USE PLAN hint is supplied while testing.

Never embed or specify the USE PLAN query hint in any of your application, if so this it will definetly get down the SQL Server resources availability to zero. As you are aware stored procedure plan and other query plans are cached as and when necessary, by using the above hint it will not cache and may be the changes to server setting are difficult to manage. By desing when you include the USE PLAN directly into the query also generally makes the plan for the query not cacheable. So you might be understood by now that this hint is to use with the Plan Guides feature only.

Using SET SHOWPLAN_XML ON you could get huge list of show plan xml listing procedure, that can be used to document the plan as a part of your testing. Also by using the pre-supplied stored procedure as referred below you could get complete information on plan guide: 

sp_create_plan_guide 
@name = N'PlanGuide3', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY  OrderDate DESC   

BOL refers that plan guides are created and administered by using the following two system stored procedures:

• sp_create_plan_guide
 
• sp_control_plan_guide