Locks and Performance
-
Hi Experts,
I have read an article about 'Locks and Performance' couple of days back.
I came to know when we tune Page level lock to DataPage level and DataPage level lock to RowLevel Lock performance will improve. is it true ?
Can any experts give me proper real time example and explanation ?
Regards
Karthik
karthik
-
Hi Karthik,
I would suggest you to read SQL SErver 2000 for Expereinced Users by Rober Vierra.
This is very Good Book.
Regards,
Ezhilan Ekambaram
-
Read up on Locking Hints in BOL. Yes, you can help with performance by using locking hints to tell SQL Server to use a specific type of lock. Using locking hints, imho, should be used as an exception and not the rule. SQL Server does not have to obey your hint anyhow. If you specify a Row lock and SQL Server determines it needs to lock the extent or table, SQL Server will still escalate the lock to whichever level it choses.
-
Finer resolution on locks will sometimes decrease performance... nothing faster than a full table lock because it is only one lock. Of course, you might not want to do that if you're working with just one row
And, as John suggested, specifying locking resolution hints doesn't always work as advertised. Better to worry about writing good, fast code than just hinting about it 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply