MS SQL Memory Usage exception

Problem Description:

Environment: Mainframe, IBM 445/4 cpu/4g M

OS = Windows Server

MSSQL = Enterprise Edition Server +sp3

Condition:

My MSSQL is deactivated, in the operating system's task Manager, memory use-like prudential instructions to down to nearly 0, when I started the MSSQL service, Task Manager in the Memory usage status indicator bar to about 70%, and then carefully look at the Task Manager SQL process memory usage is much less than 70 m ( 70 megabytes is not mistaken), and the possible maximum memory in Task Manager is 3.6G. Restarting the server is also the same situation. I have about 10 db in MSSQL.

Problem:

1. Why does the memory Usage status bar in Task Manager differ significantly from the memory usage that is present in the process (2.5g/70m)?

2. Where is the real problem with this situation?

Problem Reason:

This behavior is most likely to occur when the AWE option for SQL Server is enabled, and the following code is executed to see if your SQL Server is starting the AWE option, and the AWE option is enabled if the following code shows the value of column run_value as 1.

EXEC sp_configure ' show advanced options ', 1

Reconfigure

Go

EXEC sp_configure ' awe enabled '

Go

This failure occurs when the AWE option is enabled, and the original description can be found on SQL Server Books Online:

Use System Monitor (Performance Monitor in Microsoft Windows nt®4.0) to retrieve information about SQL Server memory usage and available memory. Task Manager does not provide accurate memory usage information for AWE. Therefore, the amount of memory consumed by the reported sqlservr.exe is not correct. To get the correct SQL Server memory usage, use the total server memory (KB) performance counter (which can be activated through System Monitor), or select memory usage from sysperfinfo. For more information, see Monitoring memory usage.

Additional considerations:

When the AWE-enabled option is started, SQL Server eats up all available memory at startup (this memory is reserved for memory outside the system, or the amount of memory set in the SQL Server's maximum memory limit), so if your server is not a dedicated database server, Please configure the maximum memory limit for SQL Server before enabling AWE options

In addition, if you have less than 2G of memory, the open AWE option is invalid, can be turned on, but does not reach the effect