3

I'm doing some digging into potential memory/configuration problems on a sql server 2012 SP2. I stumbled upon something that seems a bit strange in the performance counters as well as the dbcc memorystatus information. here's the info from the buffer pool section of dbcc memorystatus:

enter image description here

If i'm reading things correctly the value is the number of 8 KB pages. The server which is a VM has 16 gb of memory total and SQL server is assigned a min/max value of 12GB. The "Target" value of 32751616 gives me about 262GB which just so happens to be the total amount of memory in the VM host. If i check the "target pages" performance counter on the server i see the same number. I'm guessing this hints of the VM not being configured properly but what i'm really wondering is how the sql server will handle this. Will it impact performance / will the server try to commit more memory than is actually available?

Looking at the memory manager things seem to be looking more normal:

enter image description here

I've been searching the web for information about target buffer pool exceeding max memory but I haven't been able to come up with much so any input is much appreciated.

  • Edit. Adding a picture of the performance counter. enter image description here
  • Sorry, been a long day. It should be VM as in VMware, I corrected it now so it might be easier to understand what I'm talking about. SQL servers buffer pool seems to believe it has access to the full amount of memory in the VM host machine, even though the server only has 16GB assigned, and the SQL server is assigned only 12 of those. I've found plenty of information on the buffer pool target being lower than the assigned amount of memory to sql server, but i'm having a hard time understanding how it's even possible for this number to exceed the maximum memory amount. – Hal Nov 25 '14 at 22:43
  • Value Buffer pool target is not in amount of 8 KB pages but the value is in Kbytes . So as per output the target buffer pool memory is 32751616 Kbytes which would be 31 G. I have assumed here that that value is in KB but its definitely not in terms of 8 KB pages. – Shanky Nov 25 '14 at 22:54
  • The perfmon counter for Target pages is the same number - link I also read this article link which i interpreted as the number refering to number of pages, but maybe I've misinterpreted the information. If you have a reference to it being KB I'd gladly read it. Even if it is value in KB, it still far exceeds the memory on the server so my question would remain the same. – Hal Nov 25 '14 at 23:12
  • But that value does specifically says its pages. Target pages counter actually refer to pages. I would further confirm and get back to you – Shanky Nov 25 '14 at 23:34
2

Value Buffer pool target is not in amount of 8 KB pages but the Is actually number of pages which you were correct to assume . So as per output the target buffer pool memory is would be 8*32751616 which comes to 249G. Which is really absurd

Reason:

Now for reason why Bpool target is showing value 250 G in DBCC MEMORYSTATUS and memory manager is showing 12 G as Target committed is because the Counter SQL Server Buffer Manager: target Pages is DEPRECATED from SQL Server 2012 onwards. Microsoft did not specifically mentioned this anywhere but I found it from SQL Server Buffer Manager Object BOL Document. If you read this article you wont find counter target pages because its deprecated. You would also see the same counter present in SQL Server 2008 R2.

Other reason may be I assume your system is NUMA system with NUMA nodes. I am sure if you see MEMORYSTATUS output it would have memory allocation breakdown for various nodes like Node0, Node 1...Node 64. So target includes target memory allocation for all NUMA nodes. If you note Buffer pool in MEMORYSTATUS output, it has single entry and is not restricted to particular node. Plus this value is calculated during startup and does not actually shows correct value after a period of time so you should not refer to it. Instead refer to memory manager output

enter image description here

Edit:

As per question regarding buffer pool target pages on NUMA node believe me its not showing correct value for buffer pool. For my system with 4 G of RAM target pages are 7979008 with almost nothing running on the system. This is totally absurd and I would request you not to look at this counter. I had a chat with one of the experts on this field to confirm the behavior and he told me you should avoid looking at buffer pool output and target pages specially on NUMA system its calculation( way of calculation) is not correct. I can raise a connect Item but it would take few months to get response from MS team.

Not every thing in DBCC MEMORYSTATUS output matters to normal users many things are internal to MS and they would just not give any information regarding the same. If you want to monitor SQL Server memory usage please use below query.

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If you really want to monitor SQL Server target memory I would suggest you to look at

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

There are other counters as well you can get from This Link.

Few other interesting counters are

Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design

Free List Stalls/sec – Number of requests per second that had to wait for a free page

PS: Please let me know if I have missed anything or if something is not clear

Edit: Here is the Connect Item for your issue lets wait what MS folks have to say about it. Please vote it

  • Thanks for your time Shanky. You are correct about it utilizing NUMA. However I am not convinced. As I've said before the performance counter that specifically says "target pages" is the exact same number as the target in buffer pool. See my edited question that now contains an image of said counter. Furthermore, I have other SQL servers also running on NUMA VMs and they show the correct amount both in target pages perfcounter and the buffer pool section of dbcc memorystatus. As an example I have a SQL server with max 62GB, showing 7864320 in both commited and target,roughly 62gb asuming pages – Hal Nov 26 '14 at 8:31
  • I just updated the memory of the server to 64GB and assigned 60GB to the sql server. The target pages went up times 4, same as the increase in memory. The new figure in target pages is thus 130990080. I checked the VM settings (though i am no vm expert) and I can't see any differences in the settings compared to the other machines that show a target equaling that of the maximum memory setting. However the other servers are running SQL server 2008R2, so it's hard to tell if that's the reason or if it's something else causing this. – Hal Nov 26 '14 at 9:57
  • I've been monitoring other stats than the buffer pool target but I was asking specifically about this since the numbers seem way off. After adding memory to the server things are looking a lot better (the cache was being replaced far too frequently and I was geting too much disk reads). I will let the target pages counter go for now and regard it as a bug, though i still find it a bit troublesome as it's actually a documented MS counter and as such should not be a mysterious internal MS feature. Thanks again for your time. Best regards – Hal Nov 27 '14 at 9:06
  • 1
    I checked same behaviour on 2008 r2 with NUMA nodes and it's giving correct value for buffer pool target I guess because change was made to memory manager code from 2012 onwards something is forcing this to give incorrect value – Shanky Nov 27 '14 at 12:54
  • 1
    @Hal I have updated my answer please have a look. Do let me know if any issue. I am working hard on this behavior and after new findings I updated the answer – Shanky Nov 29 '14 at 23:40

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.