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.
