Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 231

Finally, note that SQL Server attempts lock escalation after acquiring 5000 locks on the rowset. It also
attempts lock escalation under memory pressure.
Dynamic
SQL Server manages locking in a dynamic manner. This means that the engine determines what granular-
ity and mode are appropriate when the query is executed. By allowing SQL Server to manage the locking
mechanism autonomously this shifts the responsibility from the database administrator or developer.
Blocks
As stated earlier, an existing lock can block an attempt to lock the same resource. This is a normal and
usually temporary condition. The existing lock will be released and the new lock will be granted. You
can surmise from the previous discussion of locking that blocking is a fact of life within the operation of
SQL Server. However, blocks can sometimes conflict with each other. This leads to a condition known as
deadlock.
Deadlocks
One of the most common situations for deadlocks is when transaction A is holding a lock on a resource
that transaction B wants to change. Additionally, transaction B has a lock on a resource that transaction A
wants to modify. If left unattended this condition will live on indefinitely. However, SQL Server monitors
for deadlocks and will manage them. When the database engine detects a deadlock it will choose one of
the transactions as a deadlock victim. The engine will then roll back the changes made by that transaction.
When this occurs the only option is to re-execute the original statement.
Deadlocks are certainly something you want to minimize. We'll look at some guidelines for that in the
next chapter, on SQL tuning.
InPractice
At this point you may be wondering how all this talk of concurrency and locking leads to relieving bot-
tlenecks. First, it's important that you have a decent understanding of these areas. Ensuring that you're
using the appropriate concurrency controls can't be done unless you understand what your options are.
Using the proper isolation level is a key to achieving this goal. Second, as far as locking is concerned,
there's little that can be done to directly control the lock manager. SQL Server manages locking automat-
ically. However, you should have a good appreciation for the amount of effort that SQL Server has to
exert when managing locks.
Both these areas lead to what should be clearly an underlying theme with this chapter. Designing tables
that provide the highest row density as practical is paramount. The benefits you'll reap for this are numer-
ous. Conversely, by not doing this the results can be severe.
In fact, let me tell you a story that illustrates this principle. Several years ago I inherited a data warehouse
database that had been hastily designed and implemented. When it was first implemented it performed
well enough. However, after about 15 months, the database had become problematic. Because the tables
didn't have very many rows per page, the DB quickly grew to about 90 GB. In addition to that, the nightly
load on this database took about 6 hours. The lack of density caused the database size to grow rapidly.
Furthermore, this also led to overburdening the lock manager. Since the density was so low, the data
pages and row counts were almost one to one. I vividly remember watching the statistics of one of the
table loads. This particular load included about 100,000 rows, and the lock manager generated about