Hot Spots

"Nobody goes there anymore because it's too crowded."

Yogi Berra

When we say Moose Jaw is the hot spot to be this year, we're talking not about weather but about popularity. Similarly, a hot spot isn't exactly where the disk drive head is burning from friction. Rather, it's an object that many processes are trying to get at the same time.

Not all hot spots are hellish. If every transaction is READ UNCOMMITTED, or if traffic is light, then it's wonderful to find that a page is still in the cache, left over from the last user 's access. The hot spots we're talking about here, though, are the malignant kind: hot spots that cause exclusive locks and block other processes.

INSERT statements cause hot spots because the DBMS tries to put all new rows at the end of the table file-section (unless it's a clustered table). Because the table file has only one end, INSERT statements always try to get locks for the last page in the file. The DBMS can help here; for example, Microsoft can use row locks rather than page locks for INSERTs. Another palliative is to use clustered indexes (recall from Chapter 9, "Indexes," that, with clustered indexes, newly inserted rows can be anywhere in the file because they're put in order by the cluster key).

Index splits cause hot spots too, because DBMSs try to put new index pages together in the index file. The DBA can avoid such hot spots by setting some parameters to make the DBMS look first at "the end of the extent" rather than "the end of the file," although that technique wastes space.

Globals also cause hot spots. Typical examples are reporting applications that change a single row containing a "date of last report" field or bank withdrawals that immediately update the general ledger. Another example of a global is an interest rate: if interest rates change only once a day, can you read them in once to a program variable instead of looking them up in a table?

Another cause of hot spots is using sequences, or monotonics. If you use sequencingthat is, if you use SERIAL data types or IDENTITY columns , or auto-incrementing objectshere is a question for you: Given that the number following 12345678 is 12345679, what is the probability that two keys containing those values will end up on the same index page? If you answered nearly 100%, you're right! There are three easy fixes for sequence hot spots.

• Store the digits of the sequence in reverse; that is, store the numbers as 87654321 and 97654321. Because the difference is now at the start rather than at the end of the key, the keys won't sort near each other. Oracle supports this idea explicitly with its "reverse indexing" option. Of course, it's not such a good idea if you often ORDER BY sequence_column when you SELECT.

• Use a unique-key generator rather than a true sequence. For example, a stored procedure can form a Microsoft Global Unique Identifier (GUID) using the network connection information Node ID, the date, and the time of day to a high precision.

• Add the sequence number to some nearly unique value derived from the row itself, such as a surname . If someone objects that sequence numbers are useful because they can easily tell how many rows there are by looking at MAX(sequence_column) , we counter that rows can be deleted, transactions can be rolled back after a sequence number is generated, and UNION can cause an increment, and all these things muck up the nice monotonic sequence. And what's more, global sequence numbers are a cause of locking trouble in themselves , because the DBMS has to put a latch on the number while incrementing and retrieving it.

The Bottom Line: Hot Spots

Wherever order exists, if it doesn't correspond to some user need, add a pinch of chaos.