By: Chad Boyd | Updated: 2007-10-15 | Comments (1) | Related: > Indexing
A customer asked me today how they could retrieve information similar to that available in the "sysindexes" system table from Sql Server 2000 in Sql Server 2005 using the new catalog views. Though in Sql 2005 there is a dbo.sysindexes and sys.sysindexes compatability 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 (which you can now do in Sql 2005 unlike in Sql 2000...see my prior post titled "Row sizes exceeding 8060 bytes in Sql 2005" for more information on this type of data).
The reasons for having to move away from a single table approach to reporting this type of data in Sql 2005 revolve primarily around 2 new features in 2005: 1) partitioning 2) row-overflow data. 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 is the methodology by which variable-length data in excess of 8060 bytes for a single row is stored in additional pages seperate from the row's primary data (in row). Again, see my prior post mentioned above for more information on this topic.
There are 3 catalog views in Sql 2005 that provide you the appropriate insight for this information, and they are:
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. This view also contains a column called hobt_id (hobt is pronounced "hobbit", and stands for "heap or b-tree") - for information on this column and it's meaning, see my post titled "What is a hobt_id in Sql 2005?".
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 2005 is a collection of pages of a single type for a given partition/hobt. A single partition could have as many as 3 different allocation units, 1 for each of the 3 page types in Sql 2005: In-row data (standard data/index pages), row-overflow data (variable length data for a given row in excess of 8060 bytes), and 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/hobt 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 (it also currently always relates to the hobt_id value from sys.partitions, but again, see my other post for information here :-)).
Now that we understand what each of these views provide and their relationship with one another, we can easily write some simple queries to get the information we are looking for.
To get the current total number of rows for the heap or clustered index in a table named 'tblTest' (which will be the number of rows in the table), try the following:
select object_name(i.object_id) as objectName, i.name as indexName, sum(p.rows) as rowCnt
from sys.indexes i
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
where i.object_id = object_id('tblTest')
and i.index_id <= 1
group by i.object_id, i.index_id, i.name
To get the current total number of pages, used pages, and data pages for given heap/cluster in a table named 'tblTest', try the following (NOTE that this will include ALL 3 page types in sum):
-- Total # of pages, used_pages, and data_pages for a given heap/clustered index
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
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
join sys.allocation_units a
on p.partition_id = a.container_id
where i.object_id = object_id('tblTest')
and i.index_id <= 1
group by i.object_id, i.index_id, i.name
If you'd like the same information as above, however aggregated per page type (i.e. In-row data, Row-overflow data, LOB data), simply modify the query slightly as follows and you'll now get a single aggregate row per page type (with an added rollup bonus for totaling per group):
-- Total # of pages, used_pages, and data_pages for a given heap/clustered index by page type
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
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
join sys.allocation_units a
on p.partition_id = a.container_id
where i.object_id = object_id('tblTest')
and i.index_id <= 1
group by i.object_id, i.name, a.type_desc with rollup
You can obviously feel free to modify/expand on any of the above to form your own aggregations, forumlas, etc. Enjoy!
About the author
Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-10-15
