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