Azure Synapse Analytics Queries #6 Monitor Data Skew

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. 

Monitor data skew - nodes

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. 

Summary 

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

comment [ 0 ]
share
No tags 0

No Comments Yet.

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *