Blog Post

Calculating size and fragmentation of tables and indexes

Oct 21, 2020 SQL by Andrew Thomas

I was just going back through old useful scripts that I constantly use across projects and there were some gems so I thought I would post them here as they may help other.

To find size of tables and indexes in SQL Server you need to look in the system tables to get the physical statistics. The following SQL will display the information:

CREATE TABLE #results (Name NVARCHAR(20), RowsCount INT, Reserved NVARCHAR(18), 
  Data NVARCHAR(18), IndexSize NVARCHAR(18), Unused NVARCHAR(18)) 

EXEC sp_MSforeachtable @command1=
         "INSERT INTO #results EXEC sp_spaceused '?'"
          
SELECT * FROM #results 

DROP TABLE #results

To find detailed table information relating to pages, pages sizes, fragmentation, etc you can also use the DBCC ShowContig procedure.

SELECT object_name(TableStats.object_id) AS TableName, SystemIndexes.name AS IndexName, 
       TableStats.avg_fragmentation_in_percent AS ExternalFragmentation,
       TableStats.avg_page_space_used_in_percent AS InternalFragmentation
FROM (SELECT object_id, index_id,avg_fragmentation_in_percent, avg_page_space_used_in_percent
        FROM sys.dm_db_index_physical_stats (db_id('XXX_DATABASE_NAME_XXX'), null, null, null, 'DETAILED')
WHERE index_id <> 0) AS TableStats 
        INNER JOIN sys.indexes AS SystemIndexes ON 
          SystemIndexes.object_id = TableStats.object_id AND 
          SystemIndexes.index_id = TableStats.index_id
ORDER BY avg_fragmentation_in_percent DESC

To find out the space of each table/index this script is useful:

CREATE TABLE #tblResults
(
 [name] nvarchar(50),
 [rows] int,
 [reserved] varchar(50),
 [reserved_int] int default(0),
 [data] varchar(50),
 [data_int] int default(0),
 [index_size] varchar(50),
 [index_size_int] int default(0),
 [unused] varchar(50),
 [unused_int] int default(0)
)
EXEC sp_MSforeachtable @command1=
 “INSERT INTO #tblResults
 ([name],[rows],[reserved],[data],[index_size],[unused])
 EXEC sp_spaceused ‘?’”
 
 — Strip out the “ KB” portion from the fields
UPDATE #tblResults SET
 [reserved_int] = CAST(SUBSTRING([reserved], 1, 
 CHARINDEX(‘ ‘, [reserved])) AS int),
 [data_int] = CAST(SUBSTRING([data], 1, 
 CHARINDEX(‘ ‘, [data])) AS int),
 [index_size_int] = CAST(SUBSTRING([index_size], 1, 
 CHARINDEX(‘ ‘, [index_size])) AS int),
 [unused_int] = CAST(SUBSTRING([unused], 1, 
 CHARINDEX(‘ ‘, [unused])) AS int)
 
SELECT * FROM #tblResults
DROP TABLE #tblResults

Tags: SQL