SQL Server 2016 Memory Accounting: A Suspicious Surprise

Originally published 2018 July 2

Updated 2020 December 15

*****

I wanted to update this blog post with a link to a fix that corrects the memory double-counting described here (and the condition that leads to it).

KB4536005 - Improvement: Fix incorrect memory page accounting that causes out-of-memory errors in #SQLServer #SQLServer 2019 CU2 #SQLServer 2017 CU20 #SQLServer 2016 SP2 CU15

KB4536005 - Improvement: Fix incorrect memory page accounting that causes out-of-memory errors in SQL Server (microsoft.com)

*****

Wading through all of the SQL Server memory-related perfmon counters to understand how they related to each other took me a really long time.  Time-series graphs that show the relationship help me tremendously, and when I started trying to account for SQL Server memory years ago I couldn't find any.  So I started to blog some time-series graphs, under the theory that either my understanding was correct and my graphs would be helpful to someone... or they'd be wrong and someone would correct me.
Well... its been about 5 years and my graphs haven't generated too much discussion, but they've really helped me 😀😀😀

Perfmon: SQL Server Database pages + Stolen pages + Free pages = Total pages
http://sql-sasquatch.blogspot.com/2013/09/perfmon-database-pages-stolen-pages.html

Working with SQL Server 2016 and some demanding ColumnStore batch mode workloads, I began to see suspicious numbers, and graphs that didn't make sense to me.  Today I got pretty close to figuring it out so I wanted to share what I've learned.

The following graphs are from a 4x10 physical server running Windows and SQL Server.  Four sockets, 4 NUMA nodes.

Perfmon has SQL Server "total" memory numbers for each SQLOS memory node, in addition to the instance-level measure.  The sum of "total node" memory across the nodes should be equal to the instance measure of "total server" memory.  That checks out.

 Database cache, free memory, and stolen memory report measures at the instance level and at the SQLOS memory node level.  Let's check out the instance measures first.  The sum of these categories should be "total server memory".  That checks out, too.

Now, since we've got instance measures of database cache, stolen, and free memory at the SQLOS memory node level as well, we can check whether they sum to "total node memory" on their respective nodes.

Uh oh.  I smell trouble...

Hey!! Trouble on this node too...

SQLOS node 001 also shows a suspicious graph...

Not to be left behind, SQLOS node 000 also shows an unexpected graph.

OK.  So either some memory is being counted multiple times... or some memory is being lost by the SQL Server memory manager and not reflected in "total node memory" and "total server memory".  Maybe I can narrow down the problem space.

Lets start by looking at Free Memory.

That graph above looks pretty good, even though it is pretty volatile.  Lets zoom in a little bit just to make sure.  The graph below makes me pretty confident - the node level "free memory" measures and the instance level "free memory" measures are aligned.

OK, since free memory accounting seems to agree between SQLOS nodes and the instance level measure, lets look at database cache.  The graph below shows that the instance measure aligns nicely with the node measures.

That leaves just Stolen Memory.  Take a look at the graph... and... yep, there's the problem.  Or at least there's a problem.

Having seen that Stolen Memory and only Stolen Memory indicates a discrepancy between the instance measure and the sum of the node measures, lets consider again the memory position on SQLOS memory node 002.  The sum of free + stolen + database is nearly 50GB greater than total node memory for an hour!  When there is not much stolen memory in that SQLOS memory node at all!!

Just to clarify what I mean by "not much stolen memory at all" on node 002...

[yeah, i name my excel workbook tabs "perfmon" and "graphs".  i use RC style format.  and its not unusual for my perfmon tabs to have over 1000 columns.]

Now... if I graph the difference between the sum of node-level stolen memory and instance-level stolen memory...

Now what if I sum database cache, stolen and free memory across all 4 SQLOS nodes and subtract the instance-level Total Server memory? Lets lay that - in transparent red - on top of the blue we just graphed.

So here's what I think is happening: in some cases of batchmode queries, a portion of memory is getting double-counted.  Its counted in one SQLOS memory node as "database cache" and in another memory node as "stolen memory"!!  That leads to the discrepancy seen here for instance and memory node measures - in stolen memory, and in total memory.

At this point i'm not sure if this represents a performance and scalability problem, or if it just masks some problems by making memory state and trends more difficult to accurately observe.  When i learn more about this, i'll blog an update post and link the two together.

Ciao for now!

[for another example of this condition, from a 2 vNUMA node VM please see the later blog post below]

SQL Server 2016 Memory Accounting Part II: Another Suspicious Example
https://sql-sasquatch.blogspot.com/2018/10/sql-server-2016-memory-accounting-part.html

Windows paging file usage & SQL Server 2017 RTM: sys.dm_os_sys_memory is Tricky

OK... using my laptop SQL Server 2017 RTM CU1 instance to show something tricky about reconciling paging space usage.  The SQL Server 2016 versions I checked briefly displayed the same behavior I describe below.

A new tool was added to the belt recently - I like the idea.  Since SQL Server will respond to server level low memory messages, I like being able to see the state of server memory.  For that has Microsoft wrought sys.dm_os_memory.

sys.dm_os_sys_memory (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql

There's good stuff in there for investigation of OOM scenarios.

Let's see what turns up on my laptop.

Based on the column names and values above, seems natural to think:
total_page_file_kb - available_page_file_kb = used page file kb
11027476 kb - 3047668 kb = 7979808 kb

Holy cow! Is my laptop using nearly as much paging space as there is RAM on the laptop??
Weird. If something forced that much paging space use relative to RAM on the laptop... I certainly wouldn't expect system_memory_state_desc = 'Available physical memory is high'!!

Lets double-check by using wmic in an administrator cmd.exe.

OK... its clear already something is afoot.  AllocatedBaseSize is the current size of the pagefile indicated in 'Description', in mb.  So... 2880 mb is the size of the pagefile.  And only 1684 mb currently used, with a peak usage of 2752 mb.  Almost 8 gb of pagefile used as reported by SQL Server sys.dm_os_sys_memory?  Nope - no way.

So what's up?  Sometimes memory accounting in SQL Server takes some work. 😀😀😀

I stared at the numbers for a bit and had a hunch... double-checked and it tied out.

11027476 kb - 8077528 kb = 2949948 kb ≈ 2880mb

Hmm.

So:
total_page_file_kb - total_physical_memory_kb =AllocatedBaseSize

Aha!  Looks like total_page_file_kb column is misnamed. Because...


total_physical_memory_kb + AllocatedBaseSize = total_page_file_kb

So total_page_file_kb is really more like 'total virtual memory kb'!

OK.   Let's look back at the definitions in BoL.

sys.dm_os_sys_memory (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql

The description of available_page_file_kb is wrong. Since total_page_file_kb is described as the commit limit - or the virtual memory as sum of physical memory + paging space - the available_page_file_kb is then not best defined as an available portion of the paging file but as an available portion of the commit limit or virtual memory.


All right - enough for now.  See you again soon!

L'esprit de L'escalier - Whither SQL Server Backup Buffers?

I've got a long, long list of interesting questions.  And I blog frightfully slowly :-)

So... why don't I start blogging more of the questions?  Someone else may very well be able to springboard from one of my questions to a *really* astounding answer!  Maybe.  I hope my questions are that good anyway :-)

Sometimes the questions come to me as a form of L'esprit de L'escalier - not something I wish I'd said but a question I wish I had asked.

So it is with my question: "Whither SQL Server Backup Buffers?"

Yesterday (or maybe really early this morning) I posed the question: "Whence SQL Server Backup Buffers?"

Here's what I noticed: 'Target server memory' 33553968 hadn't been achieved.  'Total server memory' during the backup was 14947794.  Of that 4194496 was allocated to memoryclerk_backup.

When the backup was cancelled, the memoryclerk_backup allocation was simply returned to the OS.

Why not give it to free memory?  It should have been nice, contiguous chunks of memory addresses.  The server was far from reaching target server memory.  The server wasn't under memory pressure.  This is what it looks like right now... and it would have looked nearly the same right after the backup was cancelled.

That's a lot of free memory.  Take away 4 GB from it for the backup buffers... yeah, its still a lot of free memory.

I wonder:
-If 'max server memory' wasn't being overridden by a more reasonable target (because max server memory is 120 GB on a 32GB VM), would the behavior still be the same before reaching target?  I bet it would be.
-Is this behavior specific to not having reached target?  Or when reaching target would backup buffers be allocated, potentially triggering a shrink of the bpool, then returned to the OS afterward requiring the bpool to grow again?
-What other allocations are returned directly to the OS rather than given back to the memory manager to add to free memory?  I bet CLR does this, too.  Really large query plans?  Nah, I bet they go back into the memory manager's free memory.
-Does this make a big deal?  I bet it could.  Especially if a system is prone to develop persistent foreign memory among its NUMA nodes.  In most cases, it probably wouldn't matter.

Maybe I'll get around to testing some of this.  But it probably won't be for a while.