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.

Whence SQL Server Backup Buffers?

Recently I saw a question on Twitter #sqlhelp about backup buffers, asking if they came from the bpool or MTL.

I think the question is trickier than first realized :-)

MTL (or mem-to-leave) is at this point more of an historic term than anything else.  This term was most relevant in the days of 32-bit OS.  You can search old blog posts (not of mine) and old forum posts to see tons of fun related to 32-bit Windows and attempting to ensure that multi-page allocations and Windows direct allocations had enough available memory in the first 4 GB of address space. (Hunt around for "-g" startup option and "/3GB" switch to *really* appreciate 64-bit Windows & SQL Server.)

Next, SQL Server 2012 saw a very significant change in SQL Server memory management.  Previous to SQL Server 2012, there was both a single page allocator (SPA) and a multi-page allocator (MPA) in SQL Server.  While single page allocations were under governance of "max server memory", multiple page allocations were not.  SQL Server 2012 instead saw the introduction of the any page allocator, which took over the jobs of both previous allocators.  On a related note, the multi-page allocations which were previously outside of "max server memory" control were moved under control of "max server memory".  (I'm really glad that happened - in general it made server behavior much more predictable for a given configuration.)

Now about the idea of "coming from the buffer pool."  SQL Server still uses the term "stolen memory" to describe behavior and within its memory accounting.  But in SQL Server 2012 and beyond, the bpool is a consumer of memory from the memory manager in much the same way as others.  So although the bpool may shrink in order that another consumer can grow, that's a process managed by the memory manager.  And the bpool is usually on the "need to shrink" side only because it tends to be one of the largest, if not the largest, consumers.

OK... so now I'll mention what I *think* the question really meant.  I think the question was meant to determine:
"Are backup buffers governed by 'max server memory', or should I plan for backup buffers outside of 'max server memory' like we still do for SQL Server worker threadstacks?"

That's a pretty good question.  If you search around, you may find a number of posts where the conclusion is... "I don't remember if its included in max server memory or not." :-)

Let's see what can be learned with a quick little experiment.

Don't try this at home.  :-)

Only if you have exclusive access to the memory resources on the server AND if a significant read load on the storage won't disrupt anyone.

What will happen if I run a backup to DISK='NUL' on a very large database with buffercount = 1024 and maxtransfersize = 4194304 (4 mb)?  Altogether, that should be 4 GB of memory allocation - should be quite noticeable.

I'm testing on SQL Server 2016 RC2.

Here's how things looked before the backup.

   
Kinda boring.  Memoryclerk_backup has nothing in node 0 or node 64.
'Total Server Memory (KB)'  is just a hair over 10GB, even though 'Target Server Memory (KB)' is just shy of 32 GB.  'Max server memory' is actually 120000 MB.  There hasn't been much happening in this instance for a few days.

Lets fire up a backup.

 

The test database is several terabytes, but in this case I'm writing the backup to NUL and I'll cancel the backup as soon as I learn what I want to know.

How do the backup memory clerks and performance counters look now?

Memoryclerk_backup on node 0 now has just over 4 GB allocated to it.  Total server memory has increased by... let's see... 14947744 KB - 10753248 KB =  4194496 KB.  That's exactly the size of the allocation to the backup clerk.

What about stolen memory?  It increased by 5457832 KB - 1257344 KB = 4200488 KB.  That's a little bit more than what memoryclerk_backup picked up.  In part because I opened a second query window while testing :-)

Remember - the memory isn't really stolen from the buffer pool necessarily.  Notice that 'Database Cache Memory (KB)' is the same 9467600 KB before and during the backup.  'Free Memory (KB)' has declined by 28304 KB - 22312 KB = 5992 KB.

Doesn't that number look suspicious?

4194496 KB (backup allocation) + 5992 KB (additional stolen memory) = 4200488 KB

Whew! Glad that worked out :-)

So some memory really was stolen.  But it wasn't stolen from the bpool, which remained the same size during the backup (until I canceled it) as it was before the backup.  The additional stolen memory came from the free memory.

Now, because target memory in this SQL Server instance is so much lower than 'max server memory' (my VM got shrunk from 128GB RAM to 32 GB RAM and I never adjusted 'max server memory'), I can't say that I proved that backup buffers will always be from within the allocation governed by 'max server memory'.  Can I say it's left as an exercise for the reader?

It's late and I gotta get some sleep...

:-)

Why the read bytes/sec valleys in SQL Server backup to NUL?

It only took about a year of elapsed time for me to figure out the explanation below:-)

The perfmon graphs below are from backup to NUL on the same performance test rig as my previous post (http://sql-sasquatch.blogspot.com/2014/08/ntfs-cache-sql-server-2014-perfmon.html).  No other significant activity within SQL Server or on the server at the time.  1 hour eight minutes 59 seconds to backup 2.25 TB of database footprint.  Not bad, but why does bytes/sec throughput dip so much at a few spots?  Especially when reads/second have increased?

Maybe because read latency is higher then? Nope, read latency trends roughly with bytes/second.  Latency tends to be lower when bytes/second is lower. 

This behavior during SQL Server native backup puzzled me to no end when I first saw it on a live system about a year ago.  I thought it was due to high write cache pending on one controller of a dual controller array.  High write cache pending typically results in increased read and write latency.  My theory was that the higher write latency could lead to backup in-memory buffer saturation, in turn throttling read activity and read bytes/sec.  But in this inhouse testing, high write cache pending is eliminated as a potential throttle due to backup target "DISK=N'NUL'".

The reverse correlation of reads/sec with sec/read is even stronger than the positive correlation of bytes/sec and sec/read.  Cherry-red latency is lowest when brick-red reads/sec is highest.

So, what’s happening, and how can it be remedied?  I haven’t nailed down the specifics yet*, but this is due to fragmentation/interleaving.  How do I know?  Because it was nonexistent when the database was pristine after creation for testing purposes, and increased after a few things I did to purposely inject fragmentation :-).  When the fixed number of backup read threads hit fragmentation of some type(s), they start issuing smaller reads.  The smaller reads are met with lower read latency, but the tradeoff still results in noticeable dips in bytes/second throughput.

How can it be fixed?  In this specific case, if the reader threads are tripled, most of the time there will be increased read & write latency and not much increase in bytes/sec since its limited by SAN policy to 800 mb/sec max on this particular system.  But, during those noticeable throughput valleys (which bottom out at 240 mb/sec), the higher number of threads will submit more aggregate small reads/sec… resulting in higher bytes/sec (maybe even close to 720 mb/sec during the valleys since current thread count hits 240 mb) and shorter elapsed time for the backup.  I’ll try a few things, and there will be a followup post in the future.

*Not sure if this is due to free extents only yet, or if free pages within allocated extents also cause smaller backup reads.  I don’t think that backup reads will  be smaller for extents that are fully page-allocated but have interleaved tables and indexes among them.  More testing needed to find out specifically what kind of interleaving is causing this.

*****

After I published I noticed I hadn't included any of the per LUN measures from this backup.  8 equally sized LUNs, each with an equally sized row file for the database that's being backed up.  A nearly equal amount of data in each of the files.  And the behavior?  Almost exactly the same on each LUN at all points of the backup to NUL.