Thursday, December 22, 2005

DELETE WITH TABLOCKX - Follow up

After some playing around with DBCC PAGE, DBCC EXTENTINFO I came to the conclusion that it is indeed the deallocation that causes the process to take page locks (and extent locks when a complete extent is released).

From Inside SQL Server 2000

When the last row is deleted from a data page, the entire page is deallocated. (If the page is the only one remaining in the table, it isn't deallocated. A table always contains at least one page, even if it's empty.) This also results in the deletion of the row in the index page that pointed to the old data page. Index pages are deallocated if an index row is deleted (which, again, might occur as part of a delete/insert update strategy), leaving only one entry in the index page. That entry is moved to its neighboring page, and then the empty page is deallocated.

So I suppose the spid that does the deletes also deallocates the pages and extents and not the asynchronous process that scans for ghosted records

No comments: