Database Reference
In-Depth Information
environment is a shared resource pool of resources. How efficiently you use memory as
a resource is critical to overall database performance and the overall health of the
virtualized environment.
The fundamental unit of storage in a SQL Server database is the page. All data within
the database buffer pool is stored within the many pages that make up the database
buffer pool. In SQL Server, a database page is 8KB in size, and 8KB pages are
optimized for the Windows operating system and are not adjustable. Each time a SQL
Server page is touched, a counter within the page in incremented. The MRU algorithm
then takes the hottest pages, those with the highest count, and tries to keep them current
in the database buffer pool.
Paging and Swapping: A DBA's Nightmare
Quick question:
Paging
and
swapping
are common terms used by database
administrators and system administrators. So what is the difference between paging and
swapping?
Both paging and swapping are methods of moving the contents of data in memory to
another storage device. That storage device is typically a disk drive, and the contents
are placed within what is commonly called a swap file or page file. For example, in
VMware vSphere, the file is called a vSwap file.
Swapping is when you move all the memory segments belonging to a particular process
that's running onto another storage device. The important word here is
all
. When this
happens, all execution on that process stops, until enough space exists for all the
memory segments owned by that process to be brought back into memory. Remember,
it's an all-or-nothing proposition.
Paging is when a subset of the memory segment IE: individual pages are able to be
swapped in and out as needed. In this case, the SQL Server database would look within
the page table. If the page needed is already in memory, SQL Server accesses the
contents of that page. If the page needed by the process is not in memory, you get a page
fault. Processing is temporarily suspended until the operating system is able to bring the
needed page into memory. The key here is that this is
not
an all-or-nothing proposition.
The coming in and out from the secondary storage device is done at a more granular
level. In this example, the paging in and out is at the individual page level.
Caution
When paging or swapping occurs, the performance of your virtualized database is
severely impacted. This should be avoided at all cost.
Continuing further down the stack, when the data the SQL server database engine needs
