February 23, 2009 at 4:38 am
Hi All,
I want to know the cost of the below locks.
1)ROW
2)PAGE
3)TABLE
i mean which one will take more memory space?
How to know it manually?
karthik
February 24, 2009 at 5:56 am
Each lock uses exactly the same amount of memory. I think Books Online has the details of how big this is.
If you have 20 rows in a table locked, then you have 20 locks. If you just have the table locked, then the row locks are not needed and you have just one lock. However, if you have 20 rows locked other people can read the remaining 999980 rows in the table. If you have the table locked no-one else can read any of the table.
It is best practice to let SQL Server manage locking for you. The only lock hint that may be useful to use as a standard on relevant queries is NOLOCK.
You should not use the other lock hints unless you have a problem that you know is caused by excessive locking and that using lock hints is the best way to resolve the problem. It is unusual to have a problem that is caused by excessive locking, and you need to be certain that locking is the root cause of the problem and not just a side-effect. For most situations where you have excessive locking, rewriting the query to avoid the need for some of the locks is normally the best solution.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 24, 2009 at 8:37 am
http://msdn.microsoft.com/en-us/library/ms190615.aspx
don't see the size given, but each one is the same in the db, just a different value in the rows of system views.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply