solving sql server problems for millions of dbas and developers since 2006

Identify and resolve SQL Server problems BEFORE they happen with SQL diagnostic manager

SQL Product Highlight

Idera - SQL diagnostic manager

SQL diagnostic manager is a powerful performance monitoring and diagnostics solution that proactively alerts administrators to health, performance or availability problems within their SQL Server environment via a central console or mobile device. SQL diagnostic manager minimizes costly server downtime by providing agent-less, real-time monitoring and customizable alerting for fast diagnosis and remediation of SQL Server performance and availability problems. SQL diagnostic manager also provides a 'community' environment where, using Idera's IntelliFeed(TM) technology DBAs form a community t

Learn more!

Index System Tables in SQL Server 2000 vs SQL Server 2005

By: Chad Boyd | Read Comments | Print

Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

Related Tips: More

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

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.

Related Tips: More | Become a paid author

Last Update: 3/29/2007

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial!

Write, edit, and explore SQL effortlessly with SQL Prompt.

SQL Server Consultants - What you don't know could be your biggest asset - Guaranteed Results

Join the over million SQL Server Professionals who get their issues resolved daily.

Free Web Cast - What Are You Waiting For? Delivered by Jason Strate on Wednesday, March 14 @ 3:00 PM EST