Why does SQL Server use a small amount of memory?

The memory of SQL Server cannot be accessed. From Task Schedule, we can see that SQL Server only uses 88MB of memory. The actual machine has 12 GB of memory and the available memory exceeds 8 GB.

At that time, I thought it was caused by enabling AWE, so I checked the connection to his server. However, the database is 2005 Enterprise Edition 64-bit, so you do not need to enable AWE. In addition, it is ignored even if it is enabled.

Use the following script to query the memory usage of SQL Server:

Select physical_memory_in_use_kb, locked_page_allocations_kb, * fromsys. dm_ OS _process_memory

The actual memory used is 2 GB, which is far beyond what the task manager sees. (You can also view it through Perfmon's Total server memory (MB ).

At that time, I felt very strange. I checked the SQL Server Error Log and found information similar to the following:

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

12:21:08. 16 Server Large Page Granularity: 2097152

12:21:08. 21 Server Large Page Allocated: 32 MB

I guess the Lock Pages In memory feature is enabled during this period and will be confirmed later. After enabling Lock Pages In memory, SQL Server uses AWE APIs to Lock the memory Page. Therefore, this part of memory usage is not displayed In the Working Set.

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

Now this problem is clear. In fact, SQL Server still works normally. We recommend that you use DMV or Perfmon to query SQL Server usage. You may not be allowed to directly view the Working Set information.

In addition, when we saw the above Large Page information, we thought that LargePage was enabled for the database, but 834 Trace Flag was not enabled for dbcc tracstatus check, so the big data function was not enabled. Large Page is enabled only when the 834 Trace Flag database is enabled.

When Large page is enabled, similar information is displayed in the Database Error Log:

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

I have found two articles on the Lock Pages In memory/working set Mechanism. For details, refer:

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

WhySQL Server is using so LESS memory