THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Sergio Govoni

A place where share my experience on SQL Server and related technologies

The biggest table in your database

You may need to know which is the biggest table (in terms of disk space usage) in the database that you are currently connected. Sometimes, this information is very useful to check the indexing strategy of these tables.
 
There are several methods to know which is the biggest table of a database, one of these methods is to use the standard report "Disk Usage by Top Table" exposed by SQL Server Management Studio. Another method is through T-SQL language, you can perform the sp_spaceused system stored procedure for each table contained in your database, you could store all the partial results in a temporary table and ordering these results by the "data" column, so you can find the biggest table of the database.
 
Using the sp_spaceused system stored procedure we have to accept a row-by-row solution, in fact, we will perform a call to sp_spaceused for each table contained into the database. Increasing the number of tables, the number of calls to the stored procedure will grow up.
 
An alternative solution is represented by the following Common Table Expression based on the internal code of the sp_spaceused system stored procedure, it allows us to obtain the result with a single execution, in a set-based way.
 
WITH spaceused AS
(
  SELECT
    sys.dm_db_partition_stats.object_id
    ,reservedpages = SUM(reserved_page_count)
    ,it_reservedpages = SUM(ISNULL(its.it_reserved_page_count, 0))
    ,usedpages = SUM(used_page_count)
    ,it_usedpages = SUM(ISNULL(its.it_used_page_count, 0))
    ,pages = SUM(CASE
                   WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                   ELSE lob_used_page_count + row_overflow_used_page_count
                 END
                )
    ,row_Count = SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END)
  FROM
    sys.dm_db_partition_stats
  JOIN
    sys.objects ON sys.objects.object_id=sys.dm_db_partition_stats.object_id
  OUTER APPLY
    (SELECT
       reserved_page_count AS it_reserved_page_count
       ,used_page_count AS it_used_page_count
     FROM
       sys.internal_tables AS it
     WHERE
     it.parent_id = object_id
     AND it.internal_type IN (202,204,211,212,213,214,215,216)
     AND object_id = it.object_id
  ) AS its
  WHERE
    sys.objects.type IN ('U', 'V')
  GROUP BY
    sys.dm_db_partition_stats.object_id
)
SELECT
  name = OBJECT_NAME (object_id)
  ,rows = convert (char(11), row_Count)
  ,reserved = LTRIM (STR (reservedpages * 8, 15, 0) + ' KB')
  ,it_reserved = LTRIM (STR (it_reservedpages * 8, 15, 0) + ' KB')
  ,tot_reserved = LTRIM (STR ( (reservedpages + it_reservedpages) * 8, 15, 0) + ' KB')
  ,data = LTRIM (STR (pages * 8, 15, 0) + ' KB')
  ,data_MB = LTRIM (STR ((pages * 8) / 1000.0, 15, 0) + ' MB')
  ,index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,it_index_size = LTRIM (STR ((CASE WHEN it_usedpages > pages THEN (it_usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,tot_index_size = LTRIM (STR ((CASE WHEN (usedpages + it_usedpages) > pages THEN ((usedpages + it_usedpages) - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,unused = LTRIM (STR ((CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
FROM
  spaceused
ORDER BY
  pages DESC;
 
Are you thinking how can we access to the internal code of the sp_spaceused? It is simple, we can do that using the sp_helptext system stored procedure!
 
Have fun! 
Published Friday, February 12, 2016 1:34 AM by Sergio Govoni

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement