I have inheirted several servers running solaris and sybase 12.5.4 with an 8k page size. We will be migrating to linux and 15.0.3. My problem is the page size, we are mixed OLTP and DSS however there is a real push to get data in as fast as possible. We have alot of deadlocks and at times data insert performance is slow. Along with other changes I want to go to a smaller page size. My thoughts are 2k. Row level locking seems to cause alot of row forwarding. My sysmon output shows not a lot of use of my 64k buffer pool(I reduced the size several times now.) and I dropped my 16 and 32k pools because they got almost no use at all. Only 3 tables need to change because of row/column size.

I am looking for a sanity check, all the documentation I see seems to say bigger page size but I think I need to go smaller, am I crazy? I need to find some documentation on how to determine page size to sell the change to managment.

Thanks
PJ
Thanks for any thoughts.

Ok, let us take it step by step.

First the problem of insert performance, slow inserts may mean a few things, first and the most obvious is the contention of the last page, which may be due to the lack of primary keys/indexes ont he tables in question. If its a heap table, then inserts will be slower. Identify which tables are slower during inserts and check the table structure.

I know it is late but thanks for the response. In answer to your question yes on most of the tables we have good indexes. We are however cursed with a really bad database design. We decided to go with a 4k page. Part of our problem is that while we have 1 process inserting into our tables we have 4 or 5 processes trying to get the latest data back out. The less data locked at any given moment the better off we are.

Thanks
PJ

even later :-)

I don´t think page size is your problem but the locking mechanism is, maybe the high transactional tables of yours have a "allpages" lock

SyBooks Online

consider Datapages as default and Datarows locking for the high transactional tables, this will solve your problems better than downsizing the page size.