SQL Server: Measuring Space Allocation and Index Distribution

November 14, 2008

by Shaunt Khaldtiance

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, SpaceUsedAnalyzer, that extends the functionality of the spaceused system stored procedure to present information that is more detailed.

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

Click for larger image

Figure 1

You can 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. SpaceUsedAnalyzer 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, SpaceUsedAnalyzer takes two parameters:

EXEC usp_SpaceUsedAnalyzer
  {summary|details},
  {n1 [Desc|Asc][,n2 [Desc|Asc],...]}

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:

• Table Name. This column, which is represented by n2, specifies the name of each table.
• Schema. This column, which is represented by n1, identifies the schema for each table.
• Number of Rows. This column, which is represented by n3, notes how many rows are in each table.
• Reserved Space (MB). This column, which is represented by n4, shows the amount of space that's reserved for each table.
• Data Space (MB). This column, which is represented by n5, contains the amount of space used by the data in each table.
• Index Size (MB). This column, which is represented by n6, specifies the total amount of space used by all of the indexes in each table.
• Unused Space (MB). This column, which is represented by n7, 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 n1, specifies the name of each table.
• Schema Name. This column, which is represented by n2, identifies the schema for each table.
• Index Name. This column, which is represented by n3, provides the name of each index in each table.
• Index ID. This column, which is represented by n4, contains the ID of each index.
• No. Pages. This column, which is represented by n5, specifies the number of pages in each index.
• index_type_desc. This column, which is represented by n6, notes whether each index is clustered or non-clustered.
• alloc_unit_type_desc. This column, which is represented by n7, identifies the type of space allocation for each index.
• Used Space (MB). This column, which is represented by n8, reveals how much space each index is using.

For example, the statement

EXEC usp_SpaceUsedAnalyzer 'summary','6 desc'

Displays the table-related 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 non-clustered indexes that take the most amount of space:

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

As you can see, the SpaceUsedAnalyzer stored procedure provides the disk-space usage details you need to manage disk space more efficiently. For instance, you can use 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.

Download usp_SpaceUsedAnalyzer.sql.