SQL Server 2012 Memory

SQL Server 2012 has made many changes to the memory manager to govern the SQL Server memory consumption in efficient way compared with earlier versions. Important changes to SQL Server 2012 memory which every DBA should be aware of is documented in this blog. If you are not familiar with the SQL Server memory architecture of earlier versions I would recommend reading THIS ARTICLE before you continue with changes in Denali memory manager.

SQL Server 2012 memory manager has now clubbed single page allocator and multipage allocator together as any-size page allocator . As a result, the any-size page allocator now manages allocations categorized in the past as single page and Multi-Page allocations.

These changes allow DBA’s to configure and control SQL Server more accurately in accordance with the memory requirements and using resource governor.

-g startup parameter

We used the -g startup option to change the default value of a region in SQL Server user address space known as "Memory-To-Reserve". This region was also known as "memory-to-leave or MTL. The "Memory-To-Reserve" (or) -g configuration option are relevant only for a 32-bit instance of SQL Server.

Multi pages allocation and CLR was part of Mem-to-reserve (-g) in In previous SQL Server versions until SQL Server 2008 R2 , From Denali they are part of BPOOL (Controlled by Max server memory) So you may have to remove –g if you have set it to give space for multipage allocator or CLR in earlier versions and migrating to Denali now.

AWE feature removed from SQL Server 2012

AWE feature was used in earlier versions of 32-Bit SQL Server to address more than 4GB of memory . This feature is now removed in Denali refer:"AWE deprecation". So if you need more memory then you may need to migrate to 64-Bit SQL server.

Locked pages in memory

Trace flag 845 is no more required to Lock Pages in memory. As long as the startup account of SQL Server has “Lock pages in memory” privilege Datacenter, Enterprise, standard and Business intelligence edition will use AWE allocator Api’s for memory allocations in BPOOL and this allocations will be locked.

Dynamic virtual address space management

In earlier versions of SQL Server 32-Bit we reserved Bpool at the startup and remaining addresses are left for MTL (Memory to reserve or Memory to leave) . In Denali virtual address space management is dynamic (we don’t reserve at startup) , So it is possible for 3rd part components to use more memory than what is configured in –g parameter.

You will notice the following messages in the SQL Server error log during server startup:

2012-10-18 15:23:13.250 spid8s Starting up database ‘master’.

2012-10-18 15:23:13.930 Server CLR version v4.0.30319 loaded.

Total Physical memory and memory model used

Total physical memory available on the server and the memory model used is logged in SQL Server error log

2012-10-18 15:23:06.690 Server Detected 131067 MB of RAM. This is an informational message; no user action is required.

2012-10-18 15:23:06.700 Server Using locked pages in the memory manager

2012-10-22 15:32:20.450 Server Detected 131067 MB of RAM. This is an informational message; no user action is required. 2012-10-22 15:32:20.450 Server Using conventional memory in the memory manager.

DMV and Performance counter changes

In earlier version of SQL Server most of the DMV’s used single_pages_kb and multi_pages_kb to refer allocations by SQL Server with in BPOOL and outside BPOOL. Now they are represented together as pages_kb. More details in THIS link

1. Memory allocations for thread stacks within SQL Server process and 2. Memory allocation requests made directly to Windows [Ex: Allocations (Heap,Virtualalloc calls ) from 3rd party Dll’s loaded in SQL Server process , objects created using OLE Automation procedures (sp_oa) etc] are not controlled by max server memory . Assume you load a DLL in SQLServer process and the DLL allocated 5GB of memory. It is not controlled by max server memory….
What is the confusion here?

[…] Make sure that the SQL server has suffient memory assigned. Be aware that the Windows Operating System, as well as other applications running on the same server, should have some RAM left. In general I would allow SQL to use all RAM except 4GB that will be reserved for the system. Also make sure that the SQL server has a minimum assigned amount of memory that correlates with the load on the databases. Karthick has a pretty good post around memory handling in SQL here. […]