The main gist of this article is that if you are starting any project where you want to both teach a subject or benchmark algorithms, the cleaner the machine you start with, the less competition you have from other software skewing the results. The only thing I installed on my new computer was SSMS and Dropbox. For Dropbox I selectively synced my project directory only to an external hard drive to make sharing projects and backups easier. Other than that, the box will be basically clean of software that a server would not have. It is running Windows 11 Pro as the operating system. (When doing heavy testing, I will turn off Dropbox syncing too).
Clearly this is the step most of us want to do for every project we do, but it can be hard to do. Clearing off a computer you own can be tough. And using a VM is a great solution, but can be kind of pricey if you start to process lots and lots of data (and it is hard to resist the temptation of cranking it up to super-computer level.
For this project I decided to pick up a new machine to use, basing my decision on the following 3 criteria to select.
- Give me pretty solid, repeatable test results with the software I am testing (SQL Server 2019 and 2022)
- Is useful when the book has been written
- It doesn’t cost more than I am making for the book (this was by far the hardest of the parameters!)
My old NUC computer is 3 years old, so I figured it was time to upgrade. I also decided that this time, I would work hard to have a more “real” configuration and use one computer to edit documents (a Mac Mini), one for running SSMS and SSIS (the old NUC) and one computer configured as a server just running SQL Server (a new computer).
I searched long and hard and found 2 kinds of computers that interested me. Refurbished Xeon based workstation-powerhouses (some with 24 cores), and a newer version of the NUC. The Xeon based machines fit #1 better than the NUC in many ways. Obviously a 24 core computer would be awesome, but what turned me off was the power consumption a machine like this would likely use compared to a more consumer based computer. That plus getting a lot of RAM and SSD disk drives was more complicated pushing me close to, and often, over my budget.
So I looked at regular desktop computers. Most of the newer machines were over my budget. I briefly considered building a new computer, but I am a relational programmer for many reasons, and my not exactly strong hardware configuration ability is one of them. So I checked into newer NUC machines.
The NUC devices are classified as gaming computers, and the one I ended up finding has solid, if perhaps old, specs for IO, with 2 1TB Pci-based SSDs, and 64GB of RAM. Its downside is that their CPUs are mobile versions, but looking at the Geekbench score, it scored a 1136 single core, and 6304 multi-core (Using Geekbench downloaded from here: https://www.geekbench.com/download/windows/). (The M1 Mac chip actually scored better, but I am running SQL Server here and getting large amounts of RAM and disk on a Mac is really not cheap.)
I didn’t go with the most recent set of NUC devices that are shipping because of condition #3. I ended up finding a NUC9i7QNX NUC 9 Ghost Skull Canyon Extreme machine, with an 6 core i7-9750H, with the aforementioned added memory and disk (the NUC devices come basically empty from Intel).
My old NUC is to the right. I like this new size because it has large fans built into the box, and my previous device had some overheating issues when I worked it too hard… which I did.
Looking at the list of Geekbench scores here: https://browser.geekbench.com/processor-benchmarks, it is not nearly at the top, but still is solid for a computer that will likely one day be my computer I use for holding copies of my theme park pictures and serving up video files in Plex.
I used Crystal Disk Mark (https://crystalmark.info/en/download/#CrystalDiskMark) and ran some tests. The disks are Kingston SNVS1000G drives. Not the best NVMe drives, but can read and write pretty fast. If I find that some things wont work well enough, I may consider changing out the drives.
On the pro side, these specs are more than twice than that of my previous NUC, which has a max read throughput of around 800 MB/S, and a Geekbench score of 712/2930 and I was able to do quite a bit on that machine that also served as my daily computer the last time I was doing benchmarks. In fact, I set out to buy a machine with a lot of free disk and a lot of RAM because the last time I was working on graphs in SQL Server, I hit “limitations” when trying to process a very large graph (I loaded all of IMDB into a database and tried to do 7 degrees of Kevin Bacon..) and hopefully I will get better results this time.
Looking at Glenn Berry’s numbers here for some of his tests: https://glennsqlperformance.com/2020/12/13/some-quick-comparative-crystaldiskmark-results-in-2020/ I give this machine a “meh plus” in power, but in most of the tests I will be doing, comparing one algorithm to another is more important than if I can crunch millions of rows per second for a very long time (the faster the better in any case, but principle #3 is pretty important!)
Next step, installing SQL Server, SSMS, Visual Studio (for SSIS), configuring everything and making sure my Red-Gate tools are up to date! Then see if the