Disable Index Page Locks

A script that generates statements to disable page locks on every index in the database.

Published Nov 14, 2007

Page locks can be a source of lock escalation and contention on very busy tables. This script generates the ALTER INDEX statement to turn ALLOW_PAGE_LOCKS off for every index on every user table - review and run what you need.

SELECT
	'ALTER INDEX [' + I.name + '] ON [' + SCHEMA_NAME(O.schema_id) + '].[' + O.name + '] SET (ALLOW_PAGE_LOCKS = OFF)'
FROM
	sys.objects as O
INNER JOIN sys.indexes AS I
	ON I.object_id = O.object_id
WHERE
	O.is_ms_shipped = 0
	AND O.type = 'U'