Analyze table space usage in SQL Server


It is pretty useful to check space usage of your tables. SQL server gives a nice system stored procedure called sp_spaceused that returns what you want. You can run the following script on your database and export the results to excel.

You can come up with nice graphs showing why your data base is big and what partitioning strategy to choose. For example I ran this query for a project with a 10GB database. I could not understand why only a few million small records  occupy 10GB. The result was expectable. There was an exception log table which occupied half of the space and dropping that table saved so much time for everybody.


DECLARE @spu TABLE(NAME VARCHAR(max), [ROWS] BIGINT, reserved VARCHAR(50), DATA VARCHAR(50), index_size VARCHAR(50), unused VARCHAR(50)
)

DECLARE @TName VARCHAR(80)

DECLARE C CURSOR FAST_FORWARD READ_ONLY FOR
SELECT o.name FROM sys.objects AS o
WHERE o.schema_id = SCHEMA_ID('dbo')
AND o.type = 'U'

OPEN C

FETCH NEXT FROM C INTO @Tname

WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO @spu
	EXEC sys.sp_spaceused @objname = @TName -- nvarchar(776)

FETCH NEXT FROM C INTO @Tname

END

CLOSE C
DEALLOCATE C

SELECT * FROM @spu ORDER BY DATA DESC
Advertisements

5 Responses to “Analyze table space usage in SQL Server”

  1. baodad Says:

    Your script skips over tables names with dots. For instance, there is a table in my database called ‘Audit.Data’. For such tables, sp_spaceused needs to be called like this: exec sp_spaceused ‘Audit.Data’. But your script only feeds it the name of ‘Data’ (its name in sysobjects), so sp_spaceused fails for those tables.

    • naiem Says:

      Boadad, the script only looks for the tables in schema ‘dbo’. It should be slightly modified to include other schemas. If the table’s name in sys.objects is Data, then Audit is a schema name and you can use scripts such as select * from Audit.data. However, if the dot was part of the table name, above script would work, and you had to use select * from [Audit.Data] instead.

      • baodad Says:

        Thanks to your response, now I understand that “Audit” is a different schema for my database. I got your query to work for me by substituting this in:

        SELECT s.name + ‘.’ + o.name
        FROM sys.objects o
        JOIN sys.schemas s on o.schema_id = s.schema_id

        Best regards.

  2. Kk.False Says:

    To have more accurate values, it’s necessary to do a “UpdateUsage”
    http://technet.microsoft.com/en-us/library/ms188414.aspx
    Another more “elegant” way is to use “sp_MSforeachtable” to treat all the tables insteat of a cursor.

    — Update stats
    DBCC UPDATEUSAGE (0)
    go
    — Size for each tables
    EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”
    go

    • baodad Says:

      @Kk.False, Thanks for your contribution to this discussion. I have read that sp_MSforeachtable is undocumented and unsupported. Do you think it will ever go away?
      Also, on http://technet.microsoft.com/en-us/library/ms188414.aspx there are some best practices listed for DBCC UPDATEUSAGE, the first of which reads: “Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.”


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: