About SQL Server services consuming too much memory---limiting database memory usage

Recently took over to maintain a good project, telnet to the server when the program execution special card, the use of Task Manager to view the system resource usage, monitoring for a period of time found that the CPU is small, but memory consumption is high, And then found that it is Sqlserver.exe this service occupies a large amount of memory, reached the nearly 2G server configuration 8G memory, restart the SQL service after the memory is only about 50M, but a few days later reached a near-2G memory footprint, the online search for the next optimization strategy.

1. Set up SQL Server memory allocation using the statement:

--a. Configuring Minimum Memory

--Set the minimum memory to 0MB

exec sp_configure N ' min server memory (MB) ', 16

--b. Configuring Maximum Memory

--Set the maximum memory to 256MB

exec sp_configure N ' max server memory (MB) ', 266

Execution Result:

Configuration options ' min server memory (MB) ' has changed from 14 to 16. Please run the RECONFIGURE statement to install.
Configuration options ' max server memory (MB) ' has changed from 444 to 266. Please run the RECONFIGURE statement to install.

Finally execute the following statement to install

Reconfigure with override

Execution Result:

The command was successfully completed.

System Service log:

Configuration options ' min server memory (MB) ' has changed from 14 to 16. Please run the RECONFIGURE statement to install.

Configuration options ' max server memory (MB) ' has changed from 444 to 266. Please run the RECONFIGURE statement to install.

2. Manual Operation settings

Right-click Local server--server properties--memory settings, click OK.

After the general setup, it is best to restart the SQL service.

About SQL Server services consuming too much memory---limiting database memory usage