Skip to content

Understand memory allocated to SQL Server process from Windows OS Memory Management

September 5, 2014


  1. Physical Memory which can be called Primary Memory refers to actual RAM chips.
    • The system/OS takes care the mapping between the Virtual Address to Physical Address and uses virtual addressing to hide the management of physical memory from applications
    • In Windows, the total amount of physical memory available to an application is that application’s virtual address space
    • Physical memory and the paging file limit the total amount of virtual address space available to a process
    • Example: All 32-bit applications have a 4 GB process address space (2^32). Microsoft Windows operating systems provide applications with access to 2 GB of process address space, specifically known as user mode virtual address space. Each process on 64-bit Windows has a virtual address space of 8 terabytes.
    • Virtual address space is divided into two partitions: one for use by the system/OS ,kernel memory/ kernel mode virtual address space, and one for application processes, process/user mode (virtual) address space
    • All threads owned by an application share the same user mode/process virtual address space
    • The virtual address space for a process is the set of virtual memory addresses that it can use. A virtual address does not represent the actual physical location of an object in memory/RAM; instead, the system maintains a page table for each process, which is an internal data structure used to translate virtual addresses into their corresponding physical addresses. Each time a thread references an address, the system translates the virtual address to a physical address.
    • Physical memory measurements (total and in use) are reported on the Performance tab of Windows Task Manager and on the Memory tab of Resource Monitor.
    • Low Virtual Address Space (from 0x00000000 to 0x….) is reserved for application processes
    • High Virtual Address Space (from 0x…. to 0xFFFFFFFF… -maximum VAS) is used by the Operating system
    • The memory manager creates the following memory pools that the system uses to allocate memory: nonpaged pool and paged pool. Both memory pools are located in the region of the address space that is reserved for the system and mapped into the virtual address space of each process.
      • The nonpaged pool consists of virtual memory addresses that must be resided in physical memory as long as the corresponding objects are allocated. It is used by the kernel and also by device drivers installed on a system to store data
      • The paged pool consists of virtual memory that can be paged in and out of the system. To improve performance, systems with a single processor have three paged pools, and multiprocessor systems have five paged pools.
    • Example: Kernel memory including Nonpaged and paged pool/memory is primarily in the form of RAM. Paged memory can be backed up to the page file (paging out occurrence – when being in high pressure memory usage). Nonpaged memory must remain in physical RAM at all times.
    • Working Set is the amount of memory currently in use for a process, or The subset of the virtual address space of a process that resides in physical memory is known as the working set. There are Private Working Set and Shareable Working Set
  2. Threads are an operating system feature that lets application logic be separated into several concurrent execution paths. This feature is useful when complex applications have many tasks that can be performed at the same time
    • When an operating system executes an instance of an application, it creates a unit called a process to manage the instance. The process has a thread of execution. This is the series of programming instructions performed by the application code. For example, if a simple application has a single set of instructions that can be performed serially; there is just one execution path or thread through the application. More complex applications may have several tasks that can be performed in tandem, instead of serially. The application can do this by starting separate processes for each task. However, starting a process is a resource-intensive operation. Instead, an application can start separate threads. These are relatively less resource-intensive. Additionally, each thread can be scheduled for execution independently from the other threads associated with a process.
  3. User mode virtual address space of SQL Server process:
    • The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system
    • SQL Server uses user mode virtual address space allocated for the buffer cache/buffer pool and Non buffer pool
    • Nonbuffer pool is allocated for all requests for memory greater than 8KB are catered to from such as extended stored procedures, linked servers, CLR Code, COM objects, DLL, large cached plans.
    • Buffer pool includes: data (page) cache, procedure cache (query plan and procedure caches)
    • A Buffer pool/cache area which contains 8-KB buffer page caters to all memory requests up to 8 KB in size, all data and index page size. The Max Server Memory setting up to SQL Server caps only the Buffer Pool area. If AWE mechanism is enabled, only the buffer pool/8-KB buffer may reside in AWE mapped memory
      • The main memory component in SQL Server is the buffer pool. All memory not used by another memory component remains in the buffer pool to be used as a data cache for pages read in from the database files on disk.
      • Most of the buffers taken from the buffer pool for other memory components go to other kinds of memory caches, the largest of which is typically the cache for procedure and query plans, which are usually called the procedure cache
    • SQL Server Agent, SSRS, SSAS, SQL Server Replication Agents, SSIS, SQL Server Full Text Search has its own process virtual address space
    • The buffer manager manages the functions for reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk.
    • Because the buffer manager uses most of the memory in the SQL Server process, it cooperates with the memory manager to allow other components to use its buffers
    • The buffer manager supports dynamic memory allocation on Windows 32 bit platforms when AWE is enabled
    • The buffer manager supports large pages on 64-bit platforms. The page size is specific to the version of Windows
  4. Secondary Memory is hard disk
  5. Virtual memory consists of physical memory plus the amount of space in the page file, which is temporary space on the hard disk configured as page file. When RAM runs low, virtual memory moves data from RAM to a space called a paging file
  6. Enable Address Windowing Extensions (AWE) to allow SQL Server use physical memory over 4 GB on 32 bit Windows platform
    • Only if the RAM on the server is greater than the VAS (4 GB) shall SQL be able to utilize AWE
    • Memory allocated using AWE allocator API’s are not part of Process working set, hence can not be paged out (swap to page file area) and not visible in as private bytes or working set in task manger and perfmon.
    • SQL Server builds a buffer pool in memory to hold pages read from the database.
    • Analysis Services, SSIS can not take advantage of AWE mapped memory because AWE memory is used for Buffer pool area

From → DBA

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: