We have a table that users are getting blocked on.
The table is insert/delete only with no updates.
Historical data is deleted during a purge run. It is during this purge that the users get blocked. However they are not trying to access old data, as the users are inserting new records only, no updates or deletes.
We delete 500 rows at a time, up to about 1 million rows during a nightly job.
However as this system is 24/7 there will always be users on.
My question is why would users be blocked when they are inserting only, and the purge is deleting old data that is not being used at all.
Hi thanks for the replies.
Firstly it is row level locking already and it is a sorted(clustered) table.
The table has over 100 million rows in it with about 1 million inserts per day and up to 1 million deletes per day.
We have run the delete command manually and it uses indexes etc and is fine. However when we run it as a stored procedure it escalates to an Ex_table lock, even though we are only deleting in txns of 500 rows.
The stored procedure is a replicated stored procedure, but i'm not aware that this would have any effect like this?
sp_lock shows only EX_Row and EX_Page locks, however hold_locks shows the EX_Table as stated above.
Ok so now you know that ex_table lock is the problem.
You really should check your configuration values for the parameters "row lock promotion HWM" and "row lock promotion LWM". These decides when to switch to table locking. The default is 200 so if you delete 500 rows at one you really should increase these parameters.
See Performance and Tuning guide, section "setting lock promotion thresholds" for more info...