26

I've heard MS SQL Server takes up as much RAM as it can to cache results. Well, it's not leaving enough bargaining room for our little server's RAM.

How do I change the settings to limit the amount of RAM it can use?

MS SQL Server running on Windows Server 2008.

6
  • 3
    @DavidSchwartz: I think you'll find that a lot of MS SQL DBAs will limit the amount of RAM that the DBE can use. It often has a performance impact on anything else running on the system.
    – wfaulk
    May 9, 2012 at 22:46
  • 2
    @wfault: "By default, SQL Server can change its memory requirements dynamically based on available system resources." Many people like to mess with things just for the sake of messing with them, but what they're actually doing is inhibiting the server's ability to tune itself to changing load. If it ain't broke, you can't fix it. This is most commonly done by people who like to see lots of free memory to make themselves feel good, but actually, any memory that's free is also unused -- that is, wasted. May 9, 2012 at 22:49
  • 5
    @DavidSchwartz: I understand where you're coming from, and in any other situation I would agree with you, but MS SQL doesn't seem to actually optimize this very well, IME.
    – wfaulk
    May 10, 2012 at 0:21
  • 1
    It actually was broke, as it would grind the server to a halt... Mar 26, 2013 at 17:07
  • 1
    I agree with this dude: sqlservercentral.com/blogs/glennberry/2009/10/29/… Jul 31, 2013 at 18:36

2 Answers 2

31

From How to configure memory options using SQL Server Management Studio:

Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Memory node.
  3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

You can also do it in T-SQL using the following commands (example):

exec sp_configure 'max server memory', 1024
reconfigure
1
3

To Restrict MS SQL Service memory consumption:

Set "max server memory" in SQL Server Management Studio

  1. Now we will set the "max server memory" option to limit the memory usage by SQL Server. We can do this by right-clicking on our instance and choosing "Properties".

  2. In Object Explorer, right-click a server and select Properties. enter image description here

  3. Click the Memory node as shown below:

enter image description here 4. Under Server Memory Options, enter the amount that you want for  Maximum server memory. Below as you can see we are setting up max server memory to 4096 MB (i.e. 4 GB).

enter image description here

To Restrict MS SQL Service memory consumption:

We can set "max server memory" also by using a T-SQL script:

The following example sets the max server memory option to 4 GB:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Ref: https://technet.microsoft.com/en-us/library/ms191144(v=sql.105).aspx

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .