High memory ulitization SQL 2012

Question

Hi all, I have a SQL 2012 CU#2 instance running on Server 2008r2 sp1, I am seeing incredibly high memory utilization on the SQLSERVER.EXE process and I have a vendor doing a POC for data mining complaining of sluggishness.   Server has 24gb RAM and Dual 6 core Intel processors 3.3ghz

Any help is appreciated, dont really know what info to post which is why i am coming to the best

JC

---

jon centeno

Answer 1

Hello Jon,

This is exactly what should happen. I'm betting your min and max memory option is not set, so it'll eat up as much memory as possible: http://msdn.microsoft.com/en-us/library/ms178067.aspx

If you set the max server memory to a lower value.

-Sean

---

Sean Gallardy, MCC | Blog

Answer 2

Sean

I have these set already,I wasnt too keen on the 2 pedabyte allocation for max so I set it to the following

Jon

---

jon centeno

Answer 3

Jon,

You said you have 24 GB of RAM but you allowed SQL Server to use up to 36 GB of RAM - which of course doesn't exist so it'll use the MAX amount of memory the system has, which is 24 GB.

Set it to 16 GB or something lower utnil you can figure out what a better value is through testing.

-Sean

---

Sean Gallardy, MCC | Blog

Answer 4

Sean

Had it set significantly lower originally I only increased it based off this recommendation, as you can see i played it very conservative.  Ill set it back to what it was originally reboot the server and let you know the outcome.  However I feel that these programmers are going to state sluggishness still and they dont fully understand how SQL utilizes memory.  

To set a fixed amount of memory

In Object Explorer, right-click a server and select Properties. Click the Memory node. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory. Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB).

-Jon

---

jon centeno

Answer 5

Jon,

It can be a pain. Set it to a value that seems like a good amount and monitor the free MB Memory on the server until you can always keep ~100 MB free.

If the programmers are saying their response is sluggish I would look at their queries.

Sorry for the brevity.

-Sean

---

Sean Gallardy, MCC | Blog

Answer 6

Hi,

You may use my script to calculate max server memory setting. You can get it from the Technet Gallery at here.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 7

sorry things have been chaotic, ill post results asap.  

---

jon centeno

Answer 8

and Im leaning towards their queries as well

---

jon centeno

Answer 9

When you've changed the max memory - and the apps are running - could you :

a) Return the values on this query?

SELECT total_physical_memory_kb, available_physical_memory_kb,

system_memory_state_desc

FROM sys.dm_os_sys_memory;

b) Track down queries with high logical reads , which is linked to memory pressure

---

Jack Vamvas sqlserver-dba.com