Avoid table locking and improve DML operations faster in Azure Synapse table

Question

We are facing issue. In most of the time DML operation, specifically delete operation completely struck and procedure gets hanged for ever.

The table has more than 18 million records. And will be increased in future.

The distribution policy is ROUND-ROBIN. Could you please suggest the best way to improve the performance in DML operation. Is it advisable to create clustered index on column which are used in delete where clause? Please advise.

Answer 1

Create an INDEX on the column used for searching rows to delete in the table. Without an index, SQL Server acquires a larger number of locks during the search for rows to delete.

Try using the following deletion logic:

declare @RowCount int
set @RowCount = 1
while @RowCount > 0
begin
    delete top (1000) MyTable from MyTable with (readpast) where MyColumn = SomeCriteria
    set @RowCount = @@ROWCOUNT
end

Additionally, using a CLUSTERED INDEX on the column(s) in the delete WHERE clause can improve performance, allowing SQL Server to quickly locate rows for deletion.

References:

SO link for Delete statements locks table.

Learn more about optimized locking enabled on Azure SQL Databases and Delete operation.