讀書筆記：一個非叢集索引查詢引起的“表掃描” + “阻塞”問題

標籤：

以下是使用AdventureWorks2008R2資料庫測試一個因全表掃描而引起的阻塞問題。

步驟：

一、建表

CREATE TABLE Employee_Demo_Heap
(
 [BusinessEntityID] [int] NOT NULL,
 [NationalIDNumber] [nvarchar](15) NOT NULL,
 [LoginID] [nvarchar](256) NOT NULL,
 [OrganizationNode] [hierarchyid] NULL,
 [OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
 [JobTitle] [nvarchar](50) NOT NULL,
 [BirthDate] [date] NOT NULL,
 [MaritalStatus] [nchar](1) NOT NULL,
 [Gender] [nchar](1) NOT NULL,
 [HireDate] [date] NOT NULL,
 [SalariedFlag] [dbo].[Flag] NOT NULL,
 [VacationHours] [smallint] NOT NULL,
 [SickLeaveHours] [smallint] NOT NULL,
 [CurrentFlag] [dbo].[Flag] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT PK_Employee_BusinessEntityID_Demo_Heap PRIMARY KEY NONCLUSTERED
 (
  BusinessEntityID ASC
 )

)

GO

CREATE NONCLUSTERED INDEX IX_Employee_NationalIDNumber_Demo_Heap ON Employee_Demo_Heap
(
 NationalIDNumber ASC
)

go

CREATE NONCLUSTERED INDEX IX_Employee_ModifiedDate_Demo_Heap ON Employee_Demo_Heap
(
 ModifiedDate ASC
 )

go

insert into Employee_Demo_Heap
(
 BusinessEntityID,
 NationalIDNumber,
 LoginID,
 OrganizationNode,
 JobTitle,
 BirthDate,
 MaritalStatus,
 Gender,
 HireDate,
 SalariedFlag,
 VacationHours,
 SickLeaveHours,
 CurrentFlag,
 ModifiedDate
)
select BusinessEntityID,
 NationalIDNumber,
 LoginID,
 OrganizationNode,
 JobTitle,
 BirthDate,
 MaritalStatus,
 Gender,
 HireDate,
 SalariedFlag,
 VacationHours,
 SickLeaveHours,
 CurrentFlag,
 ModifiedDate
from HumanResources.Employee

go

CREATE NONCLUSTERED INDEX IX_Employee_JobTitle_Demo_Heap ON Employee_Demo_Heap
(
 　　[JobTitle] ASC
 )

go

 

二、建立串連A中執行以下update語句

begin tran a

　　update Employee_Demo_Heap set jobtitle = ‘Changed1‘  where BusinessEntityID = 70

 

三、建立串連B中執行以下select語句

　　select BusinessEntityID, loginID, jobtitle from Employee_Demo_Heap where BusinessEntityID in ( 3, 4, 100)

 

發現串連B中的語句已被阻塞住了。查詢 select * from sys.dm_tran_locks  發現

對於 RID  1:23128:19     一個串連申請了 X 鎖，一個串連申請了 S 鎖，S 鎖的等待狀態是WAIT。

也就是說串連B也對BusinessEntityID = 70這條記錄去申請S 鎖，但是串連A事務未提交，X 鎖未釋放。導致被阻塞住了。

但問題是串連B中的select語句並未查詢 BusinessEntityID = 70 的記錄，為什麼還會對 BusinessEntityID = 70 的記錄申請 S 鎖 呢？

通過對執行計畫的分析，我們發現，select BusinessEntityID, loginID, jobtitle from Employee_Demo_Heap where BusinessEntityID in ( 3, 4, 100)  語句採用了 Table Scan ，這就是為什麼也會查詢 BusinessEntityID = 70 記錄的原因了，這也導致了串連B也對BusinessEntityID = 70這條記錄去申請S 鎖。從而導致了串連B中的語句被阻塞住了。

如果我們把Employee_Demo_Heap的主鍵 PK_Employee_BusinessEntityID_Demo_Heap  換成叢集索引，再執行串連B中的語句，我們會發現並沒有被阻塞。

 

由此，我們可以得到：

1. 叢集索引容易使用 Clustered Index Seek 從而減少阻塞的機率。
2. 非叢集索引容易使SQL Server認為非叢集索引+Bookmark Lookup並不比全表掃描快，而採用全表掃描，從而增加阻塞發生的機率。

可見用叢集索引的查詢還是比用非叢集索引要可靠的多。能用到叢集索引的地方，我們還是盡量使用叢集索引吧。

讀書筆記：一個非叢集索引查詢引起的“表掃描” + “阻塞”問題