By: Chad Boyd | Updated: 2007-03-29 | Comments | Related: > 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
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
- This is a great tip to transition your queries, skills and knowledge from SQL Server 2000 to 2005.
- Check out these other tips that help the SQL Server 2000 to 2005 transition process:
- Special thanks to Chad Boyd of my MSSQLTips.com community for this tip.
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-03-29
