SQL Server Memory problems and AWE

Sort out the basic content related to SQL Server Memory and AWE.

1. Basic concepts of SQL Server Memory
There are several important terms involved in SQL Server Memory:

Buffer Pool (BPool)
SQL Server uses several scattered address spaces for internal processes, including storage compilation, execution plan, index creation, and pointer application.

MemToLeave
The continuous address space reserved by SQL Server at startup for external components larger than 8 kb in the process space, such as the expansion process

Automation components, connection servers, etc.

Reserved
Reserved address space for future use.

Committed
The address space currently used by the process, either physical RAM or page file space.

Allocation
Allocate memory resources to consumers.

What does SQL Server do during initialization?
1. Calculate and retain MemToLeave
SQL7.0 is 256 M, SQL Server 2000 is 384 M, 2005 and 2008 is 256 M (thanks to nzperfect's Reminder ).

2. Check the minimum physical memory and VAS supported by the OS and allocate the following space:
A) buffer Header
B) BPool
C) array of the BPool trail
3. Return LazyWriter to zero
LazyWriter regularly checks the BPool to see whether to increase or decrease it. (Because the interval is generally 60 seconds, it seems relatively lazy... True Image)
4. Release the reserved MemToLeave to ensure that the MemToLeave address space is continuous.

SQL Server does not retain Min Server Memory as soon as it reaches the Min Server Memory setting.

In that setting. For Intel systems, SQL Server requires at least 4 MB of memory. If not, the SQL Server releases the memory to the OS.

. If you do not need the SQL Server, the memory will not be garbled, but the memory will not be spit out in general, in order to avoid significant performance degradation.

2. VAS and AWE
When SQL Server is available for less than 3-4 GB, SQL Server can use Virtual Address Space (VAS)

. 2 GB is used in user mode, and 2 GB is used in system (kernel mode. That is to say, in this case, SQL Server uses up to 2 GB of memory (because of

Other applications can only use less than 2G, such as 1.6G ). For example, run DBCC MemoryStatus on my 32-bit test machine. The result is as follows:

Below:
Memory Manager KB
VM reserving 1683800
VM Committed 293084
The reserved virtual address space is about GB, which occupies about MB.

For machines with more than 3 GB memory, AWE (Address wing Extensions) is required to allow ing to additional memory, up to 64 GB.

AWE is applied. From the perspective of Task Manager, the SQL Server process uses only MB of memory, and the memory used by AWE is not displayed. So read SQL

The most convenient way for the Server to occupy Memory is to use SQLServer: Memory Manager-

Total Server Memory (KB ).

You can also view it through dbcc memorystatus or the following query:
SELECT * FROM sys. dm_ OS _performance_counters
WHERE counter_name IN ('target Server Memory (KB) ', 'total Server Memory (KB )')

Target Server Memory (KB) is the maximum amount that can be eaten, and Total Server Memory (KB) is the current amount.

The SQL Server process usage can also be queried as follows:
SELECT *
FROM sys. dm_ OS _ring_buffers
WHERE ring_buffer_type = 'ring _ BUFFER_RESOURCE_MONITOR'

There are three steps to enable AWE:
1. Add/pae in boot. ini to allow Windows Server to access more memory (up to 16 GB)
2. Assign "Lock Pages in Memory" to the SQL Server process"
3. Set "AWE Enabled" in the configuration to 1.

For Windows Server 2003, if AWE is enabled, SQL Server Always uses it for dynamic management. At startup, it will be allocated as a startup

The Memory of the workload is then dependent on the amount of Memory required to be consumed until "Max Server Memory" is reached ". If SQL Server

In case of Memory pressure, the Memory will be released as much as possible until "Min Server Memory ".

We recommend that you set "Max Server Memory" to ensure that other processes have Memory usage. For 32 GB Memory, you can leave 2 GB, for 64 GB Memory, you can leave 4 GB

. If multiple instances exist at the same time, you need to prevent one instance from consuming too much memory.

AWE is not required for 64-bit machines. However, we recommend that you grant the "Lock Pages in Memory" permission to the user account that runs the SQL Server process,

Prevent SQL Server from writing data to the disk if the memory is insufficient. For 64-bit servers, AWE is used by default, so the following query may return a non-zero value:
Select sum (awe_allocated_kb)/1024 as [AWE allocated, Mb]
From sys. dm_ OS _memory_clerks
The purpose is to avoid working set trimming.

3. SQL Server Memory problems
When configuring SQL Server, it is important to know how it handles the memory pressure. Memory pressure is divided into two categories: VAS and physical memory. Physical memory pressure

The force can be seen directly from the OS, so it can also be called external memory pressure. It may also be caused by its own process, so it can also be called internal

Memory pressure.

The overall architecture of SQLOS to manage memory pressure is as follows:
Resource Monitor
/| \
Low Physical Internal/External Low VAS High Physical Internal/External |

Resource Monitor (RM) is the core of everything. Once there is a dynamic news, it will be immediately known and broadcast to memory clerks.

Sort it here for now.

The reason for studying this problem is that in a case, the customer's 64-bit SQL Server will encounter AWE Allocated suddenly starting from 6 GB (MAX Server

Memory) fell to 2 GB (Min Server Memory). After several hours, the SQL Agent encountered Virtual Memory Too Low, causing the task

Failed. I did not reach a conclusion after studying it for a long time, so I had to Escalate it. Engineers on the upstairs Team suspect that system problems have caused force

Reclaim memory to allow customers to use perfmon log and adplus (ADPlus is a tool provided by Microsoft Product Support Service (PSS ).

Can solve the problem of stopping any process or application that responds (Hangs) or fails (CRASHES .) Collect system information.
Another memory-related case is that the 4G memory of the client machine is inexplicably eaten by the SQL Server process by 2.9 GB, but the process occupies

CPU usage. This is now helping collect information. The customer seems to know less about this than me.

A recent memory shortage, a Report Viewer optimization, and a Data Mining cannot solve this problem. After a long delay, we had to use Escalate.

Blow... Okay, concentrate on the remaining Discuz! NT case, know how many I have to make up for it with effort.

References:
SQL Server 2005 and AWE Memory
Http://www.jimmcleod.net/blog/index.php/2008/06/03/sql-server-2005-and-awe-memory/

AWE related
Http://blogs.msdn.com/slavao/archive/2005/11/15/493019.aspx
Http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
Http://support.microsoft.com/kb/918483

SQLOS's memory manager: responding to memory pressure
Http://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx

SQL Server Memory
Http://www.sqljunkies.ddj.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk

Slava Oks's WebLog
Http://blogs.msdn.com/slavao/archive/tags/default.aspx