Transact-SQL To Get Index Page Counts For A Database
One of the attendees of my session on metadata last Saturday asked about how to get at this information. We were already time constrained at 50 minutes per session so I was not able to fit that query in with the remaining items I wanted to show.
The following query starts with the catalog view sys.partitions. This might throw people as they would assume that partitioning must be implemented to make use of this view. One of the architectural changes made in SQL Server 2005 is that all tables are technically on a partition (the value being 1 if non-partitioned). We also make use of sys.destination_data_spaces, sys.allocation_units, and a few other general catalog views.
SELECT sc.name AS SchemaName
,t.name AS ObjectName
,i.name AS IndexName
,i.type_desc AS IndexType
,f.name AS FileGroupName
,[rows] AS NumberOfRows
,au.total_pages AS TotalPages
FROM sys.partitions AS p
INNER JOIN sys.tables AS t ON t.[object_id] = p.[object_id]
INNER JOIN sys.schemas AS sc ON sc.[schema_id] = t.[schema_id]
LEFT JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
LEFT JOIN sys.destination_data_spaces AS dds ON
dds.partition_scheme_id = i.data_space_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS f ON f.data_space_id = i.data_space_id
LEFT JOIN (
SELECT container_id, SUM(total_pages) AS total_pages
FROM sys.allocation_units
GROUP BY container_id
) AS au ON au.container_id = p.partition_id
WHERE i.index_id > 0
ORDER BY SchemaName, ObjectName, IndexName;This query also returns the filegroup the particular index uses. If you aren’t currently utilizing multiple filegroups for your database I would strongly recommend investigating that option.
Let me know if you have questions (or requests). Enjoy…