Data skew is one of the most important considerations when working with Azure Synapse Analytics. Data skew is the uneven distribution of data across data storage distributions in SQL Dedicated Pools. In this post, you’ll learn how to monitor the data skew in your Azure Synapse Analytics SQL Pool.
About Data Skew
To begin, data skew is when your data is not distributed correctly across different storage distributions. This decreases the performance of your Azure Synapse Analytics SQL Dedicated SQL Pools. This term also applies to other big data systems, including working with Apache Spark pools or other areas like machine learning or reporting.
Firstly, the main idea when understanding data skew is that a high data skew percentage is not recommended. This causes some compute nodes to work harder (more processing query time) to read and retrieve information from the tables.
Azure Synapse Analytics Dedicated SQL Pools have 60 storage distributions and when choosing the distribution key for your hash distributed tables (aka DISTRIBUTION = HASH in the creation of the table), your goal is to select the optimal column for distributing the information evenly.
Another key point is making sure your key is the same across different tables that will be queried or joined together to avoid shuffle data movement operations.
This concept mainly applies to Hash Distributed tables.
This is because replicated tables (aka DISTRIBUTION = REPLICATE) copy information (all the rows in the table) across all the available compute nodes. Then, round_robin (aka DISTRIBUTION = ROUND_ROBIN) distributes information randomly across the storage distributions.
Analysis Azure Synapse Analytics Data Skew
The following query will help you understand if there is data skew in your Azure Synapse Analytics Dedicated SQL Pools tables.
with DataDistribution as ( SELECT s.name as [Schema Name] ,t.name as [Table Name] ,tp.[distribution_policy_desc] as [Distribution Policy Name] ,sum([row_count]) as [Table Row Count] ,max(row_count) as [Max Distribution Row Count] ,min(row_count) as [Min Distribution Row Count] ,avg(row_count) as [Avg Distribution Row Count] from sys.schemas s JOIN sys.tables t ON s.[schema_id] = t.[schema_id] JOIN sys.pdw_table_distribution_properties tp ON t.[object_id] = tp.[object_id] JOIN sys.pdw_table_mappings tm ON t.[object_id] = tm.[object_id] JOIN sys.pdw_nodes_tables nt ON tm.[physical_name] = nt.[name] JOIN sys.dm_pdw_nodes pn ON nt.[pdw_node_id] = pn.[pdw_node_id] JOIN sys.pdw_distributions di ON nt.[distribution_id] = di.[distribution_id] JOIN sys.dm_pdw_nodes_db_partition_stats nps ON nt.[object_id] = nps.[object_id] AND nt.[pdw_node_id] = nps.[pdw_node_id] AND nt.[distribution_id] = nps.[distribution_id] where tp.[distribution_policy_desc] ='HASH' -- AND t.name = @tbl GROUP BY s.name ,t.name ,tp.[distribution_policy_desc] ) Select [Schema Name], [Table Name], [Distribution Policy Name], [Table Row Count], [Max Distribution Row Count], [Min Distribution Row Count], [Avg Distribution Row Count], CASE WHEN [Table Row Count] = 0 then -1 else abs([Max Distribution Row Count] * 1.0 - [Min Distribution Row Count]*1.0) / [Max Distribution Row Count] * 100.0 END as [Table Skew Percent] FROM DataDistribution
Query results with data skew percentage for each one of your Azure Synapse Analytics tables.
You can see in the results that one of my tables has a 100% data skew. This is because some of the storage distributions don’t have any data. This is due to an incorrect design decision when choosing the distribution key for the table.
So, what does distribution look like? In the image below, you can see that the data skew has reduced to 0.52% after changing the distribution key.
To summarize, you have seen a critical query that will help you identify data skew (which is not recommended) in your Azure Synapse Analytics Dedicated SQL Pools. This query will help you select optimal distribution keys for each one of the tables when the information is hash distributed.
Check out these other blog posts
To extract Power BI Objects with Azure Data Factory or Synapse Analytics – workspaces, reports, datasets deployed to Power BI tenant – create or download the solution here.
How to monitor Azure Synapse Analytics concurrency slots in your Azure Synapse Analytics SQL Pools. Azure Synapse Analytics Queries #8