Partager via

SQLOS and Memory Management

  • Article

From SQL Server 2005 the SQLOS was implemented don SQL Server. We can see the SQLOS as a layer used by SQL Server to interact with the Operating System.

The need to have the SQLOS on SQL Server is given by the need to adapt to changes at the hardware and architecture levels. Windows is defined as a multipurpose Operating System, which allow running and working with as many and different types of applications, but this is not the best scenario for SQL. This is why SQLOS is introduce, to allow SQL to have some kind of degree of control on the platform that is running. SQLOS will manage all the SQL parts from a central location, allowing a more efficient way to work.

SQLOS mange many SQL systems and have many functions assigned, like:

  • Deadlock detections
  • Resource Management
  • Memory Management
  • IO Scheduler

SQLOS structure on Memory Management:

SQLOS is built by many components, like: Memory Nodes, Memory Clerks, Memory Caches and Memory Objects.

  • Memory Nodes: are SQLOS internal objects that are in charge to provide localization for the memory allocations, they are hidden from any memory manager different to SQLOS. Memory nodes consist on many memory allocators, one of the more commons allocators is the Single Page Allocator, who is in charge of assigning memory pages from SQLOS in 8Kb multiples, the same size as a page on a SQL Database.
  • Memory Clerks: because the memory nodes are hidden to memory manager’s different of SQLOS, so if a client of the SQLOS memory manager needs to do a memory allocation it will use its memory clerk, Each SQL components has its own memory clerk that allows it to allocate memory. The memory clerks provide allocation methods and statistics, which can be helpful to do review and control the amount of memory that is consumed by each component.
  • Memory Objects: a memory object is heap object and requires a memory clerk to assign its memory. There are three types of memory objects: variable memory heap or common heap, incremental memory heap or heap mark/shrink and fixed heap.
  • Memory Cache: is a mechanism to cache heterogeneous type of data with a given cost for each entry. Usually there is a given state associated with an entry. A cache implements lifetime control of an entry, its visibility, and provide some type of LRU policies (last resource used).

Since now why have explain the internal structure of SQLOS related to memory management.

Now let’s see another part of the memory management.

Buffer Pool:

SQL Server organize the memory assign to i ton 2 different regions: Buffer Pool and MenToLeave (or reserved memory). If you were using AWE, them it would be 3 regions, been the last the space use by memory assigns per AWE. The Buffer Pool it’s the most relevant of the 3.

The Buffer Pool is the cache for the Databases which are filled by the Buffer Manager, who is in charge to Access and update the data pages. Of course, is preferable to access the data pages on the memory cache instead of accessing at Disk, because IO operations can consume many resources and take a relative long time to finish, this is why we should use the most possible amount of memory possible to have a good performance on cache. If you’ll like to control the amount of memory used by the buffer pool you can use the configurations max server memory and min server memory, this configurations control the size of the buffer pool and NOT the amount of memory used by SQL Server as is the common belief.

When SQL Server is starting, it reserve the MenToLeave section, it try to do it on a contiguous address range of VAS (Virtual Address Space), then it decide the max size for the Buffer Pool. The default size for the MenToLeave region is 384 Mb, where 128Mb are for the worker thread stacks and 256Mb are for memory allocations outside of the Buffer Pool.

MemToLeave = Reserved Memory + (max worker threads * worker thread stack size)

The Worker thread stack is 512Kbfor 32 bits systems, 2Mb for 64 bits systems and 4Mb for IA64 systems; the number of threads is configured on max worker threads, on SQL 2005 and forward0 is the default. The number of worker threads will depend on the number of CPUs, for the example will use 256 worker threads on a 32 bit system:

MemToLeave = 256Mb + (256 * 0,5Mb) => MemToLeave = 384Mb

The Reserved Memory size can be modified using the startup parameter –g. if we use the parameter –g512, we’ll reserve 512Mb making the MenToLeave 640Mb

The Buffer Pool assigns pages when they are requested, depending on the internal and external requirements. The size of a database page is 8Kb, so the pages are assign and drop on 8Kb portions to the Buffer Pool, this is the same size used to assign pages on the Memory Nodes. On this point is where SQLOS and the Buffer Pool converge, the memory node provides localization for the page, which is assigned through a single page allocator on the buffer pool.  

Memory Node ---> Single Page Allocator ---> Buffer Pool

All SQL Server components are optimized to assign 8Kb of memory allocations, so they can make allocations using the single page allocator of SQLOS and in consequence through the Buffer Pool. However there are cases where there’s a need for bigger allocation, this will use a different cache, on those cases you’ll need to use different allocators like: Multi Pages Allocators and Virtual Page Allocators, they will allocate memory outside of the Buffer Pool, using the reserved memory region of the MenToLeave.

When AWE is used, the allocations are going to be done through this mechanism and not through a single page allocator. The API used by AWE creates a region on the Buffer Pool where reference views to the physical memory are going to be stored.

 

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”