Index System Tables in SQL Server 2000 vs SQL Server 2005


By:   |   Updated: 2007-03-29   |   Comments   |   Related: More > Indexing

Problem
The primary resource I used in SQL Server 2000 to capture index related information was master.dbo.sysindexes.  I would use this system table to capture information on row and page counts or total size information for a specific table. I know with SQL Server 2005, it is not recommended to use deprecated system tables.  Unfortunately, I am having trouble figuring out which new system objects to query in order to capture the index related information that I am accustomed to with SQL Server 2000.  As such, where can I find this information using the new SQL Server 2005 catalog views and dynamic management views?

Solution
SQL Server 2005 provides three new catalog views to access the information you are accustom to viewing.  These catalog views include:

Catalog View Description
sys.indexes Contains a single row per index/heap in the database, with information such as name, index_id, type, uniqueness, etc.)
sys.partitions Contains a single row per index per partition, with information such as the partition_id (unique per partition), object_id (object the partition belongs to), index_id (index the partition belongs to), and rows (count of rows in the given partition).  At a minimum, there is always at least 1 row per index entry in sys.partitions, even if you are not using partitioning at all.

A single index can have up to 1,000 partitions currently.

sys.allocation_units Contains a single row per partition per page type/allocation unit, with information such as allocation_unit_id, type, container_id, data_space_id, total_pages, used_pages, and data_pages. An allocation unit in SQL Server 2005 is a collection of pages of a single type for a given partition. A single partition could have as many as 3 different allocation units, 1 for each of the 3 page types in SQL Server 2005
  • In-row data (standard data/index pages)
  • Row-overflow data (variable length data for a given row in excess of 8060 bytes)
  • LOB-data (large object data such as text, ntext, image, any of the MAX data types, and CLR UDT's)

At a minimum, each partition will always have an allocation_unit record for In-row data. The "container_id" value for a given allocation_unit relates to the partition_id value from sys.partitions.

Why do I need to use these new catalog views?

Although in SQL Server 2005 there is a sys.sysindexes compatibility view, it doesn't return detailed information, particularly when using partitioning, large object data (LOB data), and/or variable character data over 8060 bytes in a single row. Additionally, it isn't recommended to make use of these deprecated compatibility views in SQL Server 2005.

Why do I need to start to use these new views?

Besides the fact that the previous views will be depreciated, the reasons for having to move away from a single table approach to reporting this type of data in SQL Server 2005 revolve primarily around 2 new features in 2005:

  • Partitioning - Partitioning allows you to split different portions of a given table/index into multiple segments (partitions) based on a partitioning column that can optionally be stored in multiple filegroups. Given this, a single index can be made up of multiple partitions, each of which must have data sizes, page counts, etc. tracked and stored.
  • Row-overflow data - Row-overflow data is the methodology by which variable-length data in excess of 8060 bytes for a single row is stored in additional pages separate from the row's primary data (in row).

How can I query these new views?

The following query will return the current total number of rows for the heap or clustered index for a table named 'tblTest':

SELECT object_name(i.object_id) as objectName, i.[name] as indexName, sum(p.rows) as rowCnt
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
WHERE i.object_id = object_id('dbo.Meeting')
AND
i.index_id <= 1
GROUP BY i.object_id, i.index_id, i.
[name]


The following query will return the current total number of pages, used pages, and data pages for given heap/cluster for a table named 'tblTest':

SELECT object_name(i.object_id) as objectName, i.[name] as indexName,
sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,
(
sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE i.object_id = object_id('dbo.Meeting')
AND
i.index_id <= 1
GROUP BY i.object_id, i.index_id, i.
[name]


The following query will return the same information as the query above, just aggregated per page type (i.e. In-row data, Row-overflow data, LOB data) for a table name 'tblTest':

SELECT case when grouping(i.object_id) = 1 then '--- TOTAL ---' else object_name(i.object_id) end as objectName,
case when grouping(i.[name]) = 1 then '--- TOTAL ---' else i.[name] end as indexName,
case when grouping(a.type_desc) = 1 then '--- TOTAL ---' else a.type_desc end as pageType,
sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,
(
sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE i.object_id = object_id('dbo.Meeting')
AND
i.index_id <= 1
GROUP BY i.object_id, i.[name], a.type_desc with
rollup

Next Steps



Last Updated: 2007-03-29


get scripts

next tip button



About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Difference between SQL Server Unique Indexes and Unique Constraints

Building SQL Server Indexes in Ascending vs Descending Order

Script out all SQL Server Indexes in a Database using T-SQL

Creating Indexes with SQL Server Management Studio

How to get index usage information in SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools