April 19, 2018 at 2:07 pm
One of our users complained that when trying to backup/restore a database roughly a GB in size is taking a slight hit on server A which are compose of flash drives as compared to that of its counterpart server B where we have the same DB same size and all the specs remain the same but it is much faster there.Any thoughts on where should I look in to identify why is this behavior especially when server A are all flash. Also, what does MB/sec mean here...is this a network throughput or disk transfers/sec...what exactly does that indicate.
Examples of running these tests are as below
BACKUP DATABASE successfully processed 129490 pages in 14.957 seconds (67.636 MB/sec) -- server A
BACKUP DATABASE successfully processed 129490 pages in 7.019 seconds (144.127 MB/sec) -- server B
Server specs are as below
SQL Server 2012 sp4
RAM: 16 GB
CPU: Intel Xeon E5 2680 v2 @2.8 2.8 GHz (4 processors)
MAXDOP = 0
COP = 5
April 19, 2018 at 3:45 pm
Are they backing up to the same place?
Have you done write tests outside of SQL to confirm the disks are configured properly? Do they both have the right allocation, etc..? How are they configured? Local? SAN? etc.. So many variables needed
April 19, 2018 at 9:00 pm
taseedorf - Thursday, April 19, 2018 3:45 PMAre they backing up to the same place?
Have you done write tests outside of SQL to confirm the disks are configured properly? Do they both have the right allocation, etc..? How are they configured? Local? SAN? etc.. So many variables needed
Both of have their own separate disks so they are not being backed up at the same place...not sure if you understood my question.
April 20, 2018 at 6:45 am
Feivel - Thursday, April 19, 2018 2:07 PMOne of our users complained that when trying to backup/restore a database roughly a GB in size is taking a slight hit on server A which are compose of flash drives as compared to that of its counterpart server B where we have the same DB same size and all the specs remain the same but it is much faster there.Any thoughts on where should I look in to identify why is this behavior especially when server A are all flash. Also, what does MB/sec mean here...is this a network throughput or disk transfers/sec...what exactly does that indicate.Examples of running these tests are as below
BACKUP DATABASE successfully processed 129490 pages in 14.957 seconds (67.636 MB/sec) -- server A
BACKUP DATABASE successfully processed 129490 pages in 7.019 seconds (144.127 MB/sec) -- server BServer specs are as below
SQL Server 2012 sp4
RAM: 16 GB
CPU: Intel Xeon E5 2680 v2 @2.8 2.8 GHz (4 processors)
MAXDOP = 0
COP = 5
The data rate shown in the statistics you included is the rate at which the data made it into the backup file. The one unanswered question is exactly where the backup files are located as compared to the database files. Are they on separate physical drives within each server? Are they even local to the servers themselves? If not, what are the properties of the network each server must traverse to get to the file backup location for that server? is any portion of each of the two paths, shared in any way? Given that one of the backups was going twice as fast as the other says one of three things: 1) the drive speed for the database is faster on one server over the other, 2) the path to the backup location is considerably less busy for one server over the other, or 3) the drive speed for the backup location is faster on one server over the other. That's why that question is being asked. We haven't seen any kind of physical configuration picture that identifies exactly what the disk drive config is for each server, or even whether or not the backup location is local to each server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 20, 2018 at 7:25 am
Feivel - Thursday, April 19, 2018 2:07 PMOne of our users complained that when trying to backup/restore a database roughly a GB in size is taking a slight hit on server A which are compose of flash drives as compared to that of its counterpart server B where we have the same DB same size and all the specs remain the same but it is much faster there.Any thoughts on where should I look in to identify why is this behavior especially when server A are all flash. Also, what does MB/sec mean here...is this a network throughput or disk transfers/sec...what exactly does that indicate.Examples of running these tests are as below
BACKUP DATABASE successfully processed 129490 pages in 14.957 seconds (67.636 MB/sec) -- server A
BACKUP DATABASE successfully processed 129490 pages in 7.019 seconds (144.127 MB/sec) -- server BServer specs are as below
SQL Server 2012 sp4
RAM: 16 GB
CPU: Intel Xeon E5 2680 v2 @2.8 2.8 GHz (4 processors)
MAXDOP = 0
COP = 5
It could be anything between the server being backed up and the target server. Switches, routers, NICs, and the settings for each. Of course, it can also be workload on each of the target servers, as well and it could be how the "disk" (real or SSD) is configured.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2018 at 2:27 pm
sgmunson - Friday, April 20, 2018 6:45 AMFeivel - Thursday, April 19, 2018 2:07 PMOne of our users complained that when trying to backup/restore a database roughly a GB in size is taking a slight hit on server A which are compose of flash drives as compared to that of its counterpart server B where we have the same DB same size and all the specs remain the same but it is much faster there.Any thoughts on where should I look in to identify why is this behavior especially when server A are all flash. Also, what does MB/sec mean here...is this a network throughput or disk transfers/sec...what exactly does that indicate.Examples of running these tests are as below
BACKUP DATABASE successfully processed 129490 pages in 14.957 seconds (67.636 MB/sec) -- server A
BACKUP DATABASE successfully processed 129490 pages in 7.019 seconds (144.127 MB/sec) -- server BServer specs are as below
SQL Server 2012 sp4
RAM: 16 GB
CPU: Intel Xeon E5 2680 v2 @2.8 2.8 GHz (4 processors)
MAXDOP = 0
COP = 5The data rate shown in the statistics you included is the rate at which the data made it into the backup file. The one unanswered question is exactly where the backup files are located as compared to the database files. Are they on separate physical drives within each server? Are they even local to the servers themselves? If not, what are the properties of the network each server must traverse to get to the file backup location for that server? is any portion of each of the two paths, shared in any way? Given that one of the backups was going twice as fast as the other says one of three things: 1) the drive speed for the database is faster on one server over the other, 2) the path to the backup location is considerably less busy for one server over the other, or 3) the drive speed for the backup location is faster on one server over the other. That's why that question is being asked. We haven't seen any kind of physical configuration picture that identifies exactly what the disk drive config is for each server, or even whether or not the backup location is local to each server.
Backup is local to the server..not they are not shared..that is what I mentioned in my question server A is all flash storage and server B is all SAS drives but things are running much faster in B rather than in A. Are there any performance counters or benchmarks that I can run to figure out if it is actually the underlying IO subsystem that is the culprit.
April 20, 2018 at 2:41 pm
Jeff Moden - Friday, April 20, 2018 7:25 AMFeivel - Thursday, April 19, 2018 2:07 PMOne of our users complained that when trying to backup/restore a database roughly a GB in size is taking a slight hit on server A which are compose of flash drives as compared to that of its counterpart server B where we have the same DB same size and all the specs remain the same but it is much faster there.Any thoughts on where should I look in to identify why is this behavior especially when server A are all flash. Also, what does MB/sec mean here...is this a network throughput or disk transfers/sec...what exactly does that indicate.Examples of running these tests are as below
BACKUP DATABASE successfully processed 129490 pages in 14.957 seconds (67.636 MB/sec) -- server A
BACKUP DATABASE successfully processed 129490 pages in 7.019 seconds (144.127 MB/sec) -- server BServer specs are as below
SQL Server 2012 sp4
RAM: 16 GB
CPU: Intel Xeon E5 2680 v2 @2.8 2.8 GHz (4 processors)
MAXDOP = 0
COP = 5It could be anything between the server being backed up and the target server. Switches, routers, NICs, and the settings for each. Of course, it can also be workload on each of the target servers, as well and it could be how the "disk" (real or SSD) is configured.
Source and target servers are the same...we aren't crossing network boundaries. As per my question we are comparing backup and restore operations performed locally on that server. One is all flash and the other is all SAS. Again, backup and restores are all local to these servers. I am only comparing why as to when doing it on server A is taking twice as long when performing the same operation on server B.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply