Memory Architecture


Sophisticated memory management architecture ensures that SQL Server 2005 gets all the memory it needs within limitations of the platform. Its memory management is conservative: the engine obtains memory as required and releases it as resources become free.

Two types of memory are used in the Windows Server 2003 operating systems: physical memory and virtual memory. The physical memory is the memory in the RAM chips installed on the system motherboards. Then you have virtual memory, which is a combination of all memory in the system and the architecture that makes it available to the OS. An important aspect of virtual memory is that it can include hard disk platter memory.

The advanced memory management facility on the Windows 2000 server platform (Address Windowing Extensions or AWE) enables SQL Server Enterprise Edition to work with a 32GB address space on 32-bit versions of the OS and SQL Server 2005, and up 64GB on 64-bit versions of the OS and SQL Server 2005. DBAs are still able to override the default and automated memory settings and specify fixed allocations (discussed in Chapter 4) should the application or environment require it. One area where this may be required is in the management of the memory pool, which is discussed shortly. The memory pool is where SQL Server stores its buffer cache of data pages.

On the Windows Server 2003 platform, 32-bit editions of both OS and SQL Server can access 2GB memory default, 3GB with the /3GB boot option parameter, and 4GB as a WOW (Windows on Windows) implementation (32-bit version of SQL Server 2005 running on a 64-bit OS). The 64-bit OS and SQL Server 2005 combinations provide access to 7TB (terabytes) on 164 architecture and 8TB on X64 architecture.

On most implementations SQL Server tries to maintain virtual memory on the server at between 4MB and 10MB less than physical memory And the only way SQL Server can do this is by changing the size of its memory pool. When SQL Server loads up collateral processes, such as calls to remote stored procedures and OLE DB providers, or when other applications or other instances of SQL Server start, it will have to reduce the size of the memory pool to release memory it has acquired. You can set limits on just how much the memory pool can vary by setting values for min server memory and max server memory, which are exposed in the Management Studio (see Chapter 4).

Any new applications that start up on the server are instead allocated paged memory. Each instance of SQL Server 2005 is thus able to work with the memory on the system, to the mutual benefit of the other instances. (When using AWE on Windows 2000 Server, however, the system will require the assignment of static memory addresses.)

Note 

Chapter 4 discusses memory management.

Page Files and the VMM

The virtual memory manager (VMM) manages all system memory and ensures that applications and the operating system have access to more memory than what is provided by the RAM chips installed in the modules on the motherboard. Memory chips are faster than virtual memory, a fact that is important for SQL Server.

The VMM has another important role: It guarantees that memory addresses used by multiple instances and applications do not “trespass” on each other. In the olden days of DOS, this problem created disasters. Memory management has evolved over the years, from the first versions of the Windows operating system to Windows Server 2003 Datacenter, which is one of the most sophisticated operating systems in existence. With each new version of the operating system, the virtual memory management has been improved, so the algorithms used by one Windows platform differ from the algorithms used in others. The memory management of SQL Server has also been vastly improved on Windows Server 2003. This means that for SQL Server 2005 to support instances on all 32-bit and 64-bit operating systems, the DMBS must use different caching algorithms.

The VMM has two major functions:

  • The VMM manages a memory-mapped table that keeps track of the list of virtual addresses assigned to each process. An analogy might be a hotel in which all the guests are assigned to rooms and the concierge can consult a table to manage the guests, their check-in times, check-out times, dirty laundry, and so on. The VMM coordinates exactly where in the system the actual data mapped to the addresses resides. In other words, it acts as a translator service, mapping virtual memory to physical memory. This function is transparent to the applications, and they operate oblivious to the process that is providing them the illusion that they have access to physical memory

  • When processes use up all available RAM, the VMM moves the memory contents least accessed out to the hard disk. This is called paging.

Windows Server 2003 32-bit thus basically has access to at least a 4GB address space, although the space is virtual and can be made up of both RAM and hard disk space. Getting back to our hotel analogy, this is like having some important or high-rate guests residing in the main hotel building overlooking the ocean, while longer-term or low-rate guests occupy the annex overlooking the street. The total room count, however, is a sum of all the rooms in both buildings.

Although we talk about a 4GB address space, this space is actually relative to how the system uses memory. In actual fact, on Windows Server 2003, the default address space available to applications is only 2GB; in fact, it is even less than that, because the 2GB assignment is shared by all processes running in user mode. The other 2GB is reserved for kernel-mode execution.

So if you study Windows Server 2003 memory architecture, you see that the 4GB space is divided into an upper segment and a lower segment, both containing 2GB address spaces. The kernel-mode threads and processes are allocated the upper portion, while the lower space is reserved for both user-mode and kernel-mode processes. The upper portion also reserves certain lower regions of total address space, which is used for hardware mapping.

The lower portion is also maintained in paging pools comprising 4KB pages. There are a nonpaged pool and a paged pool. The pages in the paged pool are the pages that can get swapped out to disk. It is usually reserved for applications, which are the processes most likely to become idle. The nonpaged pool remains in physical RAM and supports the operating system functions and critical services. The size of each page is 4KB.

More about Paging

Paging is the process of moving data in and out of physical memory, a.k.a. RAM. When the RAM-based memory pool becomes full and the operating system is called on to deliver more memory, the VMM will kick idle data out of physical memory and relocate it to the hard disk in a structure called a page file.

The VMM manages the memory pool by assigning space in the pages that are identified as either valid or invalid pages. The valid pages are located in physical memory and become immediately available to an application when it needs it. You can think of them as being “online” to the application, like a line of credit. Invalid pages are “offline” and not available to any demanding application. The invalid pages are stored on disk.

Although idle applications and data are stored in the offline pages, the VMM maintains pointers to the addresses so that it can recover the data when it needs to. For example, when an application or data in the page file is referenced, the operating system needs to get it back to RAM as quickly as possible. At this point, a page fault is triggered, spawning a new thread to access the data in the page file. But for the recalled data to fit back into the active memory pool, idle data that has been hanging around gets bumped and sent offline to disk. Now you know why fast and reliable hard disks are recommended in data- and memory-intensive applications.

The VMM performs a series of housekeeping chores as part of the paging routines:

  • It manages the data in the offline page file on a first-in, first-out basis. Data that has been on disk the longest is the first to make it back to physical memory when RAM frees up, unless something on the disk is explicitly needed. In other words, the VMM does not simply recall data idle on the hard disk to be idle in RAM. But the VMM continues to move data back and forth from RAM as long as RAM keeps freeing up and idle data needs to be paged out to the hard disk. The “hot” data the VMM keeps an eye on is known as the working set.

  • The VMM performs what is known as fetching when it brings back data from the page file. In addition, the VMM also performs what is known as page file clustering. Page file clustering means that when the VMM fetches, it also brings back some of the surrounding data in the page file, on the premise that data immediately before and after the required data might be needed in the next instant as well. This technique thus tends to speed up data I/O from the page file.

  • The VMM is smart enough to conclude that if there is no space in RAM to place returning data from the hard disk, the least needed data in RAM must be displaced and banished to the hard disk.

You can manage and control the parameters by which the VMM operates and factors such as the size of the page file; these are very important to understand.

Address Windowing Extensions

To support very large address spaces, such as 8GB on the 32-bit Windows Server 2003, the AWE have to be invoked. The AWE is to Windows Server 2003 what the old DOS extenders and EMS libraries were to the legacy PC-based operating systems and the early 16-bit versions of Windows (remember Pharlap?). The AWE is included in the 32-bit APIs that allow applications to address more than the 4GB that is addressable through the standard 32-bit API.

AWE lets applications acquire physical memory as nonpaged memory It then dynamically maps the views of nonpaged memory to the 32-bit address spaceyou could call this conjoined memory addressing. A comprehensive, bit-by-bit investigation of AWE is beyond the scope of this book, because as a DBA or application developer, you can let Windows Server 2003 and SQL Server 2005 handle the additional addressing for you. All you have to do is enable the use of AWE.

Tip 

You can unravel AWE in SQL Server 2005 Books Online.

AWE is enabled for SQL Server 2005 applications using the system stored procedure sp_configure, which can take an on or off bit for enabling or disabling AWE, respectively. It has no effect on 64-bit systems.

The SQL Server Address Space

Each instance of SQL Server started up on your computer consumes an address space that is divided into a number of areas that hold the various modules of code that make up the DBMS. Each address space, for each instance, holds the following components:

  • The executable code   This area of the address space includes the Open Data Services (ODS), the SQL Server engine code, and the SQL Server Net-Libraries. In addition, distributed queries can also independently load an OLE DB provider DLL into the address space, as will extended stored procedures and OLE Automation objects.

  • The memory pool   This area of the address space is used for the data structures that use memory in an instance of SQL Server. The typical objects using the memory pool include the following:

    • System-level data structures   These are structures that hold global data for the instance, such as database descriptors and the lock table.

    • The buffer cache   This is the cache that holds the buffer pages into which data pages are read (discussed shortly).

    • The procedure cache   This is the cache that holds the execution plans for T-SQL queries that have been executed, or are in the process of being executed, in the instance. (See the section “SQL, Query Optimization, and Query Processing” later in this chapter.)

    • The log caches   These are the caches of buffer pages used to read and write the log pages. As discussed in Chapter 2, the log buffers and the associated log files are managed separately from the data buffers and the actual data files.

    • The connection contexts   The connection contexts maintain data representing each connection in the instance. The data includes state information and the parameter values for queries and stored procedures, cursor positions, and tables under reference.

  • The stack space   The stack space is allocated by the operating system for threads that are started in SQL Server. The default stack size is 512KB.

The memory pool is a fluid structure, and SQL Server constantly adjusts the amounts of memory being allocated to various components to optimize performance. Every time a client connects, for example, SQL Server has to allocate memory to the new connection. And then it has to release it when the client disconnects. New and altered databases and all their objects are also constantly changing the allocation and usage of memory. The buffer cache, the procedure cache, and the log caches are also factors that influence how memory is used and released.

Tip 

The applications you create and how you code your queries can have a direct bearing on SQL Server performance. You want to keep the number of one-way and round-trips to the server as low as possible, but conversely you also have to keep the amount of data sent up and down as low as possible. I discuss this consideration in more detail in Chapters 15, 16, and 17.

SQL Server, like all DBMS systems, strives to minimize disk thrashing by using the services of a buffer cache in memory. This buffer cache holds pages read from the database. According to the SQL Server development team, they have spent a lot of time making sure that SQL Server 2005 makes maximum use of the buffer cache and minimizes the disk I/O of database files. When the buffer runs out of memory, the operating system will start swapping pages out to the page file, using the processes described earlier. Naturally, the system will slow down, so it is important on mission-critical systems to add more “real” memory when the VMM goes into overtime. The bigger the buffer cache, the better SQL Server will respond. As you know, the Standard and Enterprise Editions of SQL Server can use all the memory you can install in a machine (minus the OS of course).




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net