SQL Server-- ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS

Posted by: bart30508 | in SQL Server | 11 months ago |

SQL Server-- ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS

Question:

What does the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS mean on the CREATE INDEX statement ?

What is the cost\benefit of ON|OFF?

Answer:

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

參考來源:

https://www.sqlserver-dba.com/2012/06/how-to-decide-on-index-allow_row_locks-and-allow_page_locks.html

tags: SQL Server
Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

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)

Authors

bart30508 (146)

Feeds

RSS / Atom

SQL Server-- ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.