SQL Server可以锁定各种资源从小(行与键锁)到中(页锁、区)到大(表、数据库)。假定大量的查询同时从同一表和相关索引请求数据,多个细粒度的锁有助于提高查询并发性。然而大量的锁会占用内存,降低SQL Server实例的整体性能。另一方面,粗
粒度的锁增加了内存资源的可用性,但同时也减少了查询的并发性。
可以创建一个当索引被查询时限制某些锁定类型的索引,可以指定是否允许页锁或行锁。
在通常情况下,应该让SQL Server自动决定何种锁定类型是最佳的。也有临时限定某些资源锁定类型的情况,比如说故障诊断或存在严重的性能问题时。
为CREATE INDEX和ALTER INDEX配置这些选项的语法如下:
WITH(ALLOW_ROW_LOCKS ={ON l DFF}
l ALLOW_PAGE_LOCKS={ON l OFF})
例如,禁用数据库引擎在索引上放置行或页锁的能力,强制它使用表锁:
USE AdventureWorks
GO
--禁用页锁,表锁和行锁仍可以使用
CREATE INDEX NI_EmployeePayHistory_Rate ON
HumanResources.EmployeePayHistory (Rate)
WITH (ALLOW_PAGE_LOCKS=OFF)
--禁用页锁和行锁,只可以使用表锁
ALTER INDEX NI_EmployeePayHistory_Rate ON
HumanResources.EmployeePayHistory
SET (ALLOW_PAGE_LOCKS=OFF,ALLOW_ROW_LOCKS=OFF)
--允许页锁和行锁
ALTER INDEX NI_EmployeePayHistory_Rate ON
HumanResources.EmployeePayHistory
SET (ALLOW_PAGE_LOCKS=ON,ALLOW_ROW_LOCKS=ON)
当有合理的理由这样做时才去删除锁定选项——例如可能拥有产生太多行锁的活动,它可以用光内存资源。你可能希望SQL Server用粗粒度的页锁或表锁来取代行锁。