Viewing SQL Server Plan Guides

Published On: 2009-01-22By:

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).

Denny

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via