2

I wrote a fairly simple script that creates in a periodic interval a backup of our DBs, switching automatically into differential in case a full backup already exists.

This runs great on my system. A 4.5 GB database takes around 10-20 seconds to perform the first full backup. However, testing the same DB on another system took over 8 minutes to complete with the same operation. I was expecting slightly worse performance since that system is not as beefy as mine, but not such a massive difference.

  • Both are running SQL Server 2014.

  • My system is a Ryzen 5800x (8c/16t), 32GB of RAM and only has M.2 drives with a peak 4GB/s write speed.

  • The other system is an Intel i5 4460 (4c/4t), 8GB of RAM and uses an SSD with peak 500MB/s write speed.

As you can see it's fairly justifiable that the Intel rig is going to be slower than the AMD rig based on the specifications, but that much slower?

What are the hardware specifications I should care about the most when it comes to optimizing speed for such operations?

I tried finding a viable source online that tackles it, but wasn't successful so I'm asking what you people think.

Here is the query I am currently using on both systems:

BACKUP DATABASE [DBname]  
    TO  DISK = N'C:\tmp\DBname.bak' 
    WITH 
        NOFORMAT, 
        NOINIT, 
        NAME = N'DBname-Full Database Backup', 
        SKIP, 
        NOREWIND, 
        NOUNLOAD, 
        STATS = 10;
2
  • Were both runs on a warm cache? If not then the amount of time spent reading from disk will be significant. Were they both writing to the same disk as reading? If so, and the database does not fit in cache, then again time will be increased. Probably what is happening is that the other system cannot fit it into cache, so it's reading and writing to the same disk Nov 17, 2021 at 14:36
  • try backing up to a nul device to get a general sense of total backup throughput and to see if disk is your limiting factor backup database [dbname] to disk = N'NUL' with stats=10
    – swasheck
    Nov 17, 2021 at 23:13

3 Answers 3

2

My hypothesis is that memory is the limiting factor.

On your 32 GB system, the entire database and the backup file fit in memory at the same time, along with a normal SQL Server buffer allocation, so the read and write operations can be sequential.

On your 8GB system, SQL Server must read and write in chunks (the backup file is not a simple copy of the database file), always leaving some buffers free for other tasks.

To test this, on your 32 GB system, set SQL Server's max memory to 8192 MB, reconfigure, and retry the backup.

sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'max server memory', 8192;
go
reconfigure;

GO

2
  • Turns out this was actually the reason, had some sticks laying around, slapped them into the slower rig and it wasn't as slow anymore. Nov 24, 2021 at 13:10
  • @StevenBorges When I saw 8GB, my first reaction was that it was a bit tight on memory, having had the same situation myself.
    – grahamj42
    Nov 24, 2021 at 15:20
4

Looking at the bare numbers and assuming that the Actual Speed is calculated by taking the DB Size divided by Disk Speed, then we receive the following information:

 System  | DB Size | Disk Speed | Duration | Actual Speed 
---------+---------+------------+----------+-------------- 
 AMD     |  4.5 GB |  4000 MB/s |     20 s |     800 MB/s
 Intel   |  4.5 GB |   500 MB/s |    480 s |       2 MB/s

So even though both systems are writing data to disk, the are not using the available disk throughput on the system.

The AMD system is running at 1/5th of the available throughput.
The Intel system is running at 1/50th of the available throughput.

Assuming that the numbers you provided about the Disk (Write?) Speed are correct, then you have an underlying issue on the Intel system.

Questions to ask yourself:

  1. Is the Intel system used solely for that one database?
  2. Is there some disk caching mechanism that is not allowing SQL Server to write to disk?
  3. Are there any other parties (programs, antivirus, ...) that could be restricting the write speed on the disk.
  4. Are the disk locally attached or SAN?
  5. If SAN: How are the SAN disk attached? Fibre, FoE, ...
  6. If SAN: What is the connection speed of the fibre channels.
  7. Are the disks on the Intel system running without any S.M.A.R.T. issues?
    • Bad sectors
    • other S.M.A.R.T. errors?
    • large amount of re-writes due to corrupt blocks
  8. Other issues

Looking at the bare numbers of throughput, I would guess that the issue is related to:

  • SQL Server instance running on central server/desktop with other resource intensive application(s) running:
    • Server:
      • Active Directory
      • Exchange
    • Desktop:
      • Rendering Software
      • Multiple DBMS systems
  • Antivirus Software
  • If SAN: slow SAN connection
  • If local disks: Disk drives at EOL (End-of-life).

I can only provide you with a general idea on how to analyse your system, but you have to adapt these to your system to determine what or where the underlying issue is.

Good luck.

3

Have you repeatedly tested the other system and the results were always in the ~8 minute ballpark?...as other concurrently active queries and operations could be utilizing the allocated hardware as well.

My understanding is Disk is most important and then the CPU (as it compresses the backup), but what's provisioned for the other system seems fine.

You may want to verify it's all reasonably allocated to your SQL Server instance though and not being limited either at the server level, the instance settings, or via a Resource Pool.

You may want to also look into things that could be bottlenecking your backups write speeds, such as if you're backing up to a network location (then network bandwidth and latency come into play). You should also run some speed tests to verify that the Disk is reaching the speeds you expect and there's not some other root issue of your server. My StackOverflow question on slow backup issues may provide some ideas on things to check and how to test the Disk speeds on your other machine.

Some of the things worth looking into from that question:

  1. Brent Ozar Article on DiskSpd: Useful to measure your machine's Disk's actual speeds.
  2. Adam Mechanic's sp_whoisactive: Helpful for monitoring the progress of the backup, and what the common Last Wait Types are. The Wait Types might be indicative of your problem.
7
  • Yes the ballpark was always around the 8 minute clock, that system was basically Idle, no queries were performed and no other resource heavy application was running in the background. In comparison mine was running several VMs and instances of Visual Studio lol Nov 17, 2021 at 12:53
  • My recommendation then would be to verify all of those resources are properly allocated to your SQL Server instance on that machine then. SSDs are rather quick themselves, and the peak write speeds between the disk you're using and the other system's indicate that it shouldn't be 24x slower, rather I'd expect it to be maybe up to 10x slower aka only a couple of minutes tops.
    – J.D.
    Nov 17, 2021 at 12:59
  • @StevenBorges Also, please see my last update to my answer with some additional considerations.
    – J.D.
    Nov 17, 2021 at 13:04
  • 1
    thanks for all the input, I'll check it all out, there should be another system around the office with similar specs I could test this out, the installs on all systems should be the same since we install SQL Server 2014 using a custom routine I wrote, so it """""should""""" have the same configuration throughout all rigs. Nov 17, 2021 at 13:07
  • @StevenBorges Np, sounds like a good idea. Gathering other benchmarks for comparison should be helpful. I also just linked two resources (which are also in my mentioned StackOverflow question) worth looking into too.
    – J.D.
    Nov 17, 2021 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.