150 pts.
 OLTP in SQL Server 2008
While using OLTP in Micrsoft SQL 2008 enterprise edition, does the row level locking happens or table level locking happens?

Software/Hardware used:
ASKED: April 20, 2010  4:45 AM
UPDATED: April 21, 2010  6:00 PM

Answer Wiki:
Neither by default. By default SQL Server uses page level locking. Row and table level locking can be forced by using an index hint.
Last Wiki Answer Submitted:  April 20, 2010  9:12 am  by  Denny Cherry   64,505 pts.
All Answer Wiki Contributors:  Denny Cherry   64,505 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _


 

Does this forced locking affect performance. ?

 150 pts.

 

It could.

Locking at a smaller level could improve concurrency but adds additional overhead because more locks must be held if many rows are locked.

Changing the default locking level is something that needs to be carefully analyzed, but that doesn’t mean it will always have a noticeable negative impact on the system.

 63,535 pts.

 

A bigger factor in performance is the unnecessary use of locks.
Many queries in most systems could use the “with (nolock)” hint to avoid unnecessarily locking or blocking on existing locks.
You need to make sure you understand the implications, but if your queries are accessing relatively static data, or you don’t care if you see the results of a concurrent transaction immediately, using “with (nolock)” can dramatically reduce lock contention and increase system performance.
Also, ensure that all transactions are made as small as possible to minimize the time that locks are active.

A classic performance killer is to start a transaction, then do a query against a table using a non-indexed column. Until the transaction completes, the entire table will usually be locked, along with any other tables being used in the transaction.

Any queries within a transaction should only be on well-indexed columns, preferably only on primary keys or other unique indexes, to minimize lock escalations. And further, wherever possible, avoid any queries within a transaction.

 3,830 pts.