This is a follow-up this question which was about page size and performance of validation when using a SAN.
The current page size is 4K. The supplier of the hosted environment is insistent that we should try re-building the database using a 32K page size to get better I/O over the FCoE link to the EMC SAN for the validation / back-up process.
80% of our of rows are under 4K in size (90% of those in the critical core tables). Given all the warnings elsewhere about using too large a page size, what are the actual dangers concerning normal day-to-day usage of the system?
Any pointers would be helpful.
asked 27 Jan '15, 15:14
If each row in your table is 256 bytes long each 4k Page can contain 16 rows. When you use 32k Page each page can contain up to 128 rows.
So when a sample query use the data that is spread over 500pages the needed cache memory goes up from ~ 2MB to 16MB to hold the needed pages in cache.
So for the same data you need more memory.
At the same time a update to the table is written in page chunks. So if you update a page you lock much more rows at the same time. This comes into play when you have a high rate of updates on a table where many connection create and update date.
answered 28 Jan '15, 06:08