January 12, 2011 at 8:41 pm
I have a set of update statements with explicit row lock mentioned. On investigating I see that this locks are being escalated to table locks.
How do I optimize these update statements for optimal performance. The developer insists that row locks are needed for business purpose. Any help on this would be appreciated.
January 13, 2011 at 2:58 am
I am not that deep into the sql server lock system but as far as I know every update is running in an implicit transaction that locks the specific rows during the update. And if you create an explicit transaction you can make sure that you are consistent over a few tables while updating till the transaction is comitted or rolled back. So from my point of view a tablehint is not necessary.
Maybe if you want to ensure consistency you can add a tablehint to specific selects inside the transaction but that depends on the businessneeds and the workflow.
January 13, 2011 at 3:47 am
Rowlock will help till some extent (till threshold reached)....
After the threshold number of locks gets acquired the rowlock gets escalated to table level lock to optimally use the resources.....
As per my understanding and experiments you can not override the above phenomenon of lock escalation.
Moreover one should not even try to change the lock escalation, as each lock requires the memory space and locks going beyond some extent may really hamper the performance…an adverse impact
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
January 14, 2011 at 8:22 am
IIRC there is a trace flag to change the locking escalation behavior. But you best be VERY careful if you decide to implement it. And it still doesn't completely avoid escalation - it just changes the internal mathematics. It might even start throwing errors if it hits certain thresholds.
Best is to figure out if you can build a better mousetrap to avoid escalation in the first place, such as batching the updates into smaller chunks which keep you below the normal escalation threshold.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 14, 2011 at 9:39 am
if on SQL 2008 issue
ALTER TABLE table SET (LOCK_ESCALATION = DISABLE)
to disable lock escalation on that table, after that sql server will respect locking hints
--
Thiago Dantas
@DantHimself
January 14, 2011 at 10:09 am
dant12 (1/14/2011)
if on SQL 2008 issueALTER TABLE table SET (LOCK_ESCALATION = DISABLE)
to disable lock escalation on that table, after that sql server will respect locking hints
Incorrect. Per BOL:
DISABLE
Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 14, 2011 at 10:14 am
TheSQLGuru (1/14/2011)
dant12 (1/14/2011)
if on SQL 2008 issueALTER TABLE table SET (LOCK_ESCALATION = DISABLE)
to disable lock escalation on that table, after that sql server will respect locking hints
Incorrect. Per BOL:
DISABLE
Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.
living and learning, thanks!
--
Thiago Dantas
@DantHimself
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply