\nTo round out the new plan guide-related features in SQL Server 2008, there is a new way to express a table hint that increases the plan guide's reach.\n<\/p>\n
\nThe sp_create_plan_guide procedure requires a hint as the last parameter. This can be in a form OPTION (hint), just an XML query plan (in SQL Server 2008 "OPTION (USE PLAN)" isn't required), or NULL. Specifying NULL can be used to "subtract" a hint from an existing query where the hint is hardcoded in source code you don't have the ability to change.\n<\/p>\n
\nThis requirement limited plan guides to hints that could be used in an OPTION clause, and table hints didn't qualify because they are inline. In SQL Server 2008 this limitation is removed. You can specify a table hint by using the syntax OPTION (TABLE HINT (table_alias, hint)). This makes table hints usable in plan guides. You can even specify multiple hints on a per-table basis.\n<\/p>\n
\nOPTION (TABLE HINT (table_alias1, hint1), (table_alias2, hint2)…)\n<\/p>\n
\nBOL indicates in a few places that this syntax exists specifically to make table hints accessible to plan guides. Cool.\n<\/p>\n
\nAnother use for an analogous syntax that's not in SQL Server 2008 would be to be able to specific per-table JOIN hints the same way. You can currently specify only one JOIN hint in the option clause. An analogous way to specify multiple, per-join JOIN hints in an OPTION clause would be nice. But, if you're controlling the plan that closely, perhaps its best to resort to plan forcing.\n<\/p>\n
\nBTW, manditory disclaimer: Hints are (usually) evil and should be used only as a last resort. Using a USE PLAN hint seems, at least to me, similar to writing custom navigation code for each query in IMS\/DB, as I did in the '80s. You're throwing the query algebrizer\/optimizer away…or at least pinning its hands.<\/p>\n","protected":false},"excerpt":{"rendered":"
To round out the new plan guide-related features in SQL Server 2008, there is a new way to express a table hint that increases the plan guide's reach. The sp_create_plan_guide procedure requires a hint as the last parameter. This can be in a form OPTION (hint), just an XML query plan (in SQL Server 2008 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,29],"tags":[],"class_list":["post-731","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server-2008"],"yoast_head":"\n