SQL Server Standard Reports – Database: Disk Usage by Partition: [DatabaseName]

In this continuing series on the Standard Reports in SQL Server Management Studio, we’re now up to the database-level reports. Today I’ll explain another report dealing with space in the database: Disk Usage by Partition: [DatabaseName].

To get to this report, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click any database. From the menu that appears, select Reports, then Standard Reports, and then this one.

I’ve shown you a couple of other disk usage reports, and this one has similar columns, but includes a breakdown of your tables and indexes by partition. No, these aren’t disk partitions like you’re used to working with at a very low level in the operating system, these partitions are created within SQL Server.

I’ve explained that you can use Filegroups to store files, and that you can place tables and indexes on these Filegroups, which helps you separate them out for performance and maintenance. But what about a single table or index that is “too large” (more on that in a minute) for a Filegroup? What I mean by “too large” has a lot to do with how you use the data. The classic example is a table with order history entries in it. Perhaps you need to keep the detail for a long time, but you don’t want have to pay the performance penalty whenever a table-scan operation happens or perhaps you want to have large amounts of insert or updates to the table and you’re running into maintenance issues.

You can split these tables out manually if you want and join them through views or programmatic means, but that requires some kind of logic to determine when you split the tables, and how you stitch them back together for the users. For this reason, in SQL Server 2005 we created Partitions in the database system. It involves creating a function and some other objects, and then you can use the table as you normally would and SQL Server will automatically create the breakpoints for you. Very handy. More info on Partitioning is here. 

But of course you need a way of tracking the layout of the data for reporting and size planning. That’s where this report comes in.

Here are the major parts of the report:

Column	Description
Table Name	You’ll actually get two possible entries (with bands) here: one for the table name, and if there’s an index, for that as well
# Records	This is the number of records in the entire table or index
Reserved (KB)	How much space, in Kilobytes, is reserved for the table and indexes
Used (KB)	How much space, in Kilobytes, is actually used out of the Reserved Space for the table and indexes
Partition No.	The partition number where the data space is being used – you’ll get one for each database partition you create, and of course you’ll always have at least one
# Records	The number of records in this partition, for this table or index
Reserved (KB)	How much space, in Kilobytes, is reserved for the table and indexes, on this partition
Used (KB)	How much space, in Kilobytes, is actually used out of the Reserved Space for the table and indexes, on this partition