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

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.