May 9, 2007 at 7:14 am
I restored a db (created in SQL 2000) into SQL 2005. How to know if the locks in db is page or rows locks?
Tks.
May 9, 2007 at 7:39 am
SQL Server will take row locks or page locks as required. Look for "lock escalation" in SQL Books Online. Essentially, SQL wil take row locks, but if you are locking many rows, it is more efficent to manage a small number of page locks rather than a large number of row locks.
Locks escalate from row->page->table.
You can override the locking using "with" hints on your query statements. Again, look in SQL Server books online for "locking hints".
Note: SQL Server Books Online is essentially the help file for MS SQL Server.
May 9, 2007 at 7:58 am
Ok ok, I know about this, but my question is: A db created in 2000 and restored into 2005 has the same locks behavior that a db created in 2005?
Thanks again.
May 13, 2007 at 9:05 pm
I haven't seen anything to the contrary. You wouldn't ask the question unless you had a suspicion that this was not the case. Are you having troubles with your restored DB?
May 14, 2007 at 8:41 am
No, I found nothing about this question in the Net, my customer ask me about performance, so...
Thanks a lot.
Best regards.
May 18, 2007 at 12:00 am
"Locks escalate from row->page->table"
this is not true in 2005 (not sure about 2000).
from BOL:
"The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks." - http://msdn2.microsoft.com/en-us/library/ms184286.aspx
also, locking behavior is a property of the database engine, and has nothing to do with whether or not the database started out life on a 2000 server. since you are running on a 2005 server you'll get 2005 behavior, not 2000.
---------------------------------------
elsasoft.org
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply