While running SQL Server Profiler I reached some Lock:Escalations. When I searched for Statements having same SPID as Lock:Escalation event I realized that one of delete statements causes this.
Is there any way to find out why lock escalation in such place occurres?
Statement is like:
delete from BOOK_IN_LIBRARY where libraryId in (,,,,); <-20 elements ids
CREATE TABLE BOOK_IN_LIBRARY(
[libraryId] [bigint] NOT NULL,
[bookId] [bigint] NULL,
[otherData] [bigint]NULL,
[otherData2] [int] NULL,
[otherData3] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[libraryId] ASC,
[bookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Deleting 20 of these id's means deleting up to 68000 rows. The statement exceeds the threshold of row locks after which database create lock escalation. More info Lock Escalation (Database Engine). The answer thanks to JamesZ comment. Thank you!