Third party applications that access the DB use SQL code that cannot be modified by the DBA. However, this SQL code will have to be run off the SQL server, and there is a way to optimize this code using plan guides. Query hints/plans are suggested to the SQL code (generated by the application), and may improve the performance of the application generated SQL code. The end result is to return the same data in a more efficient manner by tweaking the query. Plan guides are created using the sp_create_plan_guide procedure. The ‘hints’ specified are used to replace the original SQL statement in the hope of improving performance. Query plan guides can be used on SQL objects (SPs), SQL statements, and SQL templates.
I find this option very useful. Many a times, SQL written by our in house developers are not optimized. Plan guides provide me a way to test and prove that the SQL server is not slow due to a DBA deficiency.