SQL server performance: MAC Pro v. MacBook Pro

Question

Level 1

6 points

SQL server performance: MAC Pro v. MacBook Pro

Morning Folks

this is driving me nuts. I have a macbook pro and a mac pro both running windows 10. The macbook pro has 4 2.8ghz cores, 16gb ram and a 1tb ssd, the mac pro has 8 3ghz cores, 64gb ram and 1tb ssd. Both are running on boot camp and ive gone through all the performance related settings and made sure they are the same. Neither is connected to a network and neither is running anti-virus etc. I.e. they are both completely isolated. Both are fully patched etc. and the Mac Pro is, in all other cases, blindingly faster than the laptop. i even compared disk write speeds by copying an 11gb text file, the laptop too twice as long as the desktop.

the thing thats not making any sense is the laptop is considerably faster running SQL Sever. For example, a 2.5 million record bulk insert in sql server 2014 takes 3 seconds on the laptop and 6 on the desktop. Ive checked the sql settings on both and they are identical at database and server level, both use simple recovery mode etc. The speed difference of each step in a simple bulk insert, validate and aggregate process is proportional to the overall speed difference of the entire process.

Has anyone seen this behaviour before, or have any idea where to look?

Thanks

Mark

iPhone 4, iOS 5

Posted on Dec 18, 2017 2:20 AM

Reply

Answer 1

Loner T

Level 9

59,471 points

Dec 18, 2017 5:14 AM in response to goClimb

Bulk Insert is a sequential read/write operation. It is not a very good test of overall performance. Are you planning to use any operation other than a bulk insert in your SQL server installation?

What is the year/model of the MBP?

Reply

Answer 2

goClimb Author

Level 1

6 points

Dec 18, 2017 5:30 AM in response to Loner T

Hi Loner T

Thanks for the reply...

The Laptop is about two years old, the Mac Pro about three. Both are running Windows at the moment and I’m in the middle of something so don’t really want to reboot to get more specific information.

the bulk insert is only one part of the process, the next step is equally slower (I.e. about a dozen validations for data type and the existence of reference data values in dimension tables). Validation errors are written to table, the data is moved to a final table and then aggregated.

Processing the above steps with 2.5m records takes 35 seconds on the laptop and almost a minute on the desktop. The validation step takes 17s on the laptop and 29s on the desktop.

cheers

Reply

Answer 3

Loner T

Level 9

59,471 points

Dec 18, 2017 5:52 AM in response to goClimb

NVMe and SATA SSDs have difference capabilities and sequential read/write speeds. Your operations are disk i/o bound. You can try BlackMagic disk speed tests and check if that shows you any significant differences. The MP will be slower than a 2016/2017 MBP vis-a-vis disk i/o.

Reply

Answer 4

goClimb Author

Level 1

6 points

Dec 18, 2017 6:06 AM in response to Loner T

Thanks again.... The MacBook was the last of the previous model, before the touch strip, so it’s not actually faster. I‘ve already done some simple disk speed test in OS X and Windows, I.e. a combination of simply large file copies as well as testing (in OS X) the time it takes to output a complex Logic Pro file to mp3 etc., even video editing. Everything is faster on the Mac Pro except running sql server in Windows. I’ve even about to write some bad vb string building code (I.e. string=string+string in a loop) and some file system object stuff to get another perspective... neither of these machines get connected to the net and don’t Generally install software from third parties...

Reply

Answer 5

Loner T

Level 9

59,471 points

Dec 18, 2017 2:35 PM in response to goClimb

Can you also post the output of

diskutil info /

or the appropriate disk(s), if the SQL Server and source files are on a different disk. Device block size will make a difference to I/O speed and throughput.

Reply

Answer 6

Loner T

Level 9

59,471 points

Dec 18, 2017 1:45 PM in response to goClimb

From both Macs, can you post the System Report that shows the disk connections?

Reply

Answer 7

goClimb Author

Level 1

6 points

Dec 18, 2017 2:09 PM in response to Loner T

Will do... I’ve reinstalled all the software, will set up the test dB and get proper system detail in the morning.

Cheers

Reply

Answer 8

goClimb Author

Level 1

6 points

Dec 18, 2017 2:40 PM in response to Loner T

Both installs are the same, everything on the SSD. Pretty sure the drives are very different though. Might get back on it in a bit. It’s bugging me...

Reply