Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 314

LockingOnlyWhenNeeded
Again, this seems obvious, but you shouldn't allow your application to generate locks that aren't
necessary. Doing so forces the lock manager to work unnecessarily. Resources are used unnecessar-
ily. This could lead to blocking, which is totally unnecessary. Use an isolation level that would help this.
Also when possible, using NOLOCK, table hints can help as well.
In SQL Server 2005 Microsoft added some very powerful features concerning lock management. Those
are the READ_COMMITTED_SNAPSHOT database option and the SNAPSHOT isolation level. The
READ_COMMITTED_SNAPSHOT database option controls the behavior of the READ_COMMITTED
isolation level. When the option is off, which is the default, SQL Server will use shared locks. If the
option is on, SQL Server will use row versioning to maintain the data consistency of a statement. With
the SNAPSHOT isolation level, data that's read by any statement in a transaction will be consistent within
that transaction since the start of the transaction.
AvoidingLockEscalation
Recall that lock escalation occurs automatically, and it's characterized by SQL Server's process of
converting fine-grain locks to coarse-grain locks. Moving a lock from page level to table level is an
example of escalation. The coarser the lock the more likely blocking can occur. When SQL Server locks an
entire table, all users are blocked from the table until the table lock is released. Note that when SQL Server
escalates locks, it does so by promoting both row- and page-level locks to the table level. It does not
promote row-level locks to page level.
One way to avoid lock escalation is to break up a few large batch operations into more numerous smaller
batch operations. For example, suppose you needed to run an update statement that was going to affect
half the rows in a table. Instead of running one update statement, try running multiple statements over
smaller row sets.
Another option for controlling lock escalation is by using the table hints ROWLOCK or PAGLOCK. These
directives won't allow SQL Server to escalate locks on the tables for which the hints are given.
Yet another option involves using SQL Server's trace flags. Trace flags are used to control some server
characteristics. For disabling lock escalation, you can use trace flags 1211 or 1224. See BOL for
complete details.
ConsideringanOLAP/DSSSolution
Many times, significant reporting is done from existing OLTP systems. If this reporting results in too
much blocking you should consider creating a more report-friendly data design than OLTP. OLTP data
structures place a high importance on preserving what is written into a database. This means that a high
degree of normalization is usually present. Also, indexes are usually sparse. Transactions must flow into
these database structures as quickly and efficiently as possible. Unfortunately this means that reporting
on that data cannot be done as quickly as desired. Therefore, in those cases, creating another data store
that does support reporting is an option. These structures tend to be less normalized. They also contain a
high number of indexes. In short, these structures are much less likely to generate blocks.
Also, for those sites running SQL Server 2005 Enterprise edition, database snapshots are an option. A
database snapshot is a read-only, static view of a source database. The snapshot is consistent with the
source database at the time the snapshot was created.