The wonders of USE PLAN in SQL Server 2005

There's a great new feature in SQL Server 2005 called USE PLAN.  If I understand it correctly, USE PLAN allows the substitution of one execution plan for a given query with another of your own design.  This is especially exciting for those of you who may be suffering from poorly coded third-party applications where you cannot change the SQL of the procedures, triggers, views, and functions within the product.

To use the statement, you tell it what query you want the plan guide to look for. (The plan guide is the execution plan that you wish to be substituted with one of your own definition.)  Thus, your plan guide will trump any other query modification such as isolation level changes or hints because it completely replaces the execution plan generated by the SQL Server query engine.

The USE PLAN feature is invoked with the sp_create_plan_guide stored procedure.

Note that you can only force a plan that would be in the normal realm of possible alternatives for the query.  Thus, if the query doesn't do any sort of JOIN, you can't make it do one.  Additionally, you can't use the feature to force wrong results or to avoid normal security checks.  Security checks, in particular, occur before plan forcing in the course of query processing.




Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.