SQL SERVER memory allocation and common memory issues (1)--Introduction

Original: SQL SERVER memory allocation and common memory issues (1)--Introduction

First, the question:

1. The amount of memory that SQL Server consumes has been increasing since startup:

First of all, as a mature product, the chances of memory overflow are negligible. Learn how SQL Server coordinates and shares memory with Windows. and the internal memory management mechanism of SQL Server.

2. SQL Server running in Windows 2003 or later has a sudden and sharp drop in memory usage:

Memory is the lifeline of SQL Server. In Errorlog, there is a situation:

This kind of problem is often not caused by SQL Server, but Windows feels the urgency of memory pressure, forcing SQL Server to free up memory.

3, the user in the operation, encountered memory application failure: Not the user wants to apply how much there is how many

4. Performance degradation due to memory pressure: memory pressure is one of the most common causes of performance problems.

Second, the operating system level look at SQL SERVER memory allocation:

Operating system does not lack memory does not mean that SQL Server is not short of memory

Special term: Virtual address space: The maximum address space an application can access. The data information stored in the virtual address space is not necessarily in physical memory and may be placed in a cache file (Paging file) physical memory (physical RAM): Data objects that are accessed frequently must be kept in physical memory for long periods of time in order to achieve optimal efficiency. Reserved memory (Reserved): Is the address of the virtual address space, not the real physical space, which is reserved by some APIs in advance. Committed memory (Commit RAM): The page that preserves memory is formally submitted. Eventually to physical memory, shared memory: Windows provides a mechanism for sharing memory between processes and operating systems. Can be understood as memory that is visible in multiple processes. Private Bytes: The non-shared part of the address space submitted by a process. Working Set: The address space of a process that is stored in the portion of physical memory. Page Fault (pages access error): Divided into Soft/hard, access to a virtual address space exists, but there is no physical memory of the page, it will occur pagefault, if not the access caused by a target page exists on the hard disk, will generate hard disk read and write, Fault. Another exists in physical memory, but is not placed directly under the working set of the process. Requires Windows redirection, which does not result in hard drive operation, becomes soft fault, the general soft performance impact can be ignored as long as the relationship is fault. System working set:windows Systems also have working Set that can be monitored by the Memory:cache bytes in the Performance Monitor, and page fault that occurs in system memory can be used Mmeory:cache faults /sec See. System cache: Improves disk I/O speed and can be used with Performance Monitor: Memory:cache resident bytes monitoring. Non Paged Pool (non-paging zone): A memory swap area that contains a range of system virtual addresses that can be guaranteed to reside in physical memory at all times and can be monitored by Memory:pool nonpaged bytes. This piece of cache can be shared by all, and one of the most common uses is to hold pointers to all objects (object Handles) Paged pool (page swap): System process Working set can be called into or paged out in systems space (WorkiNg set) of the virtual memory area. can be monitored by Memory:pool Paged bytes and Memory:pool Paged resident bytes. Stack: Each thread has two stacks, one for kernel mode (kernel mode) and one for user mode. Each stack is a piece of memory space that holds the calling address of the procedure or function that the thread is running, and the value of all parameters. In process: Runs in the same process's address space. Out of process: runs in different processes in the address space. Memory Leak: One is SQL Server as a process that constantly requests memory resources from Windows to know that the entire Windows memory is exhausted. The other is inside SQL Server, where a SQL Server component is constantly requesting memory, knowing that the memory that can be applied to is exhausted so that other components do not function properly. The former situation is very rare. 32-bit under Windows Address space and awe:

By default, the Windows process has a 4G virtual address space, 2G gives the kernel mentality (kernel mode), and the remaining 2G to the user mode. These two parts are strictly differentiated.

A map of the address space of any user process:

Method 1: Using the/3GB parameter in the boot. ini file, the kernel mentality can be reduced to 1G in the Enterprise Edition of Windows, speaking of user-state up to 3G.

Method 2: Using address space extension, AWE, is a mechanism that allows 32-bit applications to allocate 64GB of physical memory and map a view or window to a 2G virtual address space (windowsing). Not all memory requests use AWE, but only reserve, then commit memory calls using AWE.

Memory usage checks at the Windows level: when checking SQL Server memory usage, first check memory usage at the Windows level.

What you need to check is: The number of memory usage and memory distribution of the Windows system itself. Whether there is memory pressure, whether the pressure is more serious. Even memory usage for each process. The last is whether it affects each other.

You can use Performance Monitor implementations.

Windows system usage:

1, the overall use of analysis:

Committed bytes:

Total memory for the entire Windows system, including data from physical memory and file caches.

Commit Limit:

The maximum amount of memory the entire Windows system can request, equal to the physical memory plus the size of the file cache. If commit limit≈committed bytes, the system memory is close to the limit. If the cache file does not grow automatically, the system will not be able to provide more memory space.

Available MBytes:

Now the system is free of physical memory, directly reflecting the Windows level there is no memory pressure.

Page file:%usage and page file:%peak Usage:

Reflecting the amount of cache file usage, the more data is stored in the file cache, the greater the gap between the amount of physical memory and the actual demand, the worse the performance.

Pages/sec:

Hard page fault the number of pages per second that need to be read or written from disk. Is the sum of memory:pages input/sec + memory:pages output/sec.

Memory:page faults/sec is the sum of soft page fault and hard page fault. However, because soft page fault has little effect on performance, it is not pages/sec useful to use. Pages/sec cannot remain in a relatively high value for a long time.

2. Memory usage of Windows system itself:

Memory:cache bytes:

The working set of the system, which is the number of physical memory used by the system.

Memory:system Cache Resident Bytes: The system tells the cache to consume physical memory.

Memory:pool Paged Resident Bytes: The amount of physical memory consumed by the paging area.

Memory:system Driver Resident Bytes: The physical memory consumed by the adjustable page device driver code.

Memory:system Code resident Bytes:Ntoskrnl.exe the memory that is consumed by the paging codes.

3. System pool:memory:p ool Nonpaged Bytes (non-paging) and Memory:pool paged Resident bytes (page swap area)

Single Process usage:

Process:%processor Time: The number of CPU resources consumed by the target process, including the user state and kernel mentality.

Process:page faults/sec The number of pagefaults that occurred on the target process.

Process:handle Count Target process Handle data, thread Leak occurs if there are objects in the process that are always created and not recycled in time.

Process:pool Paged bytes The size of the Paged Pool used by the target process.

Process:pool Nonpaged Bytes The non-paged Pool size used by the target process.

Process:working set in the address space of a process, stored in the portion of physical memory.

Process:virual Bytes: The size of the virtual address space requested by a process.

Process:private bytes: A process submits a non-shared portion of the address space.

Memory is always the most important system resource.

SQL Server memory usage and the relationship between Windows: Server has two important memory counters: Total Server and target server. Total Server Memory: the sum of the buffer pool RAM allocated by itself.

Target Server Memory: The maximum amount of RAM that can theoretically be used.

SQL Server memory usage drops sharply: Cause: 1, windows in some cases, the application of too much kernel memory, instead of compressing the user state can use the physical memory. 2, some hardware drivers request too much kernel state memory, also occupy too much physical memory. 3, some applications suddenly request a large amount of physical memory. Use the following method to avoid a sharp preemption of SQL SERVER Memory:
1, open lock page in MEMORY function only 05/08 Enterprise Edition has 2, use sp_configure to set up SQL Max SERVER MEMORY. 3, upgrade the new version or patch. 4, upgrade the hardware driver.
Logical configuration of SQL Server memory Two principles: (1) Windows and other critical application services to have enough memory, do not run the process because of insufficient memory, and Rob SQL Server has requested the memory. (2) on the premise of satisfying the 1th, SQL Server uses as much memory as possible and guarantees the stability of the number of memory usage. Methods: 1, use 64-bit 2, dedicated server 3, set up SQL Server Max Server MEMORY4, give the SQL Server startup account the lock Pages in memory permission. 5, "set working set Size" do not use.
Third, SQL Server internal unique memory management mode: SQL Server open Memory tuning interface: 1, min Server memories (sp_configure): ultimately determined by Windows, does not guarantee that SQL Server uses the minimum amount of physical memory. 2. Max Server Memory (sp_configure): The data is placed in physical or buffered files and is determined by Windows. 3, set working set Size (sp_configure): do not use. 4. AWE enalbed (sp_configure): Meaningful for 32-bit systems. 5. Lock Pages in Memory (Enterprise Edition will automatically open): There is a chance to ensure the number of physical memory for SQL Server. Memory usage Category: By Purpose: Database Cache: The buffer that holds the data page. All kinds of consumer:connection:: Default 4K General: Contains statements compiled, normalized, each lock data structure, transaction context, table and index metadata, and so on.    Default 8K. Query Plan: Default 8k, Optimizer: Default 8k, Utilities: Special operations like BCP, Log Manager, Parallel Queries, backup. Default 8k, thread memory: each thread within the process allocates 0.5MB of memory. Store the data structure and related information for the thread. Default 512K third-party code requests for memory (COM,XP ... ）
Classification By application: Some memory applications are: Reserve a large chunk of memory, and then use a small block of commit, and some other memory applications directly from the address space commit, this is called stolen.

SQL SERVER memory allocation and common memory issues (1)--Introduction