As you learned last month in "Managing Data Space," InstantDoc ID 50009, SQL Server 2005 manages the storage space that database objects (i.e., tables, indexes, and large objects—LOBs) use differently than SQL Server 2000 does. The main difference is that SQL Server 2005 provides no single table or view that contains information about space-consuming objects that's analogous to the sysindexes system table in SQL Server 2000. Instead, SQL Server 2005 provides several new views that contain information formerly available through sysindexes. Let's take a closer look at these views and some examples of queries that you can use to examine the storage-related metadata the views contain.
A New View on Indexes
SQL Server 2005 replaces the sysindexes table with the sys.sysindexes compatibility view and the sys.indexes catalog view. As I explain in "Seek and You Shall Find," September 2005, InstantDoc ID 46929, SQL Server 2005 provides backward-compatibility views of SQL Server 2000 system tables that you can use to ensure that your old applications work when they're run against SQL Server 2005.The sysindexes view returns all the same columns as the old sysindexes table; however, many of the columns aren't even populated.
You can see the definition of the SQL Server 2005 sysindexes view by running the following statement:
SELECT object_definition (object_id('sys.sysindexes'))
(Some code lines in the article and listings wrap to multiple lines because of space limitations.) In the statement's results, notice that many of the columns are set to 0 or null, including all the reserved columns, plus pgmodctr, keys, and statblob.
You can also see an undocumented property value called keycnt80 that's used with the INDEXPROPERTY function. The sysindexes table in SQL Server 2000 had a property value called keycnt, but none of the SQL Server 2005 views that replace sysindexes have such a column. Although SQL Server 2005 Books Online (BOL) doesn't show keycnt80 as a possible value for INDEXPROPERTY, the definition of the sys.sysindexes compatibility view uses keycnt80 with INDEXPROPERTY to populate the keycnt column in the view. SQL Server 2005 provides key information in the sys.index_columns view but has no one column that keeps track of the number of keys, so you might find this undocumented INDEXPROPERTY parameter useful.
The new sys.indexes catalog view essentially shows only property information about each index. The view columns indicate whether the index is clustered or nonclustered, whether it's unique or nonunique, whether the index supports a primary key or unique constraint, what kinds of locks are allowed, and other similar information. Search sys.indexes in BOL to see the complete list of columns in the view. Note that sys.indexes contains no information about the index's structure or size or the location of the index pages.
Views on Partitions
As I discuss in "Managing Data Space," the sys.partitions view has 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 have at least one partition even if they aren't explicitly partitioned. To help familiarize you with the information in this view, I'll create a partitioned table, then compare the metadata for partitioned and nonpartitioned tables.
The code in Listing 1 creates two tables, each of which has three columns and 1000 rows of random data. The partition function PF1 defines five partitions based on the four range endpoints 1, 2, 3, and 4. The partition schema PS1 places all the partitions on the primary file group. (Be aware that putting all the partitions on the same file group isn't something you'd typically do to take full advantage of partitioning, but this partitioning structure is much easier to set up and still suits our purposes in examining the metadata in sys.partitions.) Test1 is created as a partitioned table, applying the partition scheme PS1 to the values in column a. The table test2 is a copy of the data in test1 but is nonpartitioned.
The following query examines the rows in sys.partitions for these two tables and returns the data that Table 1 shows. Be aware your data will be slightly different, due to the random data being generated.
SELECT object_name(object_id)
AS name, partition_id,
partition_number AS pnum,
hobt_id, rows
FROM sys.partitions
WHERE object_name(object_id)
LIKE 'test%'
As you can see, the query returns five rows for test1, which was created on five partitions, and one row for test2. Notice that the partition_id value is the same as the hobt_id value for all rows; you should see this relationship for all your objects. Of course, the actual values for hobt_id and object_id that you'll get will probably be different, and your number of rows in each partition can be different since they're generated randomly.
The reason that SQL Server 2005 has both the partition_id and hobt_id columns is to allow for future expansion. You can think of a partition as being a rowset, or a set of rows, that's stored in one location and a heap or B-tree (HOBT) as being a part of table or index stored in one location. In SQL Server 2005, there's a one-to-one relationship between rowsets and HOBTs, but future versions might let you have multiple HOBTs for the same rowset.
Prev. page
[1]
2
next page 
