Wednesday, August 17, 2011

Refactoring sp_spaceused

A while back I refactored sp_spaceused into CTEs.  One query would return the total for a database, the other would return totals for individual user objects within the current execution context.  This only accounted for on-page data, which the original stored procedure does manage.  I keep finding myself running the object level query to determine which tables are index heavy or are ready for historical archiving, both of which are especially handy when you're working with the storage limitation of Express Edition.  (Sample use cases)

Disclaimer: By following any of these activities you hold the author harmless of any effects, averse or otherwise.  There's no fitness or guarantee for this information. 

WITH size_calculations (objectid, reserved_pages, used_pages, pages, num_rows)AS (SELECT [OBJECT_ID] AS [objectid]
  
, SUM(reserved_page_count) AS [reserved_pages]
  
, SUM(used_page_count) AS [used_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
  
) AS [pages]
  
, SUM( CASE
          
WHEN (index_id < 2) THEN row_count
          
ELSE 0
      
END
  
) AS [num_rows]FROM sys.dm_db_partition_stats GROUP BY [object_id])SELECT OBJECT_NAME(SC.objectid) AS [name]
  
, SC.num_rows AS [rows]
  
, (SC.reserved_pages * 8) AS [reserved_kb]
  
, (SC.pages * 8) AS [data_kb]
  
, ((CASE WHEN SC.used_pages > SC.pages THEN (SC.used_pages - SC.pages) ELSE 0 END) * 8) AS [index_size_kb]
  
, ((CASE WHEN SC.reserved_pages > SC.used_pages THEN (SC.reserved_pages -SC.used_pages) ELSE 0 END) * 8) AS [unused_kb]

FROM size_calculations AS SC
INNER JOIN sys.objects AS O
ON SC.[objectid] = O.[object_id]
WHERE O.TYPE = 'U'
ORDER BY SC.reserved_pages DESC;

No comments:

Post a Comment