Could SSMS shows actual execution plan in Azure Synapse?

Question

I'm studying of Azure Synapse.

In dedicated SQL pool database, 'actual execution plan' of SSMS was disabled. In serverless pool database, SSMS says 'set statistics is not supported.' in SQL execution.

I forgot of take screenshot. Image of 'disable of actual execution plan' is '4. Run the query by selecting Execute or use the following shortcut: F5.' of this page.

I understood SSMS could only shows plan of SQL, and not shows actual plan and live statistics. But I still mysterious thing is how Synapse experts make faster SQL in Synapse? In synapse, they don't use complex SQL(ex. much of outer join SQL)? If my question is completely misunderstood of Azure Synapse system, please point out me.

Answer 1

Later versions of SQL Server Management Studio (SSMS), v18.x plus do support estimated execution plans for Azure Synapse Analytics dedicated SQL pools so the first thing you should do is check your version and update to the latest one. Here is an example plan from SSMS against a dedicated SQL pool, you can see it's got a Round Robin operator:

In terms of performance, you should generally look to hash partition your large fact tables on a key that gives good distribution and replicate your small dimensions. Use round robin distribution as a starting point or where you find it's appropriate for performance with your workloads. Use EXPLAIN to view text-based estimated execution plans. Visual Actual Execution Plans are not supported for but you can review the performance DMVs like sys.dm_pdw_request_steps:

SELECT * 
FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

Further details on this listed here.