What does the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS mean on the CREATE INDEX statement ?
What is the cost\benefit of ON|OFF?
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.**
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
SQL-Server–使用另一個Windows-Account操作SSMS-錯誤訊息--請輸入用於cprogram-filesql-server-management-studio-18common7idessmsexe" title="SQL Server–使用另一個Windows Account操作SSMS, 錯誤訊息 : 請輸入用於c:\program file\sql server management studio 18\common7\ide\ssms.exe