4

I am running MSSQL 2008 Enterprise and in the configuration of the server the Max memory amount is set to 2147483647 MB.

My Question is, the server has 192 GB of ram,the number in the config can't be increased, if the config is set to that, does that mean the server can use whatever it needs? Or how can i enable it that the server uses all the ram that is available.

If i open the task manager, the memory usage never goes above 19 GB.

Thank you for the help

Improve this question
1
  • yes, its a 64 bit, 24 core machine. Windows Server 2008
    – Brirby
    Commented Jan 5, 2012 at 14:41

2 Answers 2

Reset to default
5

Knowing this is 64-bit SQL Server 2008 Enterprise simplifies the discussion, so thanks for that. The first thing to say is that it is almost never right to leave the max server memory configuration option at the default of 2147483647.

The max server memory option only limits the size of the buffer pool (used for all single-8KB allocations pre-2012). SQL Server can also require significant amounts of non-buffer pool memory (allocations > 8KB). Other instances, applications, and the OS also need room to work (and 2GB is not always a good rule of thumb there). Amit Banerjee, an Escalation Engineer with Microsoft PSS has three excellent posts on this topic, which I encourage you to read.

You will generally want to set max server memory to a fairly conservative maximum value to start with, monitor the actual amount of free memory on the server over a full load cycle, preferably over a decent period of time, and gradually increase it as appropriate and necessary.

The exact level to start at depends on how much non-buffer pool memory SQL Server might be expected to require, and how much memory will be needed by other instances, applications and the OS. On a server dedicated to a single instance of SQL Server, I might start max server memory around 160GB with 192GB installed. Your circumstances might dictate that a much lower starting point is indicated.

I also recommend you consider using the Lock Pages in Memory (LPIM) option (that link explains why in great detail). This will be enabled by default if the SQL Server service is running as LocalSystem (which is not a best practice). See this CSS article for details including how to tell if SQL Server is currently using LPIM or not.

Memory used via this mechanism is not visible to Task Manager; it is better to monitor SQL Server memory usage via Performance Monitor counetrs and/or the sys.dm_os_sys_memory DMV.

Improve this answer
0
2

The way you have it set, SQL will use all the memory it requires, up to and including all available RAM (your 192 GB, minus whatever the OS is using).

You may want to limit the amount of memory SQL will use, in order to guarantee the OS and other apps running have enough. I usually leave at least 2GB for the OS and let SQL have the rest.

As for memory usage never going up over 19GB, that is likely a reflection of the workloads in SQL. How large is your main database? How many active connections?

Improve this answer
3
  • The main database is around 600GB, during the day on average 1200 active connections.
    – Brirby
    Commented Jan 5, 2012 at 14:43
  • During a quiet time on the server, you could try running some queries that generate full table scans against the largest tables. You should see memory utilization go over 19GB, assuming you are able to read enough data, as SQL reads the data into its cache. How big are your largest tables?
    – datagod
    Commented Jan 5, 2012 at 15:22
  • the largest table is 55243376 KB
    – Brirby
    Commented Jan 5, 2012 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.