Always rowlock?

I noticed with 4.0 SP1 (may be in other versions, I don't know), that when using SQL 2000, an option in Alter Database is Always Rowlock. Currently, it is not checked off.

I'm wondering what this really will do. I've seen other posts about record and table level locking, but I didn't see anything on this setting in particular.

Is it advisable to enable this? We just upgraded to 4.0, with the help of our NSC, and we are seeing some of the inevitable speed and locking problems that we didn't have when we were on 2.6 code with 3.7 clients.
  • So you are not running 2.6 app code from 4.0? If not, it would explain the difference in performance.

    The Always Rowlock when checked causes locking behaviour to be that of 4.0, 3.70 and all versions prior, for the SQL version. That is, all SELECT statements contain a ROWLOCK directive that forces SQL Server to place row locks, when locking.

    From 4.0 SP1 onwards, this has changed. The ROWLOCK is not issued which means SQL Server has the freedom to choose at what level it will lock; row, page or table. The up side of this is that it is a big reduction in lock maintenence and memory required for many rowlocks, which instead will be replaced with page locks. Therefore improved performance. The down-side is that page locking is not as fine-grained and therefore a user can be locking 'too much' data that can impact other users. Therefore reduced concurrency.

    Either way there can be problems of different kinds. But the use of ROWLOCK should be unnecessary is the server is able to utilize lock levels well. I would leave it unchecked.
  • We are using 4.0 code, just with our modifications, which were pretty extensive in some cases, especially in the sales header processing area.

    Either way I wasn't sure what the purpose of this was, so now you have answered it. Thanks. I will leave it unchecked.