question

Tatyana avatar image
Tatyana asked

ALLOW_PAGE_LOCKS OFF against extensive Page Locks?

Hi; here is another ALLOW PAGE LOCKS question... 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 PAGE locks associated with this process. Why is this so, and what would be the solution? I rebuilt indexes on one table WITH ALLOW PAGE LOCKS OFF option, and the DELETE statement passed quickly. I'm not sure though that this is a best-practice solution. To start with, why my DELETE statement causes me all these problems?..
sqlindexinglocking
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
SirSQL avatar image
SirSQL answered
It's interesting that the locks are not escalating. Is there an extremely high number of rows in the table? Is the column in your where clause indexed? Are your statistics up to date? As regards the delete itself, how about specifying a WITH (TABLOCK) to prevent attempts to lock pages?
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image SirSQL commented ·
I wouldn't delete the WA% statistics, the ones you mentioned are auto-generated by SQL because it's been decided that they are useful for certain queries. Do you have a lot of indexes on the table? That could also be causing you problems.
1 Like 1 ·
Tatyana avatar image Tatyana commented ·
Thank you for your suggestions! They had already me looking into the places I didn't think about, like statistics (I probably should have). The number of rows in those tables is from 3 to 5,000,000. An average number of rows deleted during a single incoming data processing is 5,000. Yes, the column in the WHERE clause has a non-clustered index on it. Speaking of statistics... they are well up to date, but it seems that there are too many of them! The tables have a “WA-Sys” statistics per each column, and since the tables are extremely wide there are hundreds of statistics (plus 5-10 "dta-index" statistics on indexed columns). Maybe this is a reason for extensive locking? Should I drop those "WA-sys" statistics? And I'll follow your advice and will experiment with WITH(TABLOCK) hint, as well as with trying smaller batches (Håkan Winther's advice below), and hopefully will be back with results. Thank you!
0 Likes 0 ·
Tatyana avatar image Tatyana commented ·
I added WITH (TABLOCK) hint to the DELETE statement, and disabled all the indexes except the two used in the WHERE clause of the DELETE statement (and rebuilt them with ALLOW PAGE LOCK put to OFF)... It still falls into that "suspended" state with tons of exclusive locks on the pages, and the Activity Monitor points out to that DELETE statement. Hm... where else I can looks for a reason? It does not happen EVERY time though; but when it happens there is no other way to get through except stopping the SSIS package and restarting it.
0 Likes 0 ·
SirSQL avatar image SirSQL commented ·
I don't suppose there is a trigger on that table anywhere is there?
0 Likes 0 ·
Tatyana avatar image Tatyana commented ·
No, no triggers... Maybe I should try setting auto update statistics to OFF? Because of this problem being on and off it's hard to tell if the latest change has caused the expected effect and if it'd last. But, in general, what are other things in the database that could result in creating a massive page-lock? Can I be sure that locked pages belong to the table where the records are being deleted from? Could it be that some system table is involved? Maybe watching this process from the Profiler could bring more information, too?..
0 Likes 0 ·
Show more comments
Håkan Winther avatar image
Håkan Winther answered
If you are going to delete many records, I recommend you to do it in smaller batches to reduce the problem with blocking.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tatyana avatar image Tatyana commented ·
Would you suggest to do something like this (I found it on the net)? WHILE 1 = 1 BEGIN DELETE TOP(2000) FROM Table1 WHERE Date <= '1-1-2011' IF @@ROWCOUNT < 2000 BREAK; END Or, since there will be only about 5000 rows deleted, to have a batch even smaller?
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
I would use something similar but only if we are talking about millions of records. 5000 records should be just fine in one batch
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.