Showing the query plan returned from the explain statement in a graphic manner

Question

Showing the query plan returned from the explain statement in a graphic manner

pmscorca 1,052

Hi,

in order to check some T-SQL queries against a Synapse dedicated sql pool I'd like to use the EXPLAIN statement, but it produces a XML query plan and I want to view this plan in a graphic manner.

Any suggests to me, please? Thanks

1 answer

Your answer

Answer 1

Amira Bedhiafi 33,476 Volunteer Moderator

Azure Synapse itself does not provide a direct graphical view for the EXPLAIN output, you can accomplish this in another way.

First, you need to run your EXPLAIN statement in Azure Synapse Analytics to get the XML query plan. Make sure to copy or save this XML data.

Although your work is in Azure Synapse Analytics, SSMS (a free tool provided by Microsoft for managing SQL Server) can visualize XML plans.

Open SSMS and connect to any database (it doesn't need to be the source of the plan).
Save the XML plan you got from Synapse to a .sqlplan file. You can do this by opening a new text file, pasting the XML content, and saving the file with a .sqlplan extension.
Open the .sqlplan file in SSMS. SSMS recognizes this file format and automatically renders the query plan graphically.

If SSMS is not an option or you prefer a different tool, there are third-party options available that can visualize SQL query plans from XML. SentryOne Plan Explorer and Redgate's SQL Monitor are examples of such tools.

There are also online SQL plan viewers that can render your XML plan graphically. These tools might come in handy if you cannot or prefer not to install software on your computer. You would need to upload your .sqlplan file or paste the XML content into the online tool. Be cautious about using online tools if your query plan contains sensitive information. An example of such a tool is the Paste The Plan feature on Brent Ozar website.

https://www.brentozar.com/pastetheplan/

pmscorca 1,052 Reputation points

2024-03-12T16:12:17.8833333+00:00

Hi, copying and pasting the XML result in a text file, changing the extension in .sqlplan and opening the related file in SSMS I can obtain this error:
Amira Bedhiafi 33,476 Reputation points Volunteer Moderator

2024-03-12T16:17:32.0533333+00:00

Try to validate your xml : https://www.xmlvalidation.com
pmscorca 1,052 Reputation points

2024-03-12T16:41:43.1966667+00:00

Hi, no errors were found.

I don't check this option:
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-03-13T10:14:38.96+00:00
@pmscorca The error message you're seeing suggests that the XML query plan generated by the EXPLAIN statement may be invalid or corrupted. Here are a few things you can try to resolve the issue:

Check the XML file for any syntax errors or formatting issues. Make sure that the file is well-formed and conforms to the XML standard. You can use an XML editor or validator to help you identify and fix any errors.

Try generating the query plan again using the EXPLAIN statement. Make sure that the query is valid and that all required parameters are specified correctly.

If the issue persists, try using a different tool to view the query plan. For example, you can try using SQL Sentry Plan Explorer or SQL Server Profiler to view the plan in a graphical format.

I hope this helps! Let me know if you have any further questions or concerns.
pmscorca 1,052 Reputation points

2024-03-13T11:32:30.1266667+00:00

Hi, I'm using the EXPLAIN statement to generate automatically the XML query plan and if this plan is wrong should I adjust it each time?
SentryOne Plan Explorer and Redgate's SQL Monitor aren't free tools.
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-03-14T05:22:54.3866667+00:00

Hi @pmscorca

If the XML query plan created by the EXPLAIN statement isn't accurate, you might need to adjust your query to fix any problems. But there's also a chance the issue lies with the Synapse dedicated SQL pool itself. In that case, you should consider reaching out to Microsoft support for more help.

As for third-party tools, you're right that SentryOne Plan Explorer and Redgate's SQL Monitor aren't free. However, they do offer free trials you can try out to see your query plans visually. There are also other free tools like SQL Server Management Studio (SSMS) and SQL Server Profiler that can help you view query plans.

I hope this helps!
pmscorca 1,052 Reputation points

2024-03-14T07:04:43.5333333+00:00

Hi, Synapse doesn't support SQL Server Profiler.

I think that Synapse isn't a more mature data platform respect to SQL Server.
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-03-14T09:37:58.8933333+00:00

@pmscorca Yes, you are correct, Synapse Analytics does not support SQL Server Profiler.

Share via

Showing the query plan returned from the explain statement in a graphic manner

1 answer

Your answer