One of my older posts talked about SQL Server Memory Basics. The infamous MemToLeave region was shown with a default value of 384MB. This post was penned a long time back and the SQL landscape has changed since then. Since the post was mainly based on showing the memory architecture for SQL Servers in a 32-bit environment, the calculations shown were based on the 2GB user address space limitation for x86 architecture.
With the adoption of 64-bit architecture, the 2GB user mode address space (3GB if you use the /3GB switch) went out of the window. One of the lesser known facts that I have seen multiple times while addressing memory related concerns for non-BPool memory consumption on MSDN is that the non-BPool region is now not just 384MB.
Before I go onto explain this, I would like to point out that the Max Server Memory setting for SQL Server (irrespective of architecture) is concerned with the BPool region. This doesn’t limit the amount of memory that can be used by components which request for contiguous allocations above 8KB in size. The non-BPool region is now limited not by your virtual address space in 64-bit environments but by your physical memory (RAM) on the server. Let me explain this with an example.
If you have a SQL Server instance on a 64-bit box with Max Server Memory set to 24GB and the total physical memory on the server is 32GB, then your SQL instance can now theoretically use the remaining 8GB for contiguous memory allocations requested above 8KB in size. This is where I keep advocating the understanding of the environment before setting Max Server Memory for the SQL instance. Leaving 2 or 3GB for your Operating System and the non-BPool memory is just not going to cut it if you have components active within SQL Server which require huge amounts of contiguous memory. I have already listed out the non-BPool memory consumers in a previous post which outlines the most common consumers. The reason this happens is that on 64-bit environments you are no longer constrained by the 2 or 3GB user address space limitation because your address space runs into a few TBs. So, the limiting factor here is your physical memory (RAM). Once you have set Max Server Memory to finite value, the SQL instance is told that the Buffer Pool cannot grow beyond this value but this doesn’t limit non-BPool allocations. As long as you have available physical memory on the server, SQL can continue using that for contiguous allocation requests above 8KB in size. This makes it highly important for you to evaluate which components in your SQL environment can make such allocation request and set the Max Server Memory in such a manner that you do not starve either of the two memory regions in the SQL Server or any of the other applications running on the server or the Operating System. Unless you do this, you could end up troubleshooting SQL Memory Pressure very frequently on the box especially issues like paging or general server unresponsiveness/sluggishness before you hit the nail on the head!
To summarize, two points that you need to keep in mind for 64-bit SQL environments:
1. Max Server Memory setting only limits Buffer Pool
2. Non-BPool memory region for SQL is effectively now: Total Physical Memory – (Max Server Memory + Physical Memory used by OS and other applications)
A previous post of mine talked about SQL Server memory architecture in a nutshell. Let’s find out what are the common consumers of the much talked about MemToLeave region and what you should be looking for in-case you run into MemToLeave issues.
Major Consumers of MTL
- Use of Linked Servers – You can find out the linked servers that being used in your environment using the sysservers system catalog.
- Use of XML documents – You would have to find out if any queries or procedures perform any kind of XML data manipulation or use sp_xml_preparedocument.
- Extended Stored Procedures or sp_OAcreate calls
- Extended stored procedure usage can be identified by inspecting the SQL Server Errorlog and searching for the word using. The first XSP calls is logged in the Errorlog in the following manner: Using ‘<dll name>’ version ‘<version>’ to execute extended stored procedure ‘<XSP name>’.
- If you are using sp_OAcreate, then this information would be logged in the SQL Errorlog for the first invocation of sp_OAcreate using the same pattern mentioned above. The only difference would be that the DLL name would be odsole70.dll.
- Query Plans which are larger than 8 KB in size
- If you are using SQL Server 2000, query syscacheobjects and search for entries which have values greater than 1 for the pagesused column.
- If you are using SQL Server 2005, search for plans greater than 8KB using the DMV sys.dm_exec_cached_plans. Inspect the size_in_bytes column value and search for entries which have values greater than 8192 bytes.
- Use of SQLCLR (Applicable to SQL Server 2005 and above) – Check the memory usage for SQLCLR clerk using DBCC MEMORYSTATUS.
- Backups using larger MAXTRANSFERSIZE parameter – In such a case find out BACKUP DATABASE/LOG commands and inspect the MAXTRANSFERSIZE parameter value. The lowest size that can be provided is 65536 bytes.
- Using Network Packet Size higher than 8192 bytes. You can find all connections on SQL Server 2005 that use more than 8KB of connection using the following DMV: select * from sys.dm_exec_connections:
select * from sys.dm_exec_connections
where net_packet_size > 8192
I will give a brief account of how SQL Server memory is organized (in a nutshell). There is much more to it under the covers! :)
SQL Server Memory space is organized in the following manner:
Each instance of Microsoft SQL Server 2000 has an address space with two main components, each of which has several subcomponents:
There are two main areas of memory within SQL Server’s address space, the buffer pool (BPool) and a second memory pool sometimes called the "MemToLeave" area. The contents of the SQL Server buffer pool include cached table data, workspace memory used during query execution for in-memory sorts or hashes, most cached stored procedure and query plans, memory for locks and other internal structures, and the majority of other miscellaneous memory needs of the SQL Server. The Bpool handles memory allocations for 8K and below. All allocation requirements above 8K is satisfied from the MTL region.
- The number and size of the executable files and dynamic link libraries (DLLs) used by an instance of SQL Server varies over time. In addition to the executable files and DLLs used by Open Data Services, the SQL Server engine, and server Net-Libraries, the following components load in their own DLLs, and these DLLs can allocate memory themselves:
- Distributed queries can load an OLE DB Provider DLL on the server running the instance of SQL Server.
- Extended stored procedures are implemented as DLLs that are loaded into the address space of the instance of SQL Server.
- The OLE Automation system stored procedures are used to create instances of OLE Automation objects. Each class of OLE Automation object loads its own code into the address space of the instance of SQL Server.
The memory pool is the main unit of memory for an instance of SQL Server. Almost all data structures that use memory in an instance of SQL Server are allocated in the memory pool. The main types of objects allocated in the memory pool are:
System-level data structures
These are data structures that hold data global to the instance, such as database descriptors and the lock table.
This is the pool of buffer pages into which data pages are read.
This is a pool of pages containing the execution plans for all Transact-SQL statements currently executing in the instance.
Each log has a cache of buffer pages used to read and write log pages. The log caches are managed separately from the buffer cache to reduce the synchronization between log and data buffers. This results in fast, robust code.
Each connection has a set of data structures that record the current state of the connection. These data structures hold items such as parameter values for queries and stored procedures, cursor positioning information, and tables currently being referenced.
The high level memory architecture remains the same in SQL Server 2005 and 2008. The difference is that we introduced a change called Memory Clerks which were responsible for memory allocation requests for requirements for a particular object/cache store. The allocators changed to Single Page Allocator (any allocation required below 8KB) and Multi Page Allocator (any allocation above 8KB). With the help of SPA and MPA, we are now able to track Buffer Pool and non-Buffer Pool memory requests in a more structured manner when components within SQL Server are requesting for memory. However, if your DLL/CLR code hosted within SQL Server memory is calling VirtualAlloc() directly, then tracking those requires extensive data collection and analysis.
How to track SQL Server 2000 memory usage using DBCC Memorystatus
How to track SQL Server 2005 memory usage using DBCC Memorystatus