Query plan guides in the SQL Server 2005 BOL

Just catching up on my blogging before a little vacation next week.

Browsing through the SQL Server BOL from the December CTP, I came across some information on something called "plan guides". There is info on some stored procedures that create and manage plan guides, a database option (in ALTER DATABASE) and a system view that lists plan guides. However, none of this these are active yet in the actual product. Let's hope this is another example of documentation being ahead of things (a la EXCEPT and INTERSECT support), because these sound interesting. According to BOL…

A plan guide is a database object that associates query hints with certain queries in the database. You can create a plan guide (using sp_createplanguide) for a SQL statement or batch. The statement can be standalone or specified to be part of a certain stored procedure. The plan guide specifies an OPTION clause specifying query hints to be applied whenever the statement is executed.

Plan guides must first be "enabled" on in a database (using ALTER DATABASE) before they can be used. Then you turn them "on and off" by sp_controlplanguide enable/disable. When a matching query is detected the hints are automatically “put in place“.

Sounds VERY cool for query plan afficianados. You can have configurable query hinting without touching your queries in the application code. And turn it on or off at will. Only thing is, NONE of it works in the December CTP. Any of the stored procedures produce "not found" message, as does the ALTER DATABASE keyword and the system view. Maybe the BOL IS a little ahead again.

2 thoughts on “Query plan guides in the SQL Server 2005 BOL”

  1. Hmm .. I can’t find the relevant documentation around this, so I’ll ask you here instead.

    How do you specify that QUERY A uses hints, and QUERY B doesn’t – especially when these queries aren’t even written yet (Dynamic/Embedded Sql). ?

  2. My impression is that you can turn these on and off at any time, but they are per-query. The documentation is incomplete on this as you say (actually the docs are ahead of the feature) so we’ll all just have to see. I’ll let you know, Sahil, if I find out before you do.

    Cheers.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course