How Many Pages In My Table
Published: 21st January 2014
This is the natural follow-up to my previous post about the 8k page. It’s all well and good knowing that SQL Server works in 8k pages and that these directly influence your read figures, but we don’t want to sit there and calculate how many records there are per page in order to know how many data pages exist in our table.

Therefore we will pull up a couple of simple queries that will show how many data pages are in each level of SQL Server… database, table, and index.

These are simple queries you can happily run on any system.

The following query shows how many data pages there are in your database as a whole:

select db_name() databaseName,
      
sum(p.rows) totalRows,
      
sum(a.data_pages) numDataPages
from sys.objects o
join sys.indexes i
on o.object_id = i.object_id
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 o.is_ms_shipped = 0
and o.type = 'U'


This query then breaks that into each table within your database. Note that a table includes all indexes, therefore a table with 1 clustered index and 4 non-clustered indexes, each with 1 data page, will show as 5 data pages in size:

select t.name tableName,
      
sum(p.rows) totalRows,
      
sum(a.data_pages) numDataPages
from sys.objects o
join sys.tables t
on o.object_id = t.object_id
join sys.indexes i
on o.object_id = i.object_id
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 o.is_ms_shipped = 0
and o.type = 'U'
group by t.name


This last query shows the number of data pages per index or heap:

select object_name(i.object_id) tableName,
      
i.name indexName,
      
p.rows totalRows,
      
a.data_pages numDataPages
from sys.objects o
join sys.indexes i
on o.object_id = i.object_id
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 o.is_ms_shipped = 0
and o.type = 'U'


These can be very useful when validating the number of reads a query is producing in order to see if it’s being efficient or looping excessively through the data (possibly due to outdated stats) etc.
Comments:
NB: Comments will only appear once they have been moderated.