Which Tables Are Taking Up How Much Space

This article explores a powerful T-SQL query that helps you identify tables and indexes that are consuming the most storage space in your database.

Tables and indexes, while essential for data organization and retrieval, can accumulate significant storage over time. Identifying space-consuming objects allows you to:

• Optimize Storage Utilization: Reclaim unused space and free up disk resources.
• Plan for Future Growth: Proactively allocate storage for anticipated data expansion.
• Target Archiving or Cleanup Strategies: Strategically archive or remove inactive data.

The Space Utilization Query:

The provided T-SQL query leverages several system views to gather detailed information about tables, indexes, and their storage footprint:

SELECT
  t.NAME AS TableName,
  i.name AS indexName,
  SUM(p.rows) AS RowCounts,
  SUM(a.total_pages) AS TotalPages,
  SUM(a.used_pages) AS UsedPages,
  SUM(a.data_pages) AS DataPages,
  (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
  (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
  (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM
  sys.tables t
INNER JOIN
  sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
  sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
  sys.allocation_units a ON p.partition_id = a.container_id
WHERE
  t.NAME NOT LIKE 'dt%' AND
  i.OBJECT_ID > 255 AND
  i.index_id <= 1
GROUP BY
  t.NAME, i.object_id, i.index_id, i.name
ORDER BY
  OBJECT_NAME(i.object_id);

Breakdown of the Query:

• Joins: The query joins several system views:
  • sys.tables: Provides information about tables.
  • sys.indexes: Contains details about indexes.
  • sys.partitions: Stores data on table partitions.
  • sys.allocation_units: Tracks storage allocation for data pages.
• Filtering: The WHERE clause excludes internal system tables (starting with “dt%”) and focuses on primary indexes (usually the first index with index_id <= 1).
• Aggregations: The query uses SUM aggregations to calculate:
  • RowCounts: Total number of rows in each table.
  • TotalPages, UsedPages, DataPages: Total, used, and data storage pages allocated for the tables and indexes.
• Calculations: The query calculates the total space, used space, and data space in megabytes for each table and index combination.
• Grouping and Ordering: The results are grouped by table name, object ID, index ID, and index name. Finally, the output is ordered by table name, making it easier to analyze.

Using the Results:

• Identify Space Consumers: Focus on tables with high TotalSpaceMB or UsedSpaceMB values.
• Analyze Row Counts: Consider archiving or purging inactive data based on row counts.
• Evaluate Indexes: Review large indexes and investigate potential fragmentation or redundancy.