Hobts and Allocation Units
In SQL Server 2000, the sysindexes table contains all the information you need to determine how much space a table, its LOB data, and all its indexes consume. In SQL Server 2005, you need a bit more.
SQL Server 2005 introduces a new logical structure, a hobt (pronounced—you guessed it—"hobbit"),to keep track of both heaps and B-Trees. (Indexes, including tables that have clustered indexes, are stored as B-Trees.) Although SQL Server 2005's syshobts table isn't typically visible, you can see references to hobts in some other system objects. For example, the sys.partitions view includes the column hobt_id, and every partition of every index or heap has a unique hobt_id value.
The sys.partitions view contains one row for each partition of each table and each index in the database, along with the number of rows in that partition. All tables and indexes in SQL Server 2005 contain at least one partition, even if you don't explicitly partition the structures. Each partition can contain data of up to three different storage types—regular IN_ROW data (for either data or index rows), LOB data, or ROW_OVERFLOW data—and each type of data is stored on its own set of pages. (In future columns, I'll discuss how SQL Server actually stores these three types of data.)
SQL Server allocates space for each of these three types of pages separately, and the set of pages of the same type for a single hobt for one partition is called an allocation unit. The sys.allocation_units view contains a row for each allocation unit. Every partition has at least an IN_ROW data allocation unit, and it might optionally have a LOB data allocation unit and a ROW_OVERFLOW allocation unit, depending on the table or index definition.
The sys.allocation_units view has a column called container_id, which maps to the partition_id in sys.partitions. The view also has columns that report how many pages are reserved for the allocation unit and how many of those reserved pages have been used. In addition, sys.allocation_units contains a type column, which holds one of three values that indicate which type of pages it's keeping track of:
- 1 = IN_ROW data
- 2 = LOB data
- 3 = ROW_OVERFLOW data
Investigating with sp_spaceused
To determine the amount of space reserved and used for a particular object in the current database, as well as the number of rows for all partitions of that object, you can call the sp_spaceused stored procedure.You can also use sp_spaceused to find the disk space reserved and used by the entire database.To retrieve this information, sp_spaceused performs a simple join between the sys.partitions and sys.allocation_units views that looks similar to the join in Listing 1's sample code.
Note that the procedure retrieves information about pages used from the sys.allocation_units view and accesses row-count information from the sys.partitions view. Sp_spaceused doesn't count rows when sys.partitions indicates the partition is for an index or when sys.allocation_units indicates the data isn't IN_ROW data.
The variable in the WHERE clause at the end of Listing 1 is the object ID you want information about. The sp_spaceused procedure has determined this ID based on the parameters passed to the procedure, but you could use the object_id value to specify any object you were interested in. For example, if you wanted space-usage information for the Sales.SalesOrderDetail table in the AdventureWorks database, you could replace the WHERE clause in Listing 1 with
WHERE p.object_id =
object_id
('Sales.SalesOrderDetail')
Give Me More Space
This column gives you just a glimpse of the full storage picture in SQL Server 2005.The new release has other structures besides tables and indexes that can take up space, including full-text indexes, XML indexes, and structures to support query notifications and SQL Server Service Broker operations. The sp_spaceused stored procedure also needs to take those structures into account, particularly when reporting space usage for the entire database.
Next month, I'll continue my coverage of the information that sp_spaceused needs. I'll also explain how SQL Server 2005 handles other information that the sysindexes table keeps track of in SQL Server 2000.