sqlserver2005 何时使用页锁？

使用行所还是页锁是在数据库运行时由数据库临时决定，没有一定的定论。

As mentioned earlier, SQL Server determines at runtime whether to initially lock rows, pages, or the entire table. The locking of rows (or keys) is heavily favored. The type of locking chosen is based on the number of rows and pages to be scanned, the number of rows on a page, the isolation level in effect, the update activity going on, the number of users on the system needing memory for their own purposes, and so on.

检查使用页锁的原因：
1、the number of rows and pages to be scanned
2、the number of rows on a page,
3、the update activity going on
4、the isolation level in effect


控制锁粒度的选择：sp_indexoption

The stored procedure sp_indexoption lets you manually control the unit of locking within an index. It also lets you disallow page locks or row locks within an index. Because these options are available only for indexes, there is no way to control the locking within the data pages of a heap. (But remember that if a table has a clustered index, the data pages are part of the index and are affected by the sp_indexoption setting.) The index options are set for each table or index individually. Two options, AllowRowLocks and AllowPageLocks, are both set to TRUE initially for every table and index. If both of these options are set to FALSE for a table, only full table locks are allowed

行锁页锁对应用、数据库的影响：
行锁提高应用并发，但使用数据库内存、进程资源较多，适合数据库系统配置高的情况。
页锁节约数据库内存、进程资源，但减弱了应用并发能力，适合数据库服务器配置较低的情况。