Introduction
SQL Server 2012 Memory changes
Scope
Reason
Demonstrating The Anomaly
- Perfmon Counter Showing Incorrect Value
- DBCC MEMORYSTATUS Showing Incorrect Value
Behavior in SQL Server 2008/R2
Conclusion
Connect Item
Suggested Reading
See Also

Introduction

Starting from SQL Server 2012 SQL Server memory code has been changed. In the previous versions of SQL Server there were two distinct memory managers. There was one memory manager which handled allocation sizes of 8k or less and another for greater than 8k.. Starting from SQL Server 2012 there is Single memory manager and single page allocator which manages memory allocation because MAX SERVER MEMORY in SQL Server now controls much more than buffer pool.

SQL Server 2012 Memory changes

Books Online has limited information about memory changes. Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

Prior to SQL 2012, the buffer pool both “managed” memory and was a consumer of memory for database pages. It’s management of memory meant it allocated 8Kb pages of memory for other consumers like plan cache. Roll forward to 2012+, the buffer pool is a pure consumer of memory from SQLOS which manages all of the memory.

I would request reader to please read This Support Article about memory changes to SQL Server 2012. Support article aptly defines and outlines memory changes in SQL Server 2012.

Scope

The post is about Incorrect value shown by both DBCC MEMORYSTATUS output for Buffer Pool and Perfmon counter SQL Server Buffer manger: Target Pages. Both these show very large Target value. Some times twice or thrice larger than physical RAM present on the system which is absurd. This post is to inform reader that these counters have been deprecated so don't rely on the value shown. Microsoft has no where written,through books online, that these counters are deprecated and they do appear in DBCC MEMORYSTATUS output and perfmon counters which should not. It would be really helpful to reader and SQL Server users if they remove these counters and also write a document about it. The post is outcome of lot of confusion going regrading SQL Server memory counters on various SQL Server forums

Reason

The reason is because these counters have been deprecated from SQL Server 2012 Onwards but they still appear in List of perfmon counters and also in DBCC MEMORYSTATUS. Unfortunately Microsoft documentation does not mentions this anywhere in public but yes if you have a look at SQL Server, Buffer Manager Object it does not shows Target pages counter so yes its been deprecated. And this is reason, I assume, why it shows absurd value.

Demonstrating The Anomaly

Test would use SQL Server 2014 Evaluation edition to demonstrate this anomaly. We would use DBCC MEMORYSTATUS and perfmon counter to show that values shows by these objects for buffer manager target pages is incorrect

Perfmon Counter Showing Incorrect Value

Query 1

Select @@Version

go

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

from sys. dm_os_process_memory

GO

select (total_physical_memory_kb/1024) as Toal_RAM_MB from sys.dm_os_sys_memory

GO

Select * from sys.dm_os_performance_counters

where counter_name='Target Pages' and object_name='SQLServer:Buffer Manager'

The output is below

Image 1

we can see that memory currently used by SQL Server is 222 MB. If you have look at target pages cntr_value it is showing 797008 pages. A page size in SQL Server is 8 KB so this comes to 6383204 KB which is ultimately 6 G.
Now Max server memory is set to 2.5 G

Image 2

Now SQL Server is consuming just 222 MB when max server memory is 2.5 G which clearly shows it does not wants to grow and there is not memory pressure as such. We can further prove that there is no memory pressure by running below query

Query 2

Select * from sys.dm_os_performance_counters

where counter_name in

('Target Server Memory (KB)',

'Total Server Memory (KB)')

or (object_name='SQLServer:Buffer Manager' and counter_name in('Buffer cache hit ratio',

'Page life expectancy '))

The output is

Image 3

The above counters show we have good PLE and BCHR. Now look at target Server memory it is 1.2 G and what is shown by buffer manager target is 6 G. The above value of target memory of 1.2 G is correct keeping in mind that we have set max server memory to 2.5 G. Target server memory is one which SQL Server assumes , as per workload, that it would need to perform efficiently. Target server memory is more of a prediction which in case of Buffer Manager it is doing incorrect.

DBCC MEMORYSTATUS Showing Incorrect Value

Now Lets concentrate on DBCC MEMORYSTATUS output. Few Information regarding DBCC MEMORYSTAUS can be read from This Support article but please be aware it was applicable till SQL Server 2008 R2 from 2012 onwards due to change in SQL Server memory it might not be accurate.Below is output of DBCC MEMORYSTATUS since we are more concerned in Buffer Pool

Image 4

First its difficult to say whether value show for Buffer pool is in KB or Bytes. But his is neither in KB not in Bytes its Number Of pages. Again if we see target pages it comes to 797008 (which is number of pages) which ultimately comes to 6G. Same as we observed for Target page counter in Image 1

Behavior in SQL Server 2008/R2

Now lets examine same behavior in SQL Server 2008/2008 R2 below is output of query 1

Image 5

The target pages value is converted in MB and is 112880 which is approximately 112 G. Now lets have a look at max server memory

Image 6

Max server memory is 112 G so target actually matches the configured.

Conclusion

We can easily conclude that target pages value show by Perfmon counter and DBCC MEMORYSTATUS is incorrect and this anomaly has started from SQL Server 2012 after changes to SQL Server memory has been made. And the reason for this anomaly is because counters are deprecated and no longer supported and hence gives incorrect value. Reader should not use this counter when looking at target pages. Reader must use SQL Server Memory Manger: Target pages instead. This article also aims towards conveying MS folks to remove these counters from perfmon and also remove Buffer Pool value from DBCC MEMORYSTATUS output to avoid confusion.