quarta-feira, 9 de janeiro de 2008

lock escalation problems

I was reading something about lock escalation on kb. And one thing that called my attention is a simple way to break a large and complex query that deletes hundreds of records in a much smarter one... look at this

from http://support.microsoft.com/kb/323630/en-us

Break up large batch operations into several smaller operations. For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users:

DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. For example:
SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

end of microsoft kb

ps. That's the shit! 

0 comentários: