Get Table Size, Row Count & Database Size in Azure Synapse Dedicated SQL Pool | Azure Synapse Analytics Tutorial

Get Table Size, Row Count & Database Size in Azure Synapse Dedicated SQL Pool

📘 Overview

Monitoring table and database sizes is essential in Azure Synapse Dedicated SQL Pool to understand storage usage, identify large objects, and optimize performance. In this guide, you'll learn how to retrieve table sizes, row counts, and total database size using system views and built-in T-SQL queries.

📊 1. Get Row Count by Table

SELECT 
    t.NAME AS TableName,
    SUM(p.rows) AS RowCounts
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
GROUP BY t.NAME
ORDER BY RowCounts DESC;

Note: This query shows row counts for each table including both heap and clustered index partitions.

💾 2. Get Table Size in MB

SELECT
    t.name AS TableName,
    SUM(s.used_page_count) * 8 / 1024 AS UsedSpaceMB
FROM sys.dm_pdw_nodes_db_partition_stats s
JOIN sys.tables t ON s.object_id = t.object_id
GROUP BY t.name
ORDER BY UsedSpaceMB DESC;

🗃️ 3. Get Database Size

SELECT 
    SUM(used_page_count) * 8 / 1024 AS TotalDatabaseSizeMB
FROM sys.dm_pdw_nodes_db_partition_stats;

🔍 4. Detailed Size per Distribution

SELECT 
    t.name AS TableName,
    pnp.[distribution_id],
    SUM(pnp.used_page_count) * 8 / 1024 AS SizeMB
FROM sys.pdw_nodes_tables AS nt
JOIN sys.tables AS t ON nt.object_id = t.object_id
JOIN sys.dm_pdw_nodes_db_partition_stats AS pnp ON pnp.object_id = t.object_id
GROUP BY t.name, pnp.[distribution_id]
ORDER BY t.name;

📌 Tips

• Regularly monitor large tables to prevent unexpected storage costs.
• Use this information to plan for archiving or partitioning strategies.
• Enable performance diagnostics and auditing if usage grows rapidly.

📈 Why This Matters

Keeping track of table size and row counts helps with:

• Performance tuning (e.g., distribution skew analysis)
• Cost control for storage and compute usage
• Capacity planning and environment scaling

📺 Watch the Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.