Query to look at the execution plans in cache

Here is a simple query using DMVs and DMFs to look at the cached execution plans on an instance in SQL Server:

SELECT  [sdecp].[refcounts]
,[sdecp].[usecounts]
,[sdecp].[objtype]
,db_name([dest].[dbid]) as database_name
,object_schema_name([dest].[objectid], [dest].[dbid]) as [schema_name]
,object_name([dest].[objectid], [dest].[dbid]) as [object_name]
,[dest].[text]
,[deqp].[query_plan]
FROM    sys.dm_exec_cached_plans sdecp
CROSS APPLY sys.dm_exec_sql_text(sdecp.plan_handle) dest
CROSS APPLY sys.dm_exec_query_plan(sdecp.plan_handle) deqp
where [dest].[dbid] is not null
order by database_name, usecounts desc

Using this query, you can look at the SQL that was called and the plan (in XML) that was generated by the optimizer.  One can then either just open up the XML or open it as a graphical execution plan.  In order to open it up as a graphical plan, you can save it as <filename>.sqlplan and then open it up in SQL Server Management Studio and it will open up as a graphical plan.