Я в последнее время разочарован SQL Server 2000 именно в этой области.
Если не использовать хинтов, то SQL server с ростом числа пользователей всё больше скатывается к massive table locks.
Почему все апдейты и инсерты для малого количества записей я теперь явно прописываю с WITH(ROWLOCK). Если вставка в среднем более чем страницы записей, то пишу WITH(PAGLOCK).

Большинство SELECT, некритичных к целостности представления, я прописываю как WITH(NOLOCK).

Уровень изоляции транзакции при этом я выставляю на READ COMMITTED.

К сожалению, только при такой практике у нас получилось заставить нормально функционировать нашу ИС. Знаете, с этой точки зрения я сожалею о том, что я работаю с MS SQL.

Да, ещё одно. все массивные вставки с кучей служебных дейвствий по возможности надо переформировать, возможно, с использованием временных таблиц или таблиц-переменных, чтобы массивная вставка была максимально быстрой без отвлечения на бизнес-логику. Иначе - table locks.