asked
Extensive and persisent PAGE locks during DELETE
We process data into several large tables on a daily basis… The process includes deleting the records with overlapping dates in the targeted tables. Lately the problem became extremely visible and deteriorating: during an execution of a simple delete statement: `DELETE FROM Table1 WHERE Date >= @Date` the whole server halts, and the statement executes for, sometimes, hours, while deleting no more than 5,000- 50,000 rows. There is an index on the column used in the WHERE clause; there are 3-5 other indexes reorganized up-to-date; all the statistics are up to date; no triggers. It does not help if I add a TABLOCK hint to the DELETE statement. Did not help that I rebuilt indexes with the ALLOW PAGE LOCK option set to OFF. Every time I see, in the Activity Monitor, that the statement is suspended with the PAGEIOLATCH_EX wait type, and on the “Locks by Object” tab I see thousands of exclusive locks of a PAGE type. The situation becomes unacceptable, and I don’t know where to look for a source of this problem… Please point me out to some direction!
Just out of interest... you say that there's an index on the Date field - is that used as a foreign key as well?
Have you tried executing something like `DELETE top 500 FROM Table1 WHERE Date >= @Date` and seeing how the query performs? I would then increase or decrease the 500 according to your experience. When you have it performing in an acceptable manner then you could put this query in a section of code to keep executing until there are no more qualifying rows. There are bigger solutions to this problem such as table partitioning but that seems extreme for the numbers of rows that you mention. Be sure to have you log file backed up regularly while the delete is taking place if you are in FULL RECOVERY.
