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 ServerSQL Server Analytics Service 1
SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)
Max Chen (159)