How to troubleshoot SQL server memory related issues (Part 1)

From the Partner managed newsgroup:

This section specifically addresses low memory conditions and ways to diagnose them as well as different memory errors, possible reasons for them, and ways to troubleshoot.

 

Background

 

It is quite common to refer to different memory resources by using the single generic term memory. As there are several types of memory resources, it is important to understand and differentiate which particular memory resource is referred to.

Virtual address space and physical memory

 

In Microsoft Windows, each process has its own virtual address space (VAS). The set of all virtual addresses available for process use constitutes the size of the VAS. The size of the VAS depends on the architecture (32- or 64-bit) and the operating system. In the context of troubleshooting, it is important to understand that virtual address space is a consumable memory resource and an application can run out of it even on a 64-bit platform while physical memory may still be available.

 

For more information about virtual address space, see “Process Address Space” in SQL

Server Books Online and the article called Virtual Address Space (http://msdn.microsoft.com/library/en-us/memory/base/virtual_address_space.asp) on MSDN.

 

 

Address Windowing Extensions (AWE)

and SQL Server

 

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond the inherent 32-bit address limit. AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

 

On both 32- and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which may have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server.

From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information.

For more information about AWE mapped memory, see “Managing memory for large databases” and “Memory Architecture” in SQL Server Books Online  topics and Large Memory Support (http://msdn.microsoft.com/library/en-us/memory/base/large_memory_support.asp) on MSDN.

The following table summarizes the maximum memory support options for different configurations of SQL Server 2005. (Note that a particular edition of SQL Server or Windows may put more restrictive limits on the amount of supported memory.)

 

 

Min server memory (MB)

 

Provides floor

Does not automatically commit on startup

May not be able to reach floor

 

 

Max server memory (MB)

Provides ceiling

SQL Server may not be able to back off due to current usage

 

 

 

SQL Server 2000 organizes the memory it allocates into two distinct regions: the BPool (buffer pool) and MemToLeave (memory to leave) regions. If you make use of AWE memory, there’s actually a third region: the physical memory above 3GB made available by the Windows AWE support.

 

The BPool is the preeminent region of the three. It is the primary allocation pool in SQL Server, serves primarily as a data and index page cache, and is also used for memory allocations less than 8KB. MemToLeave consists of the virtual memory space within the user mode address space that is not used by the BPool. The AWE memory above 3GB functions as an extension of the BPool and provides additional space for caching data and index pages. In SQL Server 2005, the definitioin of Bpool is not so clear as SQL 2000 because SQL 2005 memory mangment is based SQLOS and they are usually considered by memory_clerks.

 

Inside SQL Server 2000’s Memory Management Facilities

http://msdn.microsoft.com/en-us/library/aa175282(SQL.80).aspx

 

SQLOS’s memory manager and SQL Server’s Buffer Pool

http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

 

 

 

SQL Server Memory Definitions

 

Data Cache

 

Consumer

        Connection – Responsible for PSS and ODS memory allocations

        General – Resource structures, parse headers, lock manager objects

        Utilities – Recovery, Log Manager

        Optimizer – Query Optimization

        Query Plan – Query Plan Storage

Advanced Consumer

        Ccache – Procedure cache. Accepts plans from the Optimizer and Query Plan consumers. Is responsible for managing that memory and determines when to release the memory back to the Bpool.

        Log Cache – Managed by the LogMgr, which uses the Utility consumer to coordinate memory requests with the Bpool.

 

 

 

 

Memory pressures

 

Memory pressure denotes a condition when limited amount of memory is available. Identifying when SQL Server runs under a memory pressure will help you troubleshoot memory-related issues. SQL Server responds differently depending on the type of memory pressure that is present. The following table summarizes the types of memory pressures, and their general underlying causes. In all cases, you are more likely to see timeout or explicit out-of-memory error messages.

 

 

*External Physical memory pressure:       

 

Physical memory (RAM) running low. This causes the system to trim working sets of currently running processes, which may result in overall slowdown.

 

SQL Server detects this condition and, depending on the configuration, may reduce the commit target of the buffer pool and start clearing internal caches.

 

 

General troubleshooting steps:

        Find major system memory consumers.

        Attempt to eliminate (if possible).

        Check for adequate system RAM and consider adding more RAM (usually requires more careful investigation beyond the scope of this paper).

 

Open Task Manager in Performance view and check the Physical Memory section, Available value. If the available memory amount is low, external memory pressure may be present. The exact value depends on many factors, however you can start looking into this when the value drops below 50-100 MB. External memory pressure is clearly present when this amount is less than 10 MB.

 

The equivalent information can also be obtained using the Memory: Available Bytes counter in System Monitor.

If external memory pressure exists and you are seeing memory-related errors, you will need to identify major consumers of the physical memory on the system. To do this, look at  Process: Working Set performance counters or the Mem Usage column on the Processes tab of Task Manager and identify the largest consumers.

The total use of physical memory on the system can be roughly accounted for by summing the following counters.

        Process object, Working Set counter for each process

        Memory object

        Cache Bytes counter for system working set

        Pool Nonpaged Bytes counter for size of unpaged pool

        Available Bytes (equivalent of the Available value in Task Manager)

 

If there’s no external pressure, the Process: Private Bytes counter or the VM Size in Task Manager should be close to the size of the working set (Process: Working Set or Task Manager Mem Usage), which means that we have no memory paged out.

 

Note that the Mem Usage column in Task Manager and corresponding performance counters do not count memory that is allocated through AWE. Thus the information is insufficient if AWE is enabled. In this case, you need to look at the memory distribution inside SQL Server to get a full picture.

 

You can use the sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through AWE mechanism.

 

select

    sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]

from

    sys.dm_os_memory_clerks

 

Note that in SQL Server, currently only buffer pool clerks (type = ‘MEMORYCLERK_SQLBUFFERPOOL’) use this mechanism and only when AWE is enabled.

Relieving external memory pressure by identifying and eliminating major physical memory consumers (if possible) and/or by adding more memory should generally resolve the problems related to memory.

         

Comments are closed.

Post Navigation