1) With a larger page size you can also have larger varchar columns
With a larger page size more memory is allocated per read, and since more data (rows) is read into memory per page it can result in an increase in performance for e.g. reporting
You might need more disk space. 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 a 8K server.
Since more rows can fit onto a page it means that with allpage locking more rows are locked when the page is locked, decreasing concurrency. You might want to use row level locking if you don’t already.
2) External RAID devices normally have a write cache with a battery backup that function independent of the OS. It can therefore outperform your local filesystem which has to wait for the write to complete (dsync/direct IO) to ensure integrity in case of OS/power failure.
What about performance of inserts and updates?.All our tables are allpages locking tables.
Like I said before; Since more rows can fit onto a page it means that more rows are locked decreasing concurrency (preventing others from accessing previous available rows). You might want to switch to row level locking.
Originally Posted by bsr
Another question is Can I load the dump of 2k database on a 4k database?