2

I'm doing a performance test on a high traffic (16000+ users, estimated 1000+ concurrent at peak) Microsoft Dynamics database.
The server has gone on line a few months ago, and I'm doing some tests to see how it is handling the current traffic.

While checking some performance counters during business hours I came across an oddity in the target and total server memory.
While I would only flag this as being an issue if the target memory was clearly higher than the total memory, combined with perhaps other counters (depending on the amount of difference) the results here are somewhat odd.

Using:

SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters 
where (object_name like '%Buffer Manager%' and counter_name like '%page life%')
or (object_name like '%Memory Manager%' and counter_name like '%memory grants pending%')
or (object_name like '%Memory Manager%' and counter_name like '%target server memory%')
or (object_name like '%Memory Manager%' and counter_name like '%total server memory%');

I came to the following results:

counter_name                 |    cntr_value  
------------------------------------------------
Page life expectancy         |    14887
Memory Grants Pending        |    0
Target Server Memory (KB)    |    44040192
Total Server Memory (KB)     |    44040184

Clearly there is currently no memory pressure, and the page life expectancy is higher than required. However it looks like at some point the sql server was exactly 8 KB short of memory.

Something about these numbers seems odd to me, but I can't put my finger on it.
Is this an indication that at some point the sql server was on the verge of a memory problem?
Am I correct in thinking that because these numbers are so similar, I can dismiss it for now, and test again when a decent amount of time has passed?

1

I must say this is normal and nothing to worry about. This blog tells how target server memory calculated. Please see the forumula below

Target1 = Current committed pages of SQL Server + ( Available Physical Memory - min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2))

ullAvailPageFile: The maximum amount of memory the current process can commit, in bytes. This value is equal to or smaller than the system-wide available commit value. To calculate the system-wide available commit value, call GetPerformanceInfo and subtract the value of CommitTotal from the value of CommitLimit.

If (Max Server Memory < ullAvailPageFile)

{

Target2= Max Server Memory

}

Else

{

Target2=Total Physical Memory

}

Target Server Memory (KB) =Minimum (Target1,Target2)

So if AvailablePhysicalMemory is very high (or) when MaxServermemory is low then Target Server Memory (KB) would give you the MaxServerMemory else value derived from above formula.

As per my findings using this formula even if target server memory is bit greater than total it is fine. And this is by virtue of how the value is calculated.

If target server memory is greater that total server memory it does not always signify a memory pressure. You must come out of this belief(if you have one). Like you mentioned other counters have to be taken into picture. SQl Server changes its target and total server memory values many a times and this depends on load on system. Consider a scenario where you run index rebuild for all big tables there would be flurry of activity and PLE would certainly drop increasing target memory but this does not means it is a memory pressure. SQL Server memory just adjusted itslef to current load.

In ideal world you should have RAM such that it can shold all databases in memory but this is many a times not possible.

  • +1, I definitely do not have the belief that one counter is a catch-all, however one counter can indicate that further investigation is needed. My main concern here was the oddity of the numbers. While every other counter shows a perfectly healthy system, the target memory is 8KB higher, which is an odd number and looks like more like a quirk of programming than an actual amount that was required. Following the above formula however it looks like during some memory intensive operation (possibly indexing as you said) the memory was 8KB short. – Reaces Aug 21 '15 at 8:07
  • 1
    Starting from 2012 onwards things have changed and I guess this has led to change in calculation for different counters as well. These things are undocumented. As of now you dont need to worry as I dont think there is memory pressure as such but I will revert after futher investigation about whether the formula which was used before has changed or not – Shanky Aug 21 '15 at 8:58
  • If you do end up looking into the internal workings of the way the memory counters now work, and you by some miracle remember this request, drop me a comment with a link to your findings! I'm very much interested, and ty for the help. – Reaces Aug 21 '15 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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