How to prevent lock escalation

There are several ways to reduce the lock footprint og expensive queries so that the lock escalation thresholds are not exceeded.

Beneath I have described how I do it most of the times. Other ways can be read in this article: How to resolve blocking problems that are caused by lock escalation in SQL Server


SET ROWCOUNT 500
delete_more:
DELETE FROM BATCHHISTORY WHERE CREATEDDATETIME < '2015-11-02' OPTION(MAXDOP 1)

IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
GO

Dette indlæg blev udgivet i Dynamics Ax, SQL Server - scripts. Bogmærk permalinket.