SQLServerPedia

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/