Query Store is a great new feature in SQL Server 2016 and SQL Azure Database. Among other things, it captures and stores query plans for queries that execute on a SQL Server instance, so that they can be reviewed later, i.e. for performance troubleshooting. The plans are exposed in the sys.query_store_plan view, in the query_plan column. Even though the plan is stored in its XML representation, the data type of this column is nvarchar(max). This avoids some problems with using the xml data type. So far, so good.
But when I tried to copy the plan from the SSMS grid output and paste the XML into a new window, I quickly found that for larger plans, the XML text becomes truncated. This is an SSMS limitation. Luckily, there is a simple workaround that, somewhat ironically, uses XML support in the SQL Server query engine. It is based on a trick that I learned from SQL Server MVP Adam Machanic (blog).
Here is a sample query that shows how it works:
SELECT
CONVERT(nvarchar(max), p.query_plan, 1)
AS [processing-instruction(query_plan)]
FROM
sys.query_store_plan p
WHERE p.plan_id = 13959
FOR
XML
PATH(”);
This returns a single row, single column result set that contains an XML value that is clickable in the SSMS output grid. When clicked, this opens a new window, which contains the full query plan XML text wrapped as an XML tag, looking like this:
<?query_plan
<ShowPlanXML … </ShowPlanXML>?>
At this point, you can remove <?query_plan
and ?>, and save the resulting text in a file with .sqlplan extension. Close and re-open this file to view the graphical query plan.
© 2019 Microsoft. All rights reserved.