Switching from allpages locking to data-only locking or vice versa is an expensive operation in terms of I/O cost. Most of the cost comes from the I/O required to copy the tables and re-create the indexes. Some logging is also required.
When moving from allpages to data-only locking or from data-only to allpages locking, alter table ... lock:
Copies all rows in the table to new data pages, formatting rows according to the new format. If you are changing to data-only locking, any data rows of fewer than 10 bytes are padded to 10 bytes during this step. If you are changing to allpages locking from data-only locking, padding is stripped from rows of fewer than 10 bytes.
Drops and re-creates all indexes on the table.
Deletes the old set of table pages.
Updates the system tables to indicate the new locking scheme.
Updates a counter maintained for the table, to cause the recompilation of query plans.
If a clustered index exists on the table, rows are copied in clustered index key order onto the new data pages. If no clustered index exists, the rows are copied in page-chain order for an allpages-locking to data-only-locking conversion.
The entire alter table...lock command is performed as a single transaction to ensure recoverability. An exclusive table lock is held on the table for the duration of the transaction.
