Database Reference
In-Depth Information
Figure 7.1
Memory trends and the stack.
At the server/physical host level and above, the CPU interacts with the array of memory
associated with the CPU. Vendors started creating a separate array of memory
associated with each CPU socket to prevent the performance hit associated with several
processors attempting to access the same memory at the same time. A CPU is able to
access its own local memory associated with the socket faster than nonlocal memory.
Nonlocal memory is memory local to another processor or shared between processors.
NUMA is another example of the trend of introducing memory into the stack to speed
performance. NUMA stands for non-uniform memory access. We will discuss non-
uniform memory access in great detail in the section of this chapter titled Non-Uniform
Memory Access (Numa).
Database Buffer Pool and Database Pages
At the server level, the SQL Server engine deals with the database cache, more
commonly called the database buffer pool. The SQL Server engine uses a Most
Recently Used (MRU) algorithm to determine which database pages to leave in memory
and which database pages to swap back out of disk if there is not enough room in the
database buffer pool to hold all the data in memory. By using this algorithm, it attempts
to hold the data in memory that you are most likely to request next. Remember the
important point we made earlier in the chapter: When a SQL Server database does not
have enough memory, the database will move more of its workload to physical I/O.
An important point to make here is that the only way the SQL Server can access data is
if it's residing within the database buffer pool. When a request is made within SQL
Server for data, it first looks within the database buffer pool. If the database cannot find
what it needs within the database buffer pool, the SQL Server engine then calls out to
the storage to go retrieve it. The data is retrieved and then placed within the database
buffer pool, at which point the database is able to access it.
Important
SQL Server can only access data if it's first residing in the database buffer pool.
Only data that resides in the database buffer pool can be manipulated, inspected,
or altered by SQL Server.
Only data that resides in the database buffer pool can be manipulated, inspected, or
altered by SQL Server. Until it resides within the database buffer pool, it is not usable
by the database. Without memory, the SQL Server engine cannot do its work. As a
DBA, you control the size of the database buffer pool. Too small a buffer pool and the
database will constantly be calling outside to the storage array. Too large a pool and
you could take away valuable memory needed elsewhere. Remember a virtualized
