This is just a brief post containing a script to get table sizes in SQL Server, along with some other information like table type, number of records, partition count, compression type, number of nonclustered indexes and their total size.
Intro
I was messing around with with the StackOverflow database, and I needed a quick way of getting the following information for a whole databases:
- Table names
- The name of the clustered index (if it exists)
- The type of table (clustered or heap)
- Number of records
- Table size in GB (this would be calculated based on the number of pages allocated or reserved to the table)
- The space that’s actually used in GB (based on the pages currently in use by the table)
- The type of compression being used
- The number of partitions making up the table
- The number of nonclustered indexes on the table
- The total size in GB of the nonclustered indexes
I ran into a few gotchas along the way, for example, to get the necessary size information, I had to do an INNER JOIN between the sys.partitions system view and the sys.allocation_units one, which ended up messing with the record counts due to each partition having 3 related allocation units, one for each allocation unit type.
Here’s an example using the Posts table:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT OBJECT_NAME(p.[object_id]) AS table_name, p.partition_number, p.[rows], au.allocation_unit_id, au.[type], au.[type_desc], au.container_id, total_pages, au.used_pages, au.data_pages FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.[partition_id] WHERE p.[object_id] = object_id('Posts'); |
I solved this with some “creative” aggregation.
The second gotcha is the fact that the CTE I have initially used to get the nonclustered index data was pretty slow, but I sorted that out by using a temp table instead.
The script
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | /*Make sure temp table doesn't exist*/ IF OBJECT_ID(N'tempdb.dbo.#NcIx', N'U') IS NOT NULL DROP TABLE #NcIx; /*Get NC index info into temp table*/ SELECT t.[object_id], COUNT(DISTINCT ix.index_id) AS [NcIndexes], CAST(( ( SUM([au].[total_pages]) * 8 ) / 1024.00 / 1024.00 ) AS NUMERIC(15, 3)) AS [TotalSizeGB] INTO #NcIx FROM sys.tables AS t INNER JOIN sys.indexes AS ix ON t.[object_id] = ix.[object_id] INNER JOIN sys.partitions AS p ON ix.[object_id] = p.[object_id] AND ix.index_id = p.index_id INNER JOIN sys.allocation_units AS au ON p.[partition_id] = au.container_id WHERE /*Only get nc indexes*/ ix.[index_id] > 1 GROUP BY t.[object_id]; /*Return tables info*/ SELECT SCHEMA_NAME(t.[schema_id])+'.'+t.[name] AS [TableName], CASE WHEN ix.[type_desc] = N'CLUSTERED' THEN ix.[name] ELSE N' --N/A--' END AS [ClusteredIndexName], ix.[type_desc] AS [TableType], SUM(CASE /*we only care for in row data if we don't want to get weird duplicate counts from LOB and/or offrow data*/ WHEN au.[type] = 1 THEN p.[rows] ELSE 0 END) AS [Records], CAST(( ( SUM(au.total_pages) * 8 ) / 1024.00 / 1024.00 ) AS NUMERIC(15, 3)) AS [AllocatedSpaceGB], CAST(( ( SUM(au.used_pages) * 8 ) / 1024.00 / 1024.00 ) AS NUMERIC(15, 3)) AS [UsedSpaceGB], p.[data_compression_desc] AS [CompressionType], /*since we're joining with sys.allocation_units we get duplicate partition counts for LOB and offrow allocation types, so we only care for allocation_unit.type 1*/ SUM(CASE WHEN au.[type] = 1 THEN 1 ELSE 0 END) AS [Partitions], ISNULL(NcIx.NcIndexes, 0) AS [NCIndexes], ISNULL(NcIx.TotalSizeGB, 0.00) AS [NCIXTotalAllocGB] FROM sys.tables AS t INNER JOIN sys.indexes AS ix ON t.[object_id] = ix.[object_id] INNER JOIN sys.partitions AS p ON ix.[object_id] = p.[object_id] AND ix.index_id = p.index_id INNER JOIN sys.allocation_units AS au ON p.[partition_id] = au.container_id LEFT JOIN #NcIx AS NcIx ON t.[object_id] = NcIx.[object_id] WHERE /*Only get clusters and heaps*/ ix.index_id IN ( 0, 1 ) GROUP BY t.[schema_id], t.[name], ix.[name], ix.[type_desc], p.data_compression_desc, NcIx.NcIndexes, NcIx.TotalSizeGB ORDER BY [AllocatedSpaceGB] DESC; /*Delete temp table*/ IF OBJECT_ID(N'tempdb.dbo.#NcIx', N'U') IS NOT NULL DROP TABLE #NcIx; |
You can also find this script in my SQL Server Scripts repo.
And this is how the result set looks like:
Conclusion
TL;DR: I made a script to get table sizes in sql server and I figured I’d share it here.
If you’re thinking that I could have used sys.dm_db_partition_stats instead of joining sys.partitions and sys.allocation_units – Yes, I could have, but I was already halfway done with this query before I realized.
I might add an updated version at a later time.
If you’ve found this post helpful, you might also like this post about getting database file size details.
Update: I’ve updated the script to prepend the schema name to the table name to make things easier for databases that have multiple schemas.
4 comments
Nice work , like it!
Thank you!
Very good work Vlad. This is by far one of the better table size scripts I’ve seen. It would be wonderful if able to send the results to a table to track growth.
Hi Romy,
Thank you for the feedback! You could achieve that with minimal changes to the script.
If I have some time this week, I’ll add that as an option.