Thought I'd answer one last question or two from the last time I did the talk on SQL Server 2005 plan guides. If you're asking at this point "what is a plan guide", reference the SQL Server 2005 BOL or this blog entry.
Q. Can you use a plan guide on an encrypted procedure? I want to put a hint on encrypted vendor-supplied code.
A. No, per books online you cannot create an OBJECT plan guide on any object that is itself encrypted or references an encrypted object. You get error message 10512 if you try.
Q. Can you create a plan guide on the SQL statement (rather than an OBJECT plan guide) on a SQL statement that I know exists as part of an encrypted procedure and have the guide be used?
A. You can create a SQL-type plan guide on a statement in a procedure, but it won't be used in any case; you need to create an OBJECT-type plan guide for it to be usable inside a procedure. You can confirm its "non-use" by looking at the showplan output from an un-encrypted procedure with a SQL-type plan guide; you can't see an XML showplan for statements in an encrypted procedure.
Q. Can I have more than one plan guide? For example to ENABLE/DISABLE different guides at different times of the day? Can I have two guides enabled at the same time?
A. You can't create more than one plan guide on a single statement/module-or-object combination, whether they are DISABLEd or not. You get error message 10502 if you try. You'd have to DROP the first plan guide before you can create the second one.
One thought on “Some questions and answers on plan guides”
Please check out http://blogs.msdn.com/sqlprogrammability/ for a series of articles on the Plan Cache and improvements made to the plan cache in SQL Server 2005 SP2. The articles also have a number of examples and queries on plan cache related DMVs that you may find useful.
Comments are closed.