Finding Bottlenecks in SQL Server XML and Graphical Execution Plans

A 40-operator plan is hard to scan visually. Every graphical plan is backed by XML you can query directly — genuinely useful once plans get wide.

Getting the Raw XML

SET SHOWPLAN_XML ON;
GO
SELECT * FROM dbo.OrderLog o JOIN dbo.Customer c ON o.customer_id = c.customer_id;
GO
SET SHOWPLAN_XML OFF;
-- Or right-click a graphical plan in SSMS -> "Show Execution Plan XML"

Searching for Specific Problems

Once you have the XML, search (Ctrl+F in SSMS’s XML view, or programmatically) for these telltale strings:

Finding This Programmatically Across the Plan Cache

This CROSS APPLY pattern is the exact same tool from the Developers & DBAs course’s window-functions chapter, now aimed at sys.dm_exec_cached_plans instead of a business table — the same skill, a new target.

SELECT TOP 20 qp.query_plan, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%SpillToTempDb%';

This finds every cached plan currently spilling to tempdb — a genuinely powerful way to proactively hunt for memory-pressure problems across an entire server, not just one query you’re already suspicious of.