Reactive Index Design - Relational Database Index Design and the Optimizers - page 120

is to determine which tables are accessed by the module suffering from long
lock waits; assuming index pages or rows are not locked by the DBMS, the hot
resource must be a page or a row in one of these tables—probably the most
popular row in a small table or the last page in a large table when new rows are
inserted at the end of the table.
Basic queuing theory is useful when analyzing lock wait problems.

Q = u/( 1 − u)S

where Q = average queuing time
u = utilization (resource busy)
S = average service time (resource busy per request)

This simple formula assumes a single server and a Poisson distribution for
the interarrival times and the service times.
In database locking, the time an object is “busy” (locked) is the product of
the lock request arrival rate and the average lock duration:

2 requests per second × 0 . 2 s per request = 0 . 4

u

) should
be no more than 0.1; this means that the object is locked for 10% of the time
during peak hour. The average queuing time would then be u / ( 1 − u) = 11% of
the resource locked time. Because of random variation, some individual queuing
times will be much longer than the average queuing times, and these sorts of
figures would obviously be likely to cause visible problems. When an object
is locked for say, 50% of the time, the formula shows that the average lock
wait time would become equal to the lock duration; some lock waits would be
very much higher than the average; at this level the problems are likely to be
very serious.
Index improvements reduce lock durations (

Our experience shows that the alarm limit for resource locked (

S ) and therefore also lock waits

( ). This is why we made the recommendation to start the tuning process by

analyzing the promising culprits; lock victims should probably be the next step.
Locking problems are often relatively easy to resolve once the bottlenecks have
been found.
Other victims, such as transactions with long CPU or disk drive queuing
times, are normally difficult to resolve. Long queuing implies high utilization. The
only solution may be a hardware upgrade, assuming the significant culprits have
already been tuned. Transactions that have to wait for events such as opening or
expanding a file may also be classified as victims. Delays like these are normally
system tuning problems; they may be unavoidable for infrequently used files or
on Monday mornings if the system has been restarted (and the files closed) during
the weekend.
These unusual waits are recorded in Other Waits time (which also includes
CPU queuing). More detailed exception reports might identify the type of event
or wait. For this reason it is wise to save the detailed reports for a week or two.

Q