Home » Blog » Missing Graphical Showplan

Missing Graphical Showplan

2011-11-03 - General

There are a few queries agains the DMVs that return execution plans in XML format. This is an example that lists the 10 the execution plans currently in cache that take up the most memory:

[sql] SELECT TOP ( 10 )
cp.size_in_bytes ,
cp.refcounts ,
cp.usecounts ,
cp.cacheobjtype ,
cp.objtype ,
QUOTENAME(DB_NAME(qp.dbid)) + '.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(qp.objectid, qp.dbid)) + '.'
+ QUOTENAME(OBJECT_NAME(qp.objectid, qp.dbid)) AS object_name ,
qp.number ,
qp.encrypted ,
qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
ORDER BY cp.size_in_bytes DESC;
[/sql]

Starting with version 2008 of SQL Server Management Studio, a single click opens that plan as a graphical plan, rather than just showing the XML for it in a new window.This functionality has been a great help for many of us.

Since SP1 of SQL Server 2008 R2 this nice shortcut is broken, and it seems not to be high up on Microsofts list of things to fix. You can read all the details here:
Getting graphical ShowPlans back in SQL Server 2008 R2

There are several solutions mentioned in Aarons blog post. One involves hacking your installation, while another suggests to go back to the old way of saving the xml with a .showplan extension and then reopening it in SSMS.

For those of you that do not like either solution, Aaron mentions is a third solution. It involves a third party tool: SQL Sentry Plan Explorer.

This is a really great tool that shows a lot more information about the plans that SSMS. The best part about it is: It is free.

So, if you don't use Plan Explorer yet, try it out now to get your graphical plans back.