SQL Server execution plan in XML for query is too large to be displayed or saved in full-CodePudding

I am trying to obtain the execution plan in XML for a large T-SQL query. I am able to run the query to obtain the execution plan.

However, the resulting XML execution plan text is too long to be fully displayed. Only the beginning part of the execution plan is displayed when I open it in XML (clicking on the execution plan cell in the Results result tab), and the plan does not load at all in the Query Plan result tab.

The database I am using is an Azure SQL Database database in the Azure cloud, and I am using Azure Data Studio to run the query to obtain the execution plan. I cannot post the full query, but it follows this structure:

SET SHOWPLAN_XML ON
GO

<query goes here>
GO

SET SHOWPLAN_XML OFF
GO

Is there a way to directly save the query execution plan to a file to avoid the limit that I'm encountering? How can I obtain the full query execution plan?

CodePudding user response：

I tried the same query and procedure in SSMS as suggested by Charlieface in comments, and I was able to get the full query execution plan in XML. In order to get the full query, I had to increase the XML data limit in SSMS.

To increase the XML data limit, go to the Tools menu in SSMS, and then Options. In the options window, go to Query Results, SQL Server, Results to Grid. In the Results to Grid page increase the XML data dropdown as necessary. I had to set it to Unlimited.

Thanks, Charlieface!