Reducing lock contention

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.

max_rows_per_page applies to the data pages of a heap table or the leaf pages of an index. Unlike fillfactor, which is not maintained after creating a table or index, Adaptive Server retains the max_rows_per_page value when adding or deleting rows.

The following command creates the sales table and limits the maximum rows per page to four:

create table sales
      (stor_id         char(4)      not null,
      ord_num          varchar(20)  not null,
      date             datetime     not null)
      with max_rows_per_page = 4

If you create a table with a max_rows_per_page value, and then create a clustered index on the table without specifying max_rows_per_page, the clustered index inherits the max_rows_per_page value from the create table statement. Creating a clustered index with max_rows_per_page changes the value for the table’s data pages.