SQL Server Execution Plans Too Large for SSMS: The SQLYARD Execution Plan Splitter

---

You are troubleshooting a slow stored procedure. You generate the execution plan in SSMS. The progress bar spins. SSMS freezes. You wait thirty seconds, it comes back, and what renders is so dense you cannot navigate it. You zoom in on one operator and the rest of the plan disappears off screen. You try to find the index scan that is killing the query and give up after five minutes of scrolling.

This is what happens when a SQL Server execution plan exceeds the point where SSMS can render it usefully. The file is technically valid. SSMS can technically open it. But a plan with 200 or more operators, nested views, and parallel branches pushes into territory where the graphical renderer breaks down. The XML behind it can run to 10MB, 20MB, or more on sufficiently complex queries.

The fix is to split the plan into smaller pieces, one statement at a time, so SSMS can open each piece cleanly. Experienced DBAs do this by hand. The SQLYARD Execution Plan Splitter does it in seconds.

1 What a .sqlplan File Actually Is Beginner

When you save an execution plan from SSMS using Save Execution Plan As, you get a file with a .sqlplan extension. Most DBAs treat it as a binary file that only SSMS understands. It is not. It is plain XML.

Open any .sqlplan file in a text editor and you will see something like this at the top:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
             Version="1.2" Build="13.0.4001.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT o.OrderID ..."
                    StatementType="SELECT"
                    StatementSubTreeCost="0.5231"
                    StatementEstimatedRows="1524">
          ...operators and index references...
        </StmtSimple>
        <StmtSimple StatementText="UPDATE dbo.Orders ..."
                    StatementType="UPDATE"
                    StatementSubTreeCost="1.8742">
          ...
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

The structure is straightforward. A ShowPlanXML root element wraps a BatchSequence, which contains one or more Batch elements, each containing a Statements block with individual StmtSimple (or StmtCursor, StmtCond) elements — one per SQL statement in the batch.

This structure is the key to splitting. Each StmtSimple is a self-contained unit. If you extract one and wrap it in a valid ShowPlanXML envelope, SSMS will open it as a complete, valid execution plan. That is exactly what the splitter does.

2 Why Large Plans Break SSMS Beginner

SSMS renders execution plans graphically using a WinForms-based visualizer. It works well for plans with a few dozen operators. As operator count grows the rendering becomes progressively slower and less useful. There is no official Microsoft threshold, but in practice the pattern is consistent.

The XML size correlates with operator count, not just query complexity. A single statement joining 20 tables with parallel operators, index spools, and nested loops can generate more XML than ten simple statements combined. The graphical renderer is the bottleneck, not SSMS itself. The same XML that crashes the renderer can be read perfectly well by other tools or by examining the XML directly. Splitting the plan into individual statements gives the renderer smaller, manageable pieces to work with.

3 What Causes Plans to Get Large Intermediate

Understanding what drives plan size helps you anticipate when you will hit the problem.

Multi-Statement Stored Procedures

A procedure with 10 SELECT statements each joining 8 tables generates a plan file containing all 10 statements. The total operator count compounds. If each statement produces a plan with 40 operators the combined file has 400 operators before accounting for shared objects and metadata.

Views Referencing Views

When a query references a view that references other views, SQL Server expands all view definitions into the plan. A query that looks like a simple 3-table join may expand into a 15-table plan once views are resolved. The plan XML reflects the fully expanded tree, not the surface-level query.

Table-Valued Functions

Multi-statement TVFs produce their own plan fragment inline. A query calling a TVF that internally joins 6 tables produces a plan that includes all 6 internal joins as operators, even though the query text just references the function name.

Parallelism

Parallel plans include exchange operators and parallel branch metadata that add significant XML overhead compared to serial plans. A query that uses 8 threads generates substantially more plan XML than the same query running serially.

Parameter Sniffing Investigations

When you are capturing plans across multiple executions with different parameter values to diagnose sniffing issues, you may end up working with plan files from complex procedures that already have all the factors above. These are the plans most likely to hit the size limit.

4 The Manual Split Technique Intermediate

Before the splitter existed, the manual process required opening the .sqlplan file in a text editor, searching for StmtSimple to find each statement boundary, extracting the XML for one statement, and wrapping it in a valid envelope:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
             Version="1.2" Build="">
  <BatchSequence>
    <Batch>
      <Statements>
        <!-- paste the StmtSimple block here -->
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Save the file with a .sqlplan extension and open in SSMS. If you got the tag boundaries right SSMS opens a clean, navigable plan for just that statement. If you missed a closing tag or split inside a nested element SSMS rejects the file with a parse error and you start over.

5 Introducing the SQLYARD Execution Plan Splitter Beginner

The SQLYARD Execution Plan Splitter is a free browser-based tool that takes execution plan XML and splits it into individual downloadable .sqlplan files — one per statement.

• Parses the plan XML and identifies every statement using the ShowPlanXML schema. Handles StmtSimple, StmtCursor, and StmtCond statement types.
• Shows you what it found before you download anything: statement text, statement type (SELECT, UPDATE, DELETE, INSERT), estimated subtree cost, and estimated row count for each statement.
• Lets you choose what to split. Check or uncheck individual statements so you only download the pieces you need.
• Generates valid .sqlplan files. Each download is properly wrapped in a ShowPlanXML envelope that SSMS will open without errors.

6 How to Use It: Step by Step Beginner

7 What the Results Tell You Intermediate

The statement list the tool produces is worth examining before you even download anything.

Estimated Subtree Cost

The optimizer’s estimate of the total resource cost for that statement relative to all other statements in the plan. It is not a time measurement. It is a unitless relative cost number. A statement with a cost of 3.12 is estimated to be roughly six times more expensive than one with a cost of 0.52. Use this to prioritize which statements to investigate first.

Estimated Rows

The optimizer’s estimate of how many rows that statement will process. When this is wildly off from actual row counts (which you can verify by opening the plan in SSMS and checking actual vs estimated rows on individual operators), you have a statistics problem. Outdated statistics cause the optimizer to choose bad plans because its row estimates are wrong.

Statement Type

Tells you immediately if an expensive statement is a SELECT or a data modification. An UPDATE or DELETE with high estimated cost and high row count is a candidate for index analysis on the target table. A SELECT with high cost and low estimated rows may indicate a parameter sniffing issue where the plan was compiled for a different parameter value than what is actually being passed.

A Concrete Example

Statement	Type	Est. Cost	Est. Rows	Priority
Statement 1	SELECT	0.5231	1,524	Low
Statement 2	UPDATE	1.8742	892	Medium
Statement 3	SELECT	3.1205	4,200	Investigate First
Statement 4	DELETE	0.9814	12,400	Check Row Count

Statement 3 has the highest estimated cost at 3.12. That is the first plan to open and investigate. Statement 4 has the highest estimated row count at 12,400 despite a relatively modest cost estimate, worth checking whether the DELETE is hitting a clustered index scan on a large table. Statement 2’s UPDATE has a higher cost than the DELETE despite touching fewer rows, which suggests a more complex execution path worth examining.

You now have a prioritized investigation order before you open a single graphical plan. Download Statement 3 first, open it in SSMS, and you are looking at a clean, navigable plan for just that statement.

References

• SQLYARD Tools
• SQLYARD Execution Plan Splitter: Free Tool
• SQLYARD Statistics IO and TIME Parser: Free Tool
• Microsoft Docs: Display and Save Execution Plans
• Microsoft Docs: Showplan Logical and Physical Operators Reference
• Microsoft Docs: Execution Plans
• SQLYARD: SQL Server Deadlock Alerts
• SQLYARD: Automatic Plan Correction