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.)