The diagnostic information that a DBA generally works with for a database falls into three groups: sizing, indexing, and query execution.
Size needs to be tracked and managed for capacity planning purposes. You can calculate space utilization for the database as well as for tables, indexes, and indexed views.
The space utilization for a database can be retrieved from the size column in the sys.database_files DMV.
Space utilization for a table, index, or indexed view is a bit more challenging as you have to deal with multiple DMVs that map objects through various storage structures. Every object in SQL Server 2008 is associated to a partition, even if the object is not partitioned. Therefore, each table, index, and ...