SQL Server with Mr. Denny

Jan 22 2009   3:00PM GMT

Viewing SQL Server Plan Guides

Denny Cherry Denny Cherry Profile: Denny Cherry


So you’ve saved a plan guide into your SQL server, and you want to look at the plan.  You can Query the sys.plan_guides catalog view and see the data exactly as you entered it, which works fine if you just have a hint in there.  But if you have a full XML query plan that isn’t going to do you a lot of good since SSMS isn’t going to want to show you the all the XML as it is probably longer than it wants to display.

This little query should help you with that.  You may need adjust it slightly to account for extra spaces in your plan guide, I tried to account for double spaces, but if you have more than two spaces anywhere in the top part you’ll need to tweak it.

select cast(replace(replace(replace(hints, '  ', ' '), 'OPTION (USE PLAN N''', ''), ''')', '') as xml), *
from sys.plan_guides

This will give you XM that you can click on, which should open right up in SSMS as a query plan (if using SQL Server 2008, if you are using the SQL 2005 SSMS you’ll need to save the XML file and rename it to *.sqlplan and open it in SSMS).


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: