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.
The Bottom Line: Hot SpotsWherever order exists, if it doesn't correspond to some user need, add a pinch of chaos. |