Monitoring table distribution, data skew, and index health using Synapse DMVs

In distributed databases such as Synapse dedicated SQL pools, the table is distributed across multiple nodes by design. When the rows in the table are not evenly distributed across the nodes, data distribution is said to be skewed. Data skew scenarios can have an impact on query performance. This recipe will provide a script based on Synapse Dynamic Management Views (DMVs) that you can use to monitor table skew.

Tables in a dedicated SQL pool have a column store index created by default. Column store indexes store the rows of the table in columnar format, which is optimized for processing analytics workloads. Each column store index in a table is subdivided into segments. A column store segment can be of three states – Open, Closed, or Compressed. For the column store index to be effective, its segments need to meet the following conditions:

• The number of segments in an open or closed state...