DELETE WITH TABLOCKX

Recently I was playing around with one of our procedures that we use for our reporting solution. One of the requirements was the ability to regenerate data for a certain date. In order to guarantee this we delete the records for a specific day before filling the denormalized tables with data. Because there are a lot of records for 1 day for some reports and we only fill them during the night when there is no activity I thought it would be a good idea to use WITH TABLOCKX because the finer the granularity the more work SQL Server has with managing the locks.

I accidentally came across some strange behavior (imho) when checking the actual locks taken by the process. It seems SQL Server is switching to row and page locks and escalating to table locks at some point even though I hardcoded WITH (TABLOCKX) in the DELETE statement.

Here you can find a little reproduction script:

The funny thing is that the switch to page and row locks is only happening as of a certain number of records (possibly because of the time it takes so some process finishes?)

SET NOCOUNT ON

IF NOT OBJECT_ID('tbltablockTest') IS NULL
DROP TABLE tblTablockTest
GO

CREATE TABLE tblTablockTest
(ID int,
myDate smalldatetime,
myText varchar(1000)
)

GO

DECLARE @i int

SET @i = 1

WHILE @i < 1025
BEGIN
INSERT INTO tblTablockTest
VALUES (@i, getdate(), REPLICATE(CAST(@i as varchar), 1000 / LEN(@i)))

SET @i = @i + 1
END

GO

CREATE CLUSTERED INDEX ixID ON tblTablockTest (ID)
GO


DBCC TRACEON (3604, 1200) --1200 shows detailed information about locking
BEGIN TRAN

DELETE FROM tblTablockTest WITH (TABLOCKX)
WHERE ID BETWEEN 1 AND 15 -- increase the 15 if the behaviour is not showing

ROLLBACK
DBCC TRACEOFF (3604, 1200)


I haven't figured out why this is happening yet but I'm on a mission ;-) One of my guesses is that it has something to do with page deallocation but I haven't got a good explanation yet.