How should you modify the stored procedure?

HOTSPOT

You are profiling a frequently used database table named UserEvents. The READ_COMMITED_SNAPSHOT database option is set to OFF.

In the trace results, you observe that lock escalation occurred for one stored procedure even though the number of locks in the database did not exceed memory or configuration thresholds. Events details are provided in the following table:

You need to modify the uspDeleteEvents stored procedure to avoid lock escalation.

How should you modify the stored procedure? To answer, select the appropriate Transact-SQL segments in the answer area.

View Answer

Answer:

Explanation:

Delete up to 4000 rows at a time. Keep doing it until all rows have been deleted. Note that @@ROWCOUNT returns the number of rows affected by the last statement.

References: https://msdn.microsoft.com/en-us/library/ms187316.aspx