Find the average size of rows in tables

 

How to Find the Average Row Size in SQL Server Tables

When managing large databases, it’s often helpful to understand how efficiently your tables are storing data. One useful metric is the average row size, which can help identify storage inefficiencies or opportunities for optimization.

Below is a SQL Server query that provides detailed insights into the average row size, total size, and fragmentation of your tables and indexes.

SQL Query to Analyze Table Storage Details

-- Retrieve detailed statistics about table and index storage

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT 
   CAST(OBJECT_NAME(ps.OBJECT_ID) + '.' + ISNULL(i.[Name], 'heap') AS VARCHAR(60)) AS Table_Index_Name,
   SUM(ps.record_count) AS Total_Record_Count,
   CAST(((SUM(ps.page_count) * 8192) / 1000000.00) AS NUMERIC(9,2)) AS Table_Size_MB,
   AVG(ps.max_record_size_in_bytes) AS Average_Record_Size_Bytes,
   MAX(ps.max_record_size_in_bytes) AS Max_Record_Size_Bytes,
   CAST(AVG(avg_fragmentation_in_percent) AS NUMERIC(6,1)) AS Avg_Fragmentation_Percent,
   CAST(AVG(ps.avg_page_space_used_in_percent) AS NUMERIC(6,1)) AS Avg_Page_Space_Used_Percent
FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
LEFT JOIN [sys].indexes AS i 
    ON i.OBJECT_ID = ps.OBJECT_ID 
    AND i.index_id = ps.index_id
-- Optional: Uncomment the line below to filter results by specific table(s)
-- WHERE OBJECT_NAME(ps.OBJECT_ID) IN ('YourTableName') 
GROUP BY OBJECT_NAME(ps.OBJECT_ID), i.[Name]
ORDER BY OBJECT_NAME(ps.OBJECT_ID), i.[Name];

Key Points:

• The query uses dm_db_index_physical_stats in DETAILED mode to ensure comprehensive metrics.
• It calculates the average and maximum row sizes, total number of records, and storage size in megabytes.
• Fragmentation percentage and page space usage are also included, which can be useful for performance tuning.
• You can filter the results for specific tables by uncommenting and editing the WHERE clause.

This script is ideal for DBAs and developers who want to monitor and optimize data storage within SQL Server databases.