Databases Reference
In-Depth Information
relatively good. If you have people over, however, the chance of i nding the chair occupied increases
quickly. If there's a party going on, the chair might be occupied quite a lot. Even if people tend to sit
down for just a few moments and then get up again, with enough people interested in sitting down
now and then, the chance of the chair being occupied increases; and if that chair happens to be
particularly popular (maybe it's a lot more comfortable than the other chairs), then you might have
a great deal of chair-use contention.
In terms of latches and spinlocks, recall that a process uses a piece of memory and then releases it.
Contention occurs when a process tries to use a piece of memory and i nds that another process has
already acquired a latch on it. If SQL Server were using only one processor core, there shouldn't be a
problem (you can sit where you like if you're the only person at home); but that's yesterday's server,
not today's. Increasingly, we're seeing systems with a number of cores that seem ridiculous; and
much like we used to talk about memory in megabytes rather than gigabytes, the numbers available
now will seem tiny in the future. It is already common to see six- or eight-core processors. Even my
laptop, a few years old, is a quad-core machine, raised to eight through hyperthreading.
Furthermore, there are systems that encourage the use of a particular piece of memory. For example,
when a lot of processes are trying to push data into a table, and that table always inserts data into
the same page, latch contention could result. Database administrators don't always know exactly
what the applications that use their databases are trying to do, but they often have a good idea
of which tables meet these conditions, and therefore whether they might be candidates for latch
contention.
I imagine by now you're convinced that latch contention is a very real possibility for your systems,
and that if you're not suffering from it yet, it's only a matter of time. I think it's a good time to
introduce what latches and spinlocks are, and take a deeper look at them.
UNDERSTANDING LATCHES AND SPINLOCKS
To understand latches and spinlocks, you will need to consider their actual dei nitions, and consider
why they are required in SQL Server.
Defi nitions
You might recall from the discussion about locks that they are vital to the protection of data. In
fact, it was suggested that without locks, there is no guarantee of data integrity, and all would be
chaos. Latches perform the same function, but at another level. While locks protect our data from a
logical perspective, ensuring that no one accesses the same table (or whatever) during someone else's
transaction, latches do this for memory.
Books Online describes latches as “lightweight synchronization primitives that are used by the
SQL Server engine to guarantee consistency of in-memory structures.” In other words, despite the
fact that you might think of your data as living in tables and indexes, these tables and indexes must
be implemented in memory in order to enable the database engine to work its magic. To be used at
all, data needs to be loaded off disk into RAM, making it available when needed. Latches protect
this process of loading the data, and protect the data that has been already loaded. Similar to locks,
latches are acquired when required, and have modes with various levels of compatibility. You'll learn
Search WWH ::
Custom Search