Welcome back to Part 5 of my Backup Internals Series. In this blog, I want to explore some thoughts around maximizing the performance of your SQL Server backup operations.

Blog Series Table of Contents

Fast, Good, or Cheap…

Many of us have heard the tidbit that there’s 3 options to any endeavor: Fast, Good, and Cheap, but you can only ever pick 2. When it comes to accelerating your (FULL) backup performance, there’s a similar set of choices you can make, most of which have a trade-off of some sort.

Gather Requirements

If I were to have an opportunity to re-evaluate an environment’s backup strategy and schedule, there’s a number of questions I’d want to try to answer first. Remember, the scope of this thought exercise is thinking about FULL Backup strategy from a purely performance angle.

Questions to Ask

• Backup maintenance windows: How long and how frequently?
  Every night for 4 hours? Anytime over the weekend? For 3 minutes on Sunday, between 22:55 and 22:59?
• How many databases to back up and how large are they?
  Does the server just have 1 database, but it’s 15TB in size? Or do you have 800 databases, one per customer? And of those 800, what’s the individual size distribution? Maybe 700 databases are smaller than 100GB, another 75 databases are between 100GB and 500GB, and the last 25 databases are +500GB?
• Exclusivity during backup maintenance window?
  Do you have the luxury of no workload running during your maintenance window? Or do you also have to juggle index maintenance, DBCC CHECKDB, or other application processes like ETLs, nightly processing jobs, etc.? Or to put it another way, do you have free reign to max out your SQL Server resources during your window?

Assumptions

• 1 SQL Server with many databases
• CPU = 12 cores; RAM = 256GB
• Backup storage capacity and storage ingest throughput are not a concern (you bought “orange”)
• Will ignore RPO/RTO business priorities (which I might tackle in a future blog)

Start Doing Math

Now that you know how much time you have and how many/how much you need to back up, you need to start weighing your options. Do you have the luxury to run one single backup job that will back up your databases one after another? Or do you need to split your databases across multiple backup jobs that run in parallel?

If server resources were infinite, one could theoretically kick off an individual backup job for each and every database, all starting at the exact same time. But of course, that makes no sense.

Revisiting What We’ve Learned

We now know that if our database’s data file(s) all reside on a single volume, we’ll only ever get one Reader Thread. So with a 12 core server, if I have full usage of the server’s resources, I may start with 8 backup output files to get 8 Writer Threads. And I might choose to use a large BUFFERCOUNT value and larger MAXTRANSFERSIZE value, which will result in more RAM consumption.

Now let’s pretend that to meet our requirements, we need to run backups in parallel. You might estimate that you need to run 4 backup jobs simultaneously. If you use the above parameters, you’ll may now start overrunning your CPU! Remember it’s not always about 100% CPU utilization either… all cores could be utilized at say 30%, but you could be context switching like crazy.

Who Doesn’t Love a Trace Flag?

So of course the above means you need to test different permutations. But when you’re doing testing, how can you determine the actual resource utilization of a given backup job? This is where some infrequently highlighted trace flags come into play.

You’ll see the above three Trace Flag are suffixed with least, more, and all. That’s because the output that each Trace Flag yields seems to have some overlap. And what’s more challenging is that I’ve found inconsistent documentation in older blogs that cover these Trace Flags as well. So I just opt to use them all to cover all of my bases.

When turned on, you will receive a ton of diagnostic information after running a backup operation. Here’s an example (with prefix removed for brevity):

I’ve grouped together interesting “sub-operations” like when the tail of the log backup is taken. Note the multiple size estimates that are taken throughout as well. Note that there are 4 data files, which is why you see 4 FID entries each time. But also note that all 4 data files are on 1 single volume!

Key TF Output Highlights

Let’s compare and contrast this to a backup operation with multiple backup output targets (this different database’s data files are spread across 4 different data volumes).

I think there’s a lot of really cool data in here. Remember that you can use Trace Flag 3605 to log this information to the Error Log. Thusly, you could use that to record what your BACKUP jobs are currently doing and review them later at your leisure (just remember it turn the TF off, lest you bloat your logs).

If you want to see more examples of these Trace Flags in action, visit my GitHub and check out my demo scripts from the original presentation. I introduce the Trace Flags in the 1_Baselining.sql script, then leverage them throughout the other demo scripts.

Workers vs CPU Cores

One more thing to keep in mind are the total number of worker threads you have available on your SQL Server. That’s different than the number of CPU cores you happen to have. I’m not going to dive deeper into this one today but will leave this old but still valid blog from Bob Dorr for reference instead: “How It Works: How many databases can be backed up simultaneously?“

Bringing It All Together

Hopefully now, with the above information, you can see that you really need to do an analysis and essentially create a matrix, based on your requirements and available resources. This will help you determine how much you want to crank up your database tuneables to best accelerate your FULL backups for your environment.

Hope You’ve Enjoyed This Series

This’ll most likely be the final blog of this series, though I might do an “addendum” with random tidbits. Let me know in the comments if there’s anything else you’d like me to explore in an epilogue.

Thanks for reading!

Summary

This blog series is a companion piece for my How to Accelerate Your Database Backups presentation. You can find a full recording of that presentation on MSSQLTips.com.

Table of Contents

• Part 1: Introduction
  https://sqlbek.wordpress.com/2023/07/18/sql-server-backup-internals-part-1/
  
• Part 2: Backup Buffers
  https://sqlbek.wordpress.com/2023/07/26/backup-internals-part-2-backup-buffers/
  
• Part 3: CPU Threads
  https://sqlbek.wordpress.com/2023/08/24/backup-internals-part-3-cpu-threads/
  
• Part 4: Backup Compression
  https://sqlbek.wordpress.com/2023/10/09/backup-internals-part-4-backup-compression/
  
• Part 5: Balancing Performance
  https://sqlbek.wordpress.com/2023/12/01/backup-internals-part-5-balancing-performance/

Welcome to another edition of T-SQL Tuesday! This month’s blog party post is hosted by Matthew McGiffen. He asks bloggers to write about anything related to SQL Server Encryption and Data Protection.

Security Theater

I’ll start this blog off by saying that I despise “Security Theater. ”

If you’re not familiar with the term, it “is the practice of taking security measures that are considered to provide the feeling of improved security while doing little or nothing to achieve it.” Essentially it is doing something simply for the show of it, to make people feel better, while providing little to no actual security value.

And for us data professionals, especially on the operations side, security and data protection are topics that should be top of mind for all of us.

Transparent Data Encryption – Not a Fan

Of all of the various data protection options available to us in SQL Server, I argue that Transparent Data Encryption (aka TDE) is worthless Security Theater.

TDE encrypts your data files “at rest.” This means one cannot crack open a hex editor and just start reading your raw data. And did you know that you can use a hex editor and start reading string values right out of your MDF files? Go search on that – that’s a separate tangent you can go explore.

So Andy, what’s wrong with that?

I feel that TDE is worthless because it is meant to protect against relatively narrow attack vectors. One attack vector TDE is meant to address is if someone physically walks into your data center, grabs a hard drive, and walks out. Because your data files are encrypted-at-rest, they cannot then go home, hook up said hard drive to a PC, and read your data off of it.

But who stores their SQL Server data on singular spinning hard drives anymore? Okay, there may be some people that still use Direct Attached Storage (DAS) instead of a SAN, but those are few and far between. With a SAN, depending on how your data is laid out, it’ll most likely be distributed across a bunch of physical storage media. So you’d have to waltz out with an entire SAN chassis, as a singular drive would only contain fragments. And if people are waltzing out of your data centers with entire SANs, you have a much bigger problem already.

Fine, but Andy, if an intruder gets access to my Windows machine and copies the data files elsewhere, that’s still a problem that TDE solves, right?

Not so fast… if someone has access to your Windows OS, you’re already screwed. Why? Because then an attacker than very easily exfiltrate your Service Master Key from SQL Server. Once they have that, then can then use that key plus your copied data files and decrypt/open those data files on another SQL Server at their leisure.

I’m not going to link to the piece, but there is a piece out there that showcases all of this, with the exact script one needs. It’s really disgustingly easy.

And I haven’t even noted the trade-offs of TDE, like performance penalties and backup compression consequences.

Not a Sponsored Message – What about Pure?

I should note that all of the above is just my personal opinion. Yes I happen to work for a storage company now and TDE is especially pointless in my world. Why? Because Pure Storage ALREADY encrypts-at-rest all data that is written to our Direct Flash Modules!

The other reason why I dislike TDE, from a Pure Storage context, is that it also inhibits our storage array’s data reduction capabilities. If you’re writing encrypted randomized data down to a data-reducing array like FlashArray, there’s little that can be done with randomized data to data reduce or compress it – simple as that. So if your primary storage is a data reducing array like a FlashArray, you’ve just eliminated one of your primary cost savings… all for nothing.

What Should You Do Instead?

If you really need to have encrypted data, encrypt it WITHIN the application (like within SQL Server). Store your PII using column-level encryption methods. That has actual security value.

And if you are really concerned about security, you need to do a true threat assessment and figure out what attack vectors you want to protect from. Because after all, if someone is already on your Windows OS or physically in your data center, you’ve already failed hard at security and TDE isn’t going to save your butt.

Thanks for reading.

Welcome back to Part 4 of my Backup Internals series. Today, I’d like to spend a little time exploring backup compression.

When you take a regular FULL BACKUP, SQL Server is literally taking a byte-for-byte copy of your data files. Don’t believe me? Then go read this, then come back. Additionally, Microsoft architected BACKUP operations such that the resource utilization and impact would be minimal (when using default parameters).

Blog Series Table of Contents

Back in the Day

Back in the days of my youth, storage cost a heck of a lot more per megabyte (hahaha). And backups were extra copies of your data that had to live somewhere. So who would not want their backups to consume less (expensive) storage? But alas, Microsoft did not offer built-in BACKUP compression with SQL Server in its earlier days. Instead, data professionals had to resort to 3rd party products like Litespeed to have compressed backups. Investing in a software solution often outweighed the extra storage costs of an uncompressed backup, so it was a sensible expenditure. Unfortunately for backup software vendors, Microsoft introduced native backup compression in 2008.

Costs of Backup Compression

Regardless of how one does compression, it comes at a price. Compression reads and manipulates ALL OF YOUR DATA to make it smaller, which requires CPU and memory resources. The benefit is that SQL Server now has less data to send over the wire to your backup target, reducing required storage and (usually) overall execution time. And this (usually) made both DBAs and storage admins happy.

In our last two blog posts, we explored a BACKUP operation’s RAM and CPU worker thread utilization. But what happens when you turn on compression?

The answer for RAM and backup buffers is easy – compression requires 3x whatever backup buffers you were using already. So if a regular BACKUP operation needed 10MB of RAM for backup buffers, turning on compression will now use 30MB of RAM. The buffers themselves do not get larger, rather the number of “Sets of Buffers” goes from 1 to 3.

Digging Deeper: Compression and CPU Utilization

When utilizing compression, most of us know full well that SQL Server burns a lot more CPU. But how much CPU? Does it just burn more cycles on the same Reader & Writer threads that were invoked as normal? Or does it invoke additional threads to do compression work?

To dig a bit deeper, I turned to my good friend Sysinternals’ Process Explorer. If you’re not familiar with it, it’s essentially Task Explorer on steroids. One awesome thing you can do with it is right click a given process/.exe, go to Properties, and see Thread information!

Using this, I can at least see the number of threads that are actively using CPU at any given time. Here’s a baseline shot showing an otherwise idle SQL Server

Note that there are threads “using” CPU, but they’re all marked “< 0.01”, so for purposes of this simple analysis, we’ll consider them idle.

Next, this is what the output looks like when I execute a BACKUP operation, whose files are on a single volume, against a single backup target, with no compression:

Note that there are two threads (ThreadID or TID): 10636 & 5176, showing CPU utilization values that are not “< 0.01”. That’s indicative of my 1 Reader thread and 1 Writer threads for my BACKUP operation.

Now as a different control, I executed a BACKUP whose data files are on 4 separate volumes, against 1 backup target, with no compression.

Note here that I see activity on 5 threads: 4 reader threads and 1 writer thread.

Finally, here’s a BACKUP run with a single volume, single backup target, but with compression turned on.

That’s a heck of a bunch of threads that are not idle – over 20 in this screenshot!

How Many CPU Threads Does Compression Need?

I’ll come right out and say it; I’m not certain. Here’s the thing about the above observation methodology. It updates once per second and while it’s insightful, it’s not bulletproof.

For one, I cannot confirm with absolute certainty, that the given threads are related to the BACKUP operation. In fact, for my single volume, single backup target control, I’d occasionally get a sampling that would have a 3rd thread pop up with some activity, but only for a single snapshot. In multi-volume and/or multi-backup target examples, the number of threads active at any one time would occasionally drop below what I would expect. I attribute that to a CPU thread having to wait for a resource at that precise moment in time.

If I wanted to get deeper, I suspect I’d have to go attach a debugger to SQL Server and go digging to find the actual discrete calls that are invoking additional worker threads. But for the purposes of this blog, I’m content with the above observation that when using compression, many more CPU threads get spun up.

Compression: Yay or Nay?

As we’ve seen, Backup Compression requires more resources – CPU and memory. And unless someone else is willing to fill in the gaps, I cannot tell you the algorithm/methodology to better predict CPU thread utilization when it comes to utilizing Compression. But I would also argue that one should not let that deter you either.

In the end, everything is all about trade-offs… which is what Part 5 of this series will be all about – performance trade offs of using all of these different backup parameters.

Thanks for reading!

Welcome to Part 2 of SQL Server Backup Internals. This blog series is a companion piece to my How to Accelerate Your Database Backups for MSSQLTips.com. In Part 1 of this blog series, I introduced the parts of a BACKUP operation.

Blog Series Tables of Contents

Now let’s start focusing on performance characteristics. We can impact the performance of a BACKUP operation by making changes to or more of the following:

• Increase the Number of Backup Buffers
• Increase the Size of Backup Buffers
• Add more CPU Reader Threads
• Add more CPU Writer Thread

Analogy Time!

I like to explain things with silly analogies and today will be no exception. Today, let’s pretend that we are in a shipping warehouse.

The person on the left “reads” the contents of the main container and makes copies of each widget, tossing as many widgets as they can into an available yellow basket. When the basket is full, it is sent to the other employee, who takes the contents of each basket and places it into the truck. And they also return the basket back to the first employee to be refilled.

In this silly analogy, the left employee represents a single CPU Reader Thread. They are copying data pages out of a database file and placing them into a yellow basket, which represents a backup buffer. The stack of empty yellow baskets represents the other backup buffers waiting to be filled in the Free Queue. Once a basket is filled, it is sent over to the Data Queue, which the other employee picks up one by one. That employee takes the contents of the basket and loads it into the truck. This is analogous to a CPU Writer Thread consuming a backup buffer and writing out to the backup target.

Note: The duration of transferring backup buffers from one queue to another is effectively nil. “Transfer” is probably not the best word choice here as I don’t believe any actual data movement occurs.

Backup Buffers

To help accelerate our backup operations, we can make two adjustments to our backup buffers – adding more backup buffers and increasing the size of the backup buffers.

Add More Baskets

First, let’s explore adding additional backup buffers. That’d be like having more than seven baskets to pass blocks back and forth in the diagram above. In a perfect system, both employees would be processing baskets at perfectly equal speeds. But the real world isn’t perfect – one employee may be much faster than the other.

Scenario 1

Let’s pretend the first employee is the slower one. They can fill a basket at a rate of 1 basket every 10 seconds. The second employee can process a basket at a rate of 1 every 5 seconds. So when they begin work, the second employee is sitting idle waiting for a basket to arrive. It is able to process the received basket quickly, pass it back, then sits and waits for the first employee to complete filling another basket.

Scenario 2

Conversely, if the first employee can fill a basket at a rate of 1 per second, they’ll have sent all 7 available baskets to the other employee after 7 seconds. The second employee still takes 5 seconds to process a filled basket, but the first employee will have to wait 4 seconds between receiving free baskets after they get going. The second employee now has a backlog of baskets to process because it is much slower than the first employee.

Check out the time lapse chart. One thing that is very clear is that due to the different processing rates, either the Reader or Writer will incur some idle time. Imagine what this might look like if the number of buckets was larger or smaller? And imagine what this might look like if the processing rates were orders of magnitude different?

Use Bigger Baskets

The other adjustment that can be made is to make each basket bigger. Depending on the rate of filling or processing a basket, making all baskets larger can increase overall throughput of the process. If we doubled the size of the basket, the fill rate and process rate would still increase proportionally. So in the first scenario above, the fill rate would be 1 basket every 20 seconds and the processing rate would be 1 basket every 10 seconds. And the second scenario, we’d see a fill rate of 1 basket every 2 seconds and a processing rate of 1 basket every 10 seconds.

Are you seeing a bit of a “so what’s the benefit, why bother?” conundrum here? Hold that thought for a bit.

What Are the T-SQL Parameters?

Skipped over the analogy to the good stuff? Here you go:

• BUFFERCOUNT (BC) – Define the number of Backup Buffers to be used
• MAXTRANSFERSIZE (MTS) – Define the size of each Backup Buffer

MAXTRANSFERSIZE can range anywhere from 64KB to 4MB. Frankly, I’m not sure what the allowed range of values is for BUFFERCOUNT. But because of the workflow above, there is a point where having too many Backup Buffers are a waste, because neither side can fill or process them fast enough.

Additionally, there’s a resource price to pay in the form of RAM consumed. Very simply, your BACKUP operation will consume BUFFERCOUNT * MAXTRANSFERSIZE in RAM. So if you run a BACKUP with BUFFERCOUNT = 10 and MAXTRANSFERSIZE = 1MB, your operation will consume 10MB of RAM. But if you crank things up, BUFFERCOUNT = 100 and MAXTRANSFERSIZE = 4MB, that’s now 400MB of RAM for your operation.

What about the existing SQL Server Buffer Pool? Backup Buffers are allocated outside of buffer pool memory. If you don’t have enough RAM available, SQL Server will shrink the buffer pool as needed to accommodate your backup buffer requirement. (I plan to talk about overall resource balancing in a future part of this series.)

What’s the Benefit?

So what’s the overall benefit of messing with these two parameters. If one is solely leveraging these two parameters and no other adjustments, not much really. The main bottleneck your BACKUP operation will experience is due to only having 1 Reader and 1 Writer thread. So adding a few more Backup Buffers can help your overall performance a bit, but as you saw with the time lapse examples, if you have different processing rates on one side or the other, the extra backup buffers don’t really help – they’ll just sit in one of the queues waiting on the CPU threads.

It is far more beneficial to make adjustments to BUFFERCOUNT and MAXTRANSFERSIZE if you also make adjustments to the number of Reader and/or Writer threads for your BACKUP operation. And that’s what I’ll be covering in Part 3 of this series, so stay tuned! Thanks for reading!