Ever wonder how many rows you store per page? Me too. So here's the query I use to investigate this:
Select object_name(i.object_id) As 'tableName'
, i.name As 'indexName'
, i.type_desc
, Max(p.partition_number) As 'partitions'
, Sum(p.rows) As 'rows'
, Sum(au.data_pages) As 'dataPages'
, Sum(p.rows) / Sum(au.data_pages) As 'rowsPerPage'
From sys.indexes As i
Join sys.partitions As p
On i.object_id = p.object_id
And i.index_id = p.index_id
Join sys.allocation_units As au
On p.hobt_id = au.container_id
Where object_name(i.object_id) Not Like 'sys%'
And au.type_desc = 'IN_ROW_DATA'
Group By object_name(i.object_id)
, i.name
, i.type_desc
Having Sum(au.data_pages) > 100
Order By rowsPerPage;What does this tell you? Well, the more rows you can fit on a page, the less IO you need to consume to retrieve those rows. It's also a good way to improve your buffer cache hit ratio (i.e. retrieve data from memory instead of disk, which is more efficient). So take a good look at those first few rows... do you have a small number of [rowsPerPage] but a large number of [rows]? If so, it may be time to look at re-designing your tables.
Happy Coding!
Michelle Ufford (aka SQLFool)
Source: http://sqlfool.com/2009/02/estimating-rows-per-page/
