Why SQL Server uses very little memory

SQL Server memory has been on the go. From the task schedule, you see that SQL Server uses only 88MB of memory, which has 12GB of memory and more than 8GB of available memory.

I thought it was a result of awe, so I connected to his server and looked at it. But the database is 2005 Enterprise Edition 64 bits, so you don't have to turn on awe. And even if it is turned on, it will be ignored.

Use the following script to query SQL Server memory usage:

Select Physical_memory_in_use_kb,locked_page_allocations_kb,*fromsys.dm_os_process_memory

See the actual memory used is 2GB, far beyond the task manager to see. (It can also be viewed through the total server memory (MB) of Perfmon.)

It was strange to see that the SQL Server error log found information similar to the following:

2009-06-0412:21:08.16 Server Large Page Extensions enabled.

2009-06-04 12:21:08.16 Server Large Page granularity:2097152

2009-06-04 12:21:08.21 Server Large Page ALLOCATED:32MB

Guessing this period opened the lock Pages in memory function and was later confirmed. Since lock pages in memory is turned on, SQL Server locks the memory page with AWE APIs, so this part of the memory usage does not appear in working set.

So in summary the AWE APIs for 32bit and 64bit SQL Server systems are used for different purposes. In 32bit it's really to extend memory access beyond 4Gb or to enable the AWE feature. For 64bit systems, it's to possibly gain performance and to ' lock pages ' for the buffer pool.

Now the problem is more clear, in fact, SQL Server is still working properly. General query SQL Server use or recommend the use of the DMV or perfmon, direct viewing of working set information may not be allowed.

In addition, when you see the information above large page that the database is largepage, but using DBCC TRACSTATUS to see that the 834 Trace Flag is not turned on, the large data feature is not enabled. Large Page is actually enabled only if the 834 Trace flag database is turned on.

Enabling large page will see similar information in the database error log:

2009-06-0414:20:40.03 Server Using large pages for buffer pool.

About the lock Pages in memory/working set mechanism I found two articles, you are interested to refer to:

Funwith Locked Pages, AWE, Task Manager, and the Working Set

Whysql Server is using so less memory