Lock escalation

MSSQL seems to be escalating locks to table locks while we do some write lock testing. What's the best way to handle MSSQL taking table locks? Does mssql support partitioning a table into different file groups?
thanksRE:
MSSQL seems to be escalating locks to table locks while we do some write lock testing.

Q1 What's the best way to handle MSSQL taking table locks?

Q2 Does mssql support partitioning a table into different file groups? Thanks

A1 The "best" way to handle MSSQL taking table locks depends on the specifics of the issue and the requirements of the design / situation.

For example (only) simply exceeding the number of available locks due to high levels of concurrent use would be very different from, say a situation in which inappropriately high isolation levels have been implemented. In the former one might increase memory available for locks, in the latter one might reduce the implemented isolation levels to the lowest possible levels that will not cause corruption issues.

A2 That type of requirement may be indirectly implemented in versions >=7.0.|||Originally posted by ryanveach
MSSQL seems to be escalating locks to table locks while we do some write lock testing. What's the best way to handle MSSQL taking table locks? Does mssql support partitioning a table into different file groups?

thanks

Hi!
Some facts.
If SQLServer cant lock a suitable row or range it is forced to lock the whole table. When sqlserver is forced to take so many row locks that it is more economic to take a table lock it "converts" the row locks to a table lock.

A1:Try to add suitable indexes to the tables in order to help sqlserver just to take row or range locks. If your aplication uses MTS or COM+ the isolation level is Serializable = expensive, but might be required for your app.