Вот что по этому поводу говорят на SQL Tips at Builder.com
Specify locking with sp_indexoption

The system procedure, sp_indexoption, is used to turn off or on
page-level and/or row-level locking. Under normal operation, SQL Server
automatically makes choices of page-, row-, or table-level locking. It is not
necessary to set these options manually. However, sp_indexoption is
provided for expert users who know with certainty that a particular type of
lock is always appropriate.

The system procedure takes three parameters. The first parameter,
@IndexNamePattern, is a qualified index or table name. If only the table
name is used and not the qualified index name, all indexes are affected.

The second parameter, @OptionName, can be one of the following four
options:

AllowRowLocks
DisAllowRowLocks
AllowPageLocks
DisAllowPageLocks

The third parameter, @OptionValue, specifies whether the setting is:

Enabled--True, On, 1
Disabled--False, Off, 0

You can use the metadata function, INDEXPROPERTY, to find if the
options of sp_indexoption system procedure have been set. Following is
sample script to illustrate the use of this function:


USE NorthWind
GO
-- 0=Page locking is allowed.
SELECT INDEXPROPERTY (
OBJECT_ID('Orders'),'PK_Orders','IsPageLockDisallowed')
-- 0=Row locking is allowed.
SELECT INDEXPROPERTY
(OBJECT_ID('Orders'),'PK_Orders','IsRowLockDisallowed')
GO


Please use the sp_indexoption system procedure with caution. You can
disallow the more granular locking mechanisms on a database causing the
locks to escalate to less granular locking mechanisms as in page-level and
table-level locking. Depending on the size of the table and type of
processing on the table, this could cause significant blocking within the
database.

Following is a sample script used to demonstrate the use of the
sp_indexoption:


USE NorthWind
GO
SET NOCOUNT ON
GO
EXEC sp_indexoption @IndexNamePattern = 'Orders', @OptionName =
'AllowRowLocks', @OptionValue = 'no'
GO
SELECT 'Orders',
CASE
INDEXPROPERTY
(OBJECT_ID('Orders'),'CustomerID','IsRowLockDisallowed')
WHEN 1 THEN 'Cannot Row Lock '
WHEN 0 THEN 'Can Row Lock '
ELSE 'Unknown'
END 'RowLock',
CASE
INDEXPROPERTY
(OBJECT_ID('Orders'),'PK_Orders','IsRowLockDisallowed')
WHEN 1 THEN 'Cannot Row Lock '
WHEN 0 THEN 'Can Row Lock '
ELSE 'Unknown'
END 'RowLock'
GO
EXEC sp_indexoption @IndexNamePattern = 'Orders.CustomerID',
@OptionName = 'AllowRowLocks', @OptionValue = 'yes'
GO
SELECT 'Orders',
CASE
INDEXPROPERTY
(OBJECT_ID('Orders'),'CustomerID','IsRowLockDisallowed')
WHEN 1 THEN 'Cannot Row Lock '
WHEN 0 THEN 'Can Row Lock '
ELSE 'Unknown'
END 'RowLock',
CASE
INDEXPROPERTY
(OBJECT_ID('Orders'),'PK_Orders','IsRowLockDisallowed')
WHEN 1 THEN 'Cannot Row Lock '
WHEN 0 THEN 'Can Row Lock '
ELSE 'Unknown'
END 'RowLock'
GO