I have some databases that we are wanting to convert from 2K to 8K pages. Nobody here has done that. Has anyone done that and, if so, what are the "gotchas" I should look out for?
I was planning on creating a new Sybase server with the 8K pages and doing a logical dump of the 2K page database and doing a logical load into the new 8K page database utilizing SQL Backtrack instead of using sybmigrate.
When a text or image column is initialised, a valid text pointer to the column is inserted into the table, and an entire data page is allocated to the column (2, 4, 8, or 16K depending on your logical page size).
That means if you have a 1 GB table with a text or image data type on your 2K server, it will use 4 GB of space on your 8K server.
Since more rows can fit onto a page it means more rows are locked with page level locking. You might want to use row level locking if you don’t already.
if you have small highly transactional tables (insert,update,deletes) you may see lock contention occuring as more rows can fit on a page, which increases chances that two different processes are trying to access the same page simultaneously. this may require you change the locking scheme to reduce any lock contention problems.