ALLOW PAGE LOCKSquestion... We have a number of large "raw"-data tables from which a part of data is deleted to be replaced with the new data (during incoming data files processing). There is no transactional activity on these tables; just "bulk"-delete and than a bulk insert. However, a simple statement like
DELETE * FROM Table1 WHERE Date >= '1-1-2011'(being executed from SSIS package) puts the whole server on hold! In the Activity Monitor I see that the statement is Suspended (which lasts for hours), and in the "Locks by Object" tab I see thousands, literally, exclusive
PAGElocks associated with this process. Why is this so, and what would be the solution? I rebuilt indexes on one table
WITH ALLOW PAGE LOCKS OFFoption, and the
DELETEstatement passed quickly. I'm not sure though that this is a best-practice solution. To start with, why my
DELETEstatement causes me all these problems?..