Useful query pulled from the Service Manager forums where a user (Victor Gacitua) stated they were having issues with a rather large Service Manager DB. http://social.technet.microsoft.com/Forums/en-US/systemcenterservicemanager/thread/78c93465-f72a-43c6-9623-55e9d82247d3
This will show the top 100 items in the DB and the page counts etc they use.
TOP 100 i.name, OBJECT_NAME(i.object_id), dps.used_page_count, dps.in_row_used_page_count, dps.lob_used_page_count, dps.row_overflow_used_page_count, dps.row_count
JOIN sys.partitions p
ON dps.partition_id = p.partition_id
JOIN sys.indexes i
ON p.index_id = i.index_id AND p.object_id = i.object_id
ORDERBY dps.used_page_count DESC