I have a table in a SQL Server database with a clustered index on the primary key. The table has 1 million rows. If I delete 10K rows from table, do index get restructured during deletion operation is performed ?
The deletion operation is part of stored procedure. At a time, more than one client can be executing the stored procedure, however each individual run will delete it's own set of rows (uniquely identified by primary key). I'm getting blocking on Key lock (of type U) when multiple clients executes the procedure. The blocker lock belongs to a row from the same table and it is not part of any of the concurrently running transaction. There should not be any blocking as each run is trying to delete it's own set of rows. Lock escalation is not happening as it is turned off.
I suspect, deletion operation must be causing index to re-balance and hence during restructuring process it can take key lock on any row of the table.
I would really appreciate any opinion on this.