We have recently added row level locking to 2 tables in a highly transactional database and observed the CPU utilization increase quite a bit. Does anyone have any idea the amount of overhead that may be associated within Sybase compared to the All pages locking schemes?? or what the costs??? Also what part of the Sysmon report should we be monitoring more closely?? Thank you in advance.
There will be more locks required typically. Make sure you have a sufficient number (e.g. sp_configure "number of locks").
Also remember that clustered indexes no longer provide the same functionality on DOL tables as APL tables. If you're relying on clustered indexes to do range queries and other clustered-index adventageous queries they could be less efficient now and require more CPU.
Make sure you run reorg every so often to defrag the table if need be, and try update index statistics.