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!
