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...

:-)

Perfmon "Current Disk Queue Length" to LUN w/single Tx Log; writes bound by 32 per txlog limit

I was going to ask this question on #sqlhelp:Are #SQLServer outstanding txlog writes limited by number of log buffers/txlog?  If so, is buffers/txlog fixed or configurable?

But by chance I found the answer:
Slide 6 of this deck from Microsoft's Ewan Fairweather addressed the question.
http://sqlbits.com/Downloads/246/Designing%20Highly%20Scalable%20OLTP%20Systems.pptx
Slide 6 says that the transaction log has 127 linked buffers and allows 32 outstanding IOs.
**Update - I've since learned that there are 128 txlog buffers per SQL Server txlog, with a maximum of 32 of them allowed for async inflight writes to the transaction log. End update** 

Ewan's presentation seems to be focused on SQL Server 2008R2/Windows 2008 R2 - but that's fine because so am I at the moment :)

Paul Randal also mentions the 32 outstanding write IOs per log file in his post "Trimming More Transaction Log Fat".
http://www.sqlperformance.com/2013/01/io-subsystem/trimming-more-transaction-log-fat

In the graph below, I want to drive up CPU utilization.  I've already made sure that the current CPU utilization is almost all SQL Server with very little other CPU consumed, and that there is a strong correlation between CPU utilized and logical reads on the system.  So to drive up the rate of database work, I've got to drive up CPU utilization - don't want to waste SQL Server licenses :) .

There are two main databases on this SQL Server instance, and each has data files spread over multiple volumes/LUNs.  Tempdb unfortunately has multiple datafiles (since the server has 48 logical CPUs) all residing on the same LUN.  So tempdb suffers from occasionally stressing its IO queue, as is evident below.  Windows will allow a total of 256 IO requests per LUN either in the HBA service queue for the LUN or in a Windows wait queue for the LUN.  Tempdb gets close to that limit :)  It actually used to hit that limit for a good chunk of time... so the HBA maximum transfer size was increased from the Windows default 512 kb to 2 mb.  That eased some queuing pressure on tempdb and on other LUNs as well.

Current Disk Queue Length and %CPU Busy



The somewhat surprising element to me was that, with the exception of a single operation which occurs at 9:35 am (I think this is a transaction log expansion though it could be the wrap-around) activity to the LUNs here artfully named "Logs Vol1" and "Logs Vol2" has a maximum value of 32 for "Current Disk Queue Length".  **update 12/30/2013** I previously thought that the peak in activity for one of the LUNs was due to transaction log activity like expansion/wraparound/shrink.  Probably not in this case.  Its more likely just that the LUN in question hosted more than just the single transaction log, and some of those outstanding IOs were for other LUN contents. **end update**

Current Disk Queue Length and %CPU Busy


That might be easier to see if I shrink the time period a bit and shrink the scale of the axis for "Current Disk Queue Length".  Of course, my choice of colors might make it unreadable instead - I'm never sure.

In this case, that happens to be the service queue depth for these LUNs.  (Its default service queue depth in most cases.)  But I suspect that SQL Server is not basing its activity on the service queue depth for the LUNs - rather I think its something like the number of uncommitted transaction log buffers allowed that is actually the limit.  Why wrestle to understand the difference?  Well... if I restricted the service queue depth to 16 instead of 32, and SQL Server is limiting its behavior based on outstanding transaction log buffers, the "Current Disk Queue Length" would continue looking the same, even though write latency measured by perfmon and overall performance would likely degrade.  On the other hand, if SQL Server really is taking the LUN service queue depth into account... if this was a SQL Server instance on top of IBM XIV storage, for example, I could probably crank the queue depth WAY up.  The XIV allows lots of outstanding IOs per LUN... although a really long service queue to an XIV may increase average service time, in my case its all about throughput.  So increasing throughput is acceptable even at the expense of average latency.

But, according to Ewan's presentation (and Paul Randal's post)... 32 outstanding writes per txlog is the limit, so I've got to find a way to bring down the write latency with that in mind.