USE [DB] GO CREATE NONCLUSTERED INDEX [ui_1_temp_nc] ON [dbo].[myTable] ( [col1] ASC, [col2] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
1) SQL Server takes locks at different levels – such as table, extent, page, row. ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS decide on whether ROW or PAGE locks are taken.
2) If ALLOW_PAGE_LOCKS = OFF, the lock manager will not take page locks on that index. The manager will only user row or table locks
3) If ALLOW_ROW_LOCKS = OFF , the lock manager will not take row locks on that index. The manager will only use page or table locks.
4) If ALLOW_PAGE_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF , locks are assigned at a table level only
5) If ALLOW_PAGE_LOCKS = ON and ALLOW_PAGE_LOCKS = ON , SQL decides on which lock level to create according to the amount of rows and memory available.
6) Consider these factors , when deciding to change the settings. There has to be an extremely good reason , backed up by some solid testing before you can justify changing to OFF