Track Database Disk-Space Usage on a Granular Level

One of the housekeeping challenges DBAs face is to keep track of their databases’ growth and how much space is being used by each table and index in those databases. To meet this challenge, I wrote a stored procedure, usp_ SpaceUsedAnalyzer, that extends the functionality of the sp_spaceused system stored procedure to present more detailed information.

The usp_SpaceUsedAnalyzer stored procedure is particularly helpful for finding out how much space is being used by indexes. As Figure 2 shows, you can use the stored procedure to reveal such details as how much space is being used by clustered and nonclustered indexes and their type of space allocation.

You can download usp_SpaceUsedAnalyzer by clicking the 100213.zip hotlink. You simply run this stored procedure on the SQL Server 2008 or SQL Server 2005 machine for which you want to evaluate database disk-space usage. It won’t work with SQL Server 2000 or earlier because it uses system tables that are available only in SQL Server 2005 and later.

As the following syntax shows, usp_SpaceUsed- Analyzer takes two parameters:

EXEC usp_SpaceUsedAnalyzer  {'summary'|'details'},  {'1 [desc|asc][,2 [desc|asc],...n]'}

You use the first parameter to specify whether you want to display data at the table level ('summary') or the index level ('details'). You use the second parameter to specify how you want that data sorted. When you use 'summary' for the first parameter, you can sort the returned data by the following columns in descending (desc) or ascending (asc) order:

• Schema. This column, which is represented by the value 1, identifies the schema for each table.
• Table Name. This column, which is represented by 2, specifies the name of each table.
• Number of Rows. This column, which is represented by 3, notes how many rows are in each table.
• Reserved Space (MB). This column, which is represented by 4, shows the amount of space that’s reserved for each table.
• Data Space (MB). This column, which is represented by 5, contains the amount of space used by the data in each table.
• Index Size (MB). This column, which is represented by 6, specifies the total amount of space used by all the indexes in each table.
• Unused Space (MB). This column, which is represented by 7, reveals the amount of available space in each table.

When you use 'details' for the first parameter, you can sort the returned data by the following columns in descending or ascending order:
• Table Name. This column, which is represented by 1, specifies the name of each table.
• Schema Name. This column, which is represented by 2, identifies the schema for each table.
• Index Name. This column, which is represented by 3, provides the name of each index in each table.
• Index ID. This column, which is represented by 4, contains the ID of each index.
• No. Pages. This column, which is represented by 5, specifies the number of pages in each index.
• index_type_desc. This column, which is represented by 6, notes whether each index is clustered or nonclustered.
• alloc_unit_type_desc. This column, which is represented by 7, identifies the type of space allocation for each index.
• Used Space (MB). This column, which is represented by 8, reveals how much space each index is using. For example, the statement

EXEC usp_SpaceUsedAnalyzer    'summary','6 desc'

displays table-level data that’s sorted in descending order by the total amount of space used by each table’s indexes. If you want to find the tables with the most data, you’d run the query

EXEC usp_SpaceUsedAnalyzer    'summary','5 desc'

The following query highlights the nonclustered indexes that take the most amount of space:

EXEC usp_SpaceUsedAnalyzer    'details','6 desc,8 desc'

As you can see, the usp_SpaceUsedAnalyzer stored procedure provides the disk-space usage details you need to manage disk space more efficiently. For instance, you can use usp_SpaceUsedAnalyzer to identify indexes that are taking a lot of space. If any of those indexes aren’t being used, you can remove them to free up some space. (See “Evaluate Index Usage in Databases,” October 2008, InstantDoc ID 99985, for a stored procedure you can use to find indexes that aren’t being used.)