It’s important to monitor table size in Azure Synapse Analytics Dedicated SQL Pools. In this post, you’ll learn the queries you need to monitor table size. This information will help you understand if the table type and distribution are still suitable for your table (example: Replicated, Round-robin, Hash-distributed).
Table of Contents
Why should I monitor table size in Azure Synapse Analytics SQL Dedicated Pools?
To begin, the design of the tables in your Azure Synapse Analytics Dedicated SQL Pools plays a critical role in improving the performance of the queries in your database. Having replicated tables for your small lookup information or choosing the right distribution key for your table helps you avoid performance issues.
Monitoring table size is not a new practice. It has always been important to help plan data growth or understand savings when trying to archive information.
Additionally, the added complexity in Azure Synapse Analytics Dedicated Pools is that the information is stored across multiple logical distributions. Each distribution could be storing a different number of rows for the same table (aka distributed key). Therefore, when trying to get the size of a table, you need to add the size of all the distributions.
The queries below have been copied from Microsoft documentation.
Creating a View to Ease Analysis
The first step is to create a view to ease analysis. The following query will create the table vTableSizes in your database.
Table sizes analysis in your Azure Synapse Analytics
Now, you can use the following queries to analyze table size.
Azure Synapse Analytics Table Space Summary
Query for displaying the summary:
The results display a summary of table sizes and distribution types.
Azure Synapse Analytics Table Size by Distribution Type Query
Table size by distribution type query:
Results:
Azure Synapse Analytics Table Size by Index Type
Table size by index type query:
Results:
Azure Synapse Analytics Distribution Size Summary
Distribution size summary query:
Results:
Summary
In summary, correct table design is critical to maintain and improve the performance of your queries in Azure Synapse Analytics SQL Dedicated Pools. The queries mentioned in this post will help you identify how data is distributed across different tables. Then, you can choose an optimal table type for your data assets.
WHAT’S NEXT?
In upcoming blog posts, we’ll continue to explore some of the features within Azure Services.
Please follow Tech Talk Corner on Twitter for blog updates, virtual presentations, and more!
As always, please leave any comments or questions below.
