SQL Server Memory Performance

Question

I have two instances in one server. I configured the max memory with the same number on each instance. Total 36 Gigs and I gave 17 to each. Why the target memory keep coming up and down on one instance? (moves from 17 to 12 or between) but in other instance its fixed at 17? Also the total server memory is fixed at 10 Gigs.

Answer 1

I configured the max memory with the same number on each instance. Total 36 Gigs and I gave 17 to each

This is a poor configuration, change is ASAP. You have allocated 34 GB to SQL Server and just 2 GB to OS, in such scenario you are bound to starve OS from memory resulting in paging or memory trimming of SQL Server processes.

If you want to fix the memory for both the instances start with 15 GB for each instance and leave 6 GB for OS. This is considering that you do not have any other services being used like SSAS/RS/IS, if they are their, you need to give 2-3 GB more to OS so that these extra services can use it.

Why the target memory keep coming up and down on one instance? (moves from 17 to 12 or between)

Target memory can change as per the load on the system, nothing alarming but, having said that you cannot just rely on one counter you have to look at lot of other counters related to memory.

• BDAR: Great opinion. Instance A uses less memory(Target memory is very higher than server memory). I was going to remove some memory from this Instance also but, I saw some spikes whenever we do DCR. What do you recommend? DCR happening rarely. In a regular time the memory are getting wasted.
• Shanky: When you say target is much higher than server memory, what is server memory, total server memory ? Can you add output from target and total server memory in the question.
• BDAR: Target server memory= 17 Gigs(its the one I gave it to SQL Instance) and Total Server memory = 9 to 10( The SQL server currently allocating memory).
• Shanky: This might be indication of memory pressure but again to confirm please see counters like PLE, Free list stalls/sec, Memory grants pending, Free pages/sec. Please put these counters in performance data collector and let it run for 6-7 hours( which must include peak hours). Then we can infer correct result