Maximum number of rows per page

You can now configure the maximum number of rows stored on a data page or index leaf page. The max_rows_per_page value specified in a create table, create index, or alter table command restricts the number of rows allowed on a data page, a clustered index leaf page, or a nonclustered index leaf page. This reduces lock contention and improves concurrency for frequently accessed tables.

The max_rows_per_page value applies to the data pages of an unindexed table or the leaf pages of an index. The value of max_rows_per_page is stored in the maxrowsperpage column of sysindexes. (The maxrowsperpage column was named rowpage in previous releases of SQL Server.)

Unlike fillfactor, which is not maintained after creating a table or index, SQL Server retains the max_rows_per_page value when adding or deleting rows. For information about how to use max_rows_per_page to reduce lock contention and improve concurrency for your server, see the Performance and Tuning Guide.