Reading notes: A "table scan" + "blocking" issue caused by a nonclustered index query

The following is a ADVENTUREWORKS2008R2 database test for a blocking problem caused by a full table scan.

Steps:

I. Establishment of the table

CREATE TABLE Employee_demo_heap
(
[BusinessEntityID] [INT] Not NULL,
[Nationalidnumber] [nvarchar] () not NULL,
[LoginID] [nvarchar] () not NULL,
[Organizationnode] [HierarchyID] Null
[Organizationlevel] As ([organizationnode].[ Getlevel] ()),
[JobTitle] [nvarchar] () 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

Second, execute the following UPDATE statement in new connection a

BEGIN Tran A

Update employee_demo_heap Set jobtitle = ' Changed1 ' WHERE BusinessEntityID = 70

Third, the following SELECT statement is executed in new connection B

Select BusinessEntityID, LoginID, jobtitle from Employee_demo_heap where BusinessEntityID in (3, 4, 100)

The statement in connection B was found to be blocked. Query select * FROM sys.dm_tran_locks discovery

For RID 1:23128:19 A connection has requested an X lock, a connection has requested S lock, and the wait state of S lock is wait.

That is to say connection B also to BusinessEntityID = 70 This record to apply S lock, but the connection a transaction is not committed, X lock is not released. caused by the blockage.

But the problem is that the SELECT statement in connection B does not query the record for BusinessEntityID = 70, why is the record for BusinessEntityID = 70 an S lock?

Through the analysis of the execution plan, we found that select BusinessEntityID, LoginID, jobtitle from Employee_demo_heap where BusinessEntityID in (3, 4, 100) The statement uses the Table Scan, which is why the BusinessEntityID = 70 record is also queried, which also causes the connection B to BusinessEntityID = 70 to apply for the S lock. This causes the statement in connection B to be blocked.

If we change the Employee_demo_heap primary key pk_employee_businessentityid_demo_heap to a clustered index and then execute the statement in connection B, we will find that it is not blocked.

Thus, we can get:

1. Clustered indexes are easy to use Clustered index Seek thereby reducing the probability of blocking.
2. Nonclustered indexes make it easier for SQL Server to think that nonclustered indexes +bookmark lookup is not faster than full-table scans, but full-table scans, which increase the probability of a blocking occurrence.

It is more reliable to see a query with a clustered index than to use a nonclustered index. Where we can use the clustered index, we should try using a clustered index.

Reading notes: A "table scan" + "blocking" issue caused by a nonclustered index query