Lesson Learned #150: Calculating the space used by table and per column

[Jose_Manuel_Jurado]

Today, I got a service request that our customer needs to know the total data size per table and data size per column. 

I shared the following information with this customer.

• My first idea was to identify the size per table (in terms of rows and KB). We executed this query:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

• We found that several tables are using too much data and have a lot of rows. 
• We suggested to run the following script to the tables that they consider that are the main goal to review the size. 
• Also, we mentioned that using DataLength per column our customer will have an impact in performance due to SQL SERVER needs to read all the rows of the tables and there is not needed to calculate for some data type like INT because it is fixed value that you could obtain the size basically multiple the max_lenght per number of rows. 
• I think that this script is possible to improve a lot, for example, instead of running a TSQL command per execution, try to run every 50 or 60 columns, but, anyway, here is:

DECLARE @ObjectTable    sysname = 'Example'
DECLARE @Name           sysname
DECLARE @Type           INT
DECLARE @Total          BIGINT = 0
DECLARE @SQL            NVARCHAR(4000)
DECLARE @ParmDefinition nvarchar(100)= N'@Total BIGINT OUTPUT'  

DECLARE vColumns CURSOR FOR 
     SELECT C.name,T.USER_TYPE_ID from sys.columns c
     JOIN SYS.TYPES T
     ON C.USER_TYPE_ID=T.USER_TYPE_ID
       WHERE object_id=OBJECT_id(@ObjectTable) 
        ORDER BY 1; 
  
OPEN vColumns 
FETCH NEXT FROM vColumns 
INTO @Name, @Type
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @Total=0
    SET @SQL = 'SELECT @Total = SUM(DATALENGTH(' + @Name + ')) FROM ' + @ObjectTable
    EXECUTE sp_executesql @SQL,@ParmDefinition, @Total = @Total OUTPUT;  
       PRINT 'Size:' + CONVERT(CHAR(20),@Total) + '|Type ID:' + CONVERT(CHAR(5),@Type) + '|Column Name:'+ CONVERT(CHAR(128), @Name)  + '|SQL:' + @SQL
       FETCH NEXT FROM vColumns INTO @Name, @Type
END   
CLOSE vColumns;  
DEALLOCATE vColumns;  

Enjoy!