Why does SQL server use a small amount of memory?

I saw a post in the forum yesterday, saying that the memory of SQL Server has never been used. 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. Because after Lock Pages In Memory is enabled, SQL server uses awe
APIS locks the memory page, so the memory usage of this part 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.
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 the usage of SQL Server and view the working directly.
Set information may be inaccurate.

 

In addition, when the above large page information was displayed, it was thought that largepage was enabled for the database, but DBCC tracstatus was not enabled for 834
Trace flag, so the big data function is 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