SQL Table Space Utilization

Working as a database administrator, more often than not, I want to know which are my largest tables. This can be handy to help us to identify tables that might be good candidate for table partitioning. We could also use the information to help determine if data in a particular table might be suitable to be archived.

One stored procedure that can be use to get the detail about space usage of a particular table in SQL Server database is:

sp_spaceused

Detailed information about this stored procedure can be found here.

How to Use the Stored Procedure

Let say that we want to find the space usage for Person.Person table in AdventureWorks database. You can get that information by running the following query:

USE [AdventureWorks];

EXEC sp_spaceused 'Person.Person';

Ok, that's great, but that only return space usage information for one table. What if we want to get space usage information for all tables within one particular database (let say AdventureWorks). To do that, you can run the following query:

USE [AdventureWorks];

CREATE TABLE #TableStats (
 Name NVARCHAR(128),
 Rows CHAR(11),
 Reserved VARCHAR(18),
 Data VARCHAR(18),
 Index_Size VARCHAR(18),
 Unused VARCHAR(18));
 
EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";
 
SELECT Name,
 Rows,
 Reserved,
 Data,
 Index_Size,
 Unused
FROM #TableStats;
 
DROP TABLE #TableStats;

In the query script above, basically we created a temporary table and then run the sp_spaceused stored procedure against each tables within the AdventureWorks database using the sp_MSForEachTable stored procedure. Once that's done, we just query the temporary table and drop the temporary table.

Now, let say that I want to find the top 10 largest table in AdventureWorks database in term of data size. The Data column returned by the sp_spaceused is using VARCHAR data type. So one way to sort it correctly, we can use the following query (basically in the ORDER clause we just need to convert the VARCHAR into INT.):

USE [AdventureWorks];

CREATE TABLE #TableStats (
 Name NVARCHAR(128),
 Rows CHAR(11),
 Reserved VARCHAR(18),
 Data VARCHAR(18),
 Index_Size VARCHAR(18),
 Unused VARCHAR(18));
 
EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";
 
SELECT TOP 10 Name,
 Rows,
 Reserved,
 Data,
 Index_Size,
 Unused
FROM #TableStats
ORDER BY CAST(REPLACE(Data,' KB', '') AS INT) DESC;
 
DROP TABLE #TableStats;

Some Notes:

• As noted in the SQL Server Book on line, if you use sp_spaceused without passing any parameter, you will get the space utilization of the database that you are currently connected to.
• There is a second optional parameter that you can passed onto sp_spaceused, and that's to specify whether or not to update usage statistics (using DBCC UPDATEUSAGE). The parameter accept 'TRUE' or 'FALSE' value, with 'FALSE' set as the default.