If you’ve already changed the locking scheme of a huge table from allpages locking (which was the locking scheme available in older versions of ASE) to datapages or datarows locking, you’ve noticed that it does take a long time and keeps you CPU pretty busy.

The reason is that when switching between allpages locking and data locking basically means copying the whole table and recreating the indexes.

Here are all the steps involved in such a change:

• All rows of the table are copied into new data pages and formatted according to the new locking scheme.
• All indexes are dropped and the re-created.
• The old table is removed.
• The information regarding this table in the system tables are updated.
• The table counter is updated to force recompilation of query plans.

Note that this is also the reason why some people use this switching back and forth as a way to defragment a table.

The first two steps are the one taking the most time. It’s difficult to estimate the time required for this. But you can get an idea by checking the size of the data and indexes for this table. This can be done using sp_spaceused:

1> sp_spaceused report
2> go
 name   rowtotal reserved   data      index_size unused
 ------ -------- ---------- --------- ---------- --------
 report 1837164  8377528 KB 687468 KB 7676644 KB 13416 KB

(1 row affected)
(return status = 0)

It doesn’t tell you how much time is needed but if you do it on different table, you could assume the time needed is almost proportional to the size of data+indexes.

Note that switching between datapages and datarows locking schemes only updates system tables and is thus pretty fast.