If queries with high performance requirements use clustered indexes to return large numbers of rows in index order, you may see performance degradation if you change these tables to use data-only locking. Clustered indexes on data-only-locked tables are structurally the same as nonclustered indexes.
Placement algorithms keep newly inserted rows close to existing rows with adjacent values, as long as space is available on nearby pages.
Performance for a data-only-locked table with a clustered index should be close to the performance of the same table with allpages locking immediately after a create clustered index command or a reorg rebuild command, but performance, especially with large I/O, declines if cluster ratios decline because of inserts and forwarded rows.
Performance remains high for tables that do not experience a lot of inserts. On tables that get a lot of inserts, a System Administrator may need to drop and re-create the clustered index or run reorg rebuild more frequently.
Using space management properties such as fillfactor, exp_row_size, and reservepagegap can help reduce the frequency of maintenance operations. In some cases, using the allpages locking scheme for the table, even if there is some contention, may provide better performance for queries performing clustered index scans than using data-only locking for the tables.
