TIP: Perform deletions with zero blocking on large tables

Rajasekhar Reddy Bolla || [email protected] || +91 9966246368 (WhatsApp) The WITH (ROWLOCK, READPAST) hint in the SQL DELETE statement is used to optimize concurrency and minimize locking contention. Here's what each hint does: 1. ROWLOCK: • Ensures that the DELETE operation acquires row-level locks instead of page-level or table-level locks. • Helps minimize locking contention, allowing other queries to access different rows in the table concurrently. • Useful in high-concurrency environments where multiple processes might be accessing or modifying different rows. 2. READPAST: • Instructs the DELETE statement to skip over rows that are currently locked by other transactions instead of waiting for them to be released. • Prevents blocking when multiple processes are trying to delete or modify different sets of rows. • Especially useful in high-throughput OLTP (Online Transaction Processing) systems where contention for rows is common. Why Use Them Together? • ROWLOCK ensures that only the necessary rows are locked, preventing excessive locking that could slow down performance. • READPAST helps avoid blocking issues by skipping locked rows, allowing the DELETE operation to proceed without waiting. • This combination is effective in scenarios where concurrent transactions frequently modify or delete rows in a table, reducing contention and improving performance. When Should You Be Cautious? • READPAST can cause some rows to be skipped during deletion, meaning those rows may remain in the table if they are locked by another transaction. • This might lead to inconsistent data if not handled properly, so it's important to understand whether missing some deletions is acceptable in your use case. Query: Below query helps is to purge the data DELETE TOP (@BatchSize) FROM dbo.AuditLog WITH (ROWLOCK, READPAST)