Help me spec out a replacement PC that absolutely *flies*

Build-Troys-PC.md

Time is money, and my 5+ year old desktop is costing me a heap of it right now. The final straw has come when processing several terabytes of stealer logs which has taken forever. Meanwhile, Stefan has been flying through them with a massive NVMe drive on a fast motherboard.

So, in no particular order, here's what I need it to do:

1. Read and write multi-terabyte files fast
2. Run SQL Server locally for both development and querying of large data sets (the latter is especially memory intensive)
3. Dev environment is largely Visual Studio, SSMS and other (less intensive) tools
4. Run a gazillion simultaneous Chrome tabs 😛

And here's my current thinking:

1. SSDs (Samsung 9100 PRO?):

• Fast OS drive big enough for Win 11 plus apps
• The biggest possible drive for processing the sorts of files described in the intro
• I'll probably drop an existing 10TB mechanical drive in, purely for storage

2. RAM:

• As much as feasible without ridiculous costs (a lot of the data processing is done in-memory)
• Probably don't need pricier ECC memory

3. Processor

• I've had Intel but am open to change (Threadripper seems to have got a lot of love lately)

5. GPU

• Needs to drive two 2560x1440 screens plus one 5120x1440
• This isn't going to be used for gaming or hash cracking

And before you ask:

1. Yes, it will run Windows, not Mac OS or Linux
2. No, pushing all this to "the cloud" is not feasible

Suggestions, comments, questions and all else welcome, thanks everyone!

Categorically both reasonable as powerful workstation machines.

Obviously 1TB is unreasonable for anything else than OS and a few programs. The motherboard has "only" 3 M.2 slots, so if you accept it as it is, you'd be limited to 1+4+4 or 1+8+8TB for now, but the 8TB Samsung 9100 are almost 900 euro each. The motherboard has 4 SATA slots though so you can still get idk 2x 16~26TB slow storage for cheap and/or 8-16TB SSDs for painful amount of money.

As mentioned before, I'd rather spend less on GPU to offset the RAM and NVMe costs - again, the big question is if you do video editing, ML/LLM tasks like audio/video denoise/upscaling. If you don't need to edit 6k/8k footage, or multicam 4k footage, 8GB VRAM is plenty. If you're a tab hoarder, Firefox/Chrome/etc can easily eat up 4-7GB VRAM, esp. with a lot of multimedia tabs (youtube), but that's easily fixable with app restart / closing tabs, and never a real bottleneck (i.e. not the way swapping was in Win95 era). (If you do plan on using LLM or 6k video editing, then the opposite applies - I'd aim for 16GB VRAM or more).

In the old days I'd say "buy 256GB RAM now and buy more later" but sadly that doesn't apply anymore, both because of the aforementioned fiddly DDR5 that doesn't let you just combine random series of DDR sticks, even from the same manufacturer, but also because the current AI bubble means RAM prices might be going up, or at least not go down with the regularity we were used to before DDR5.

Again, not a MSSQL DBA, but yeah, being able to have more RAM can make a dramatic difference with queries, depending on the dataset and query. If you know you'll need to intensely query more than ~200GB on a daily basis, count up the time it will take / time it will save you as a human being. Even with 256GB, the queries will likely be 10x faster than with your current old PC, some queries might be 1000x faster just from the fact that they were on HDD. If a previously 6 minute query takes 10 hypothetical seconds with 256GB RAM, it won't drastically improve your life to have them take 5s with 512GB RAM.

Again - depends on how you want to future proof it. As leaks get more common and more serious, having more RAM would likely help correlate different datasets, e.g. if you need to join several hundred-GB tables. More RAM will also allow you to create more indexes which will likely stay in RAM and (again, assuming MSSQL can do that) allow index-only reads, which again can speed up things several times.

One last thing I can think of is - regarding the fans in the second machine. First, although this is a minor point, if it's a small local company, it's possible they've never assembled a computer this powerful before but obv I don't think you went looking to some 1-person company. Still, they likely haven't sold a lot of them. Second, a lot of companies, even big ones like HP, Dell, Corsair - often either have no clue or just don't bother testing the flow of their computers. My point is, 11 fans isn't necessarily better than even 5. As long as you can open the case without voiding the warranty, this isn't an issue, but still is something to keep an eye on - maybe better to run a benchmark, then turn off some of the fans, run it again, compare CPU/GPU temperatures.

No opinion about the cases / CPU fan / PSU.

CPU differences - as I said before. Not many workloads will utilize many cores, unless you're going to run HIBP servers (or parts of it) from the machine. 32 is nicer than 24, but even 5 years from now you're not likely going to hit the limits - so again, depends on the cost difference and your workloads.

Either way, good luck! Whichever you get, it will be a genuinely dramatic speedup over the previous machine, and again, a lot of the understanding of how the different parts of the machine perform can be only discovered by actually running your workload on that particular machine.

Awesome feedback @analytik, thank you!

Kinda gives it away based on the list, but the second one is from Aftershock, who definitely know what they're doing with high-end machines.

I'll ask for some more GPU options, but otherwise I'm leaning towards the spec on that second image.

First: do checkout https://system76.com/desktops/thelio-major-r5-n3/configure

Don't forget there are PCIe NVMe adapters to add even more NVMe storage to a system than what the onboard M2s provide.

I'll advise to get a CPU with more cache and higher BASE speed than more cores... unless your workloads are massively parallelized.

Case: I prefer tool-less cases, and then depending whether you want more HDDs than SSDs the placement and airflow I do check too.

PSU: Gold/platinum and 50% more than current CPU, RAM, NVMe, HDD, GPU etc. and do check the 3V and 5V and 12V power requirements of each component

I do have a AIO water cooler loops, but they do have some challenges some time you need to compare/check w.r.t. orientation, but a decent setup you can have a whisper quiet system at high usage... okay, the GPU might be the airplane taking off :D

One thing to check with (especially the Intels) is the motherboard's NVMe and SATA controller which shares the same PCIe lanes, ie. you might loose some or all of the SATA ports when installing the NVMes, and then you might also need to check the NVMes which one are on the chipset and which are direct CPU connections and which PCIe slots are shared when you need more peripherals.

But do check out System76's offerings :)

These builds generally look good to me, and are gonna be smoking machines compared to anything from 5 years ago, much less your old mid-level box.

But... what @kltye and you said here:

...put them in RAID0 in Storage Spaces...

...The drive specs on the second are a bit light (the first one with 3x 4TB is a much better fit)...

Yeah. Second spec sounds real light on disk. This is a big ol' deal for your type of workload, esp. the 3x vs 1x disk count. Big files and SQL, which sounds like "analytic" workloads which do high volumes of largely sequential IO, and will likely often be IO bound, even if you have a lot of RAM. And I suspect the disk IO will really matter - with TB-scale files, you're not going to have enough RAM to cache all of that, so I bet you will be hitting the disk frequently and waiting on it. (At work, I do scientific programming and am a SQL Server DBA & developer.)

Having 3x SSDs instead of 1x doesn't just give you more disk space, it gives you faster disk. Because if you put them in RAID0 like @kltye says, that parallelizes your IO across the disks and they're all working on it at the same time, so their IO speeds are additive. (With a bunch of caveats, of course.) You could get 2x or 3x the IO speed. I think you'll want that. At the enterprise level - and you're kinda getting there - making SQL and storage go fast is all about the RAID and multiple "spindles".

The RAM still does matter of course, for reasons like @analytik discussed. Both SQL Server and the OS will be doing caching of file data. Plus the RAM is where all your analytic computation is happening, and is so much faster than disk, if you can keep your whole "working set" for a given task in RAM instead of spilling to disk, that can be a drastic performance difference. But RAM won't make disk IO speed irrelevant.

assuming MSSQL can do that

Yep. MSSQL does a bunch of caching, for both row data and indexes. It's a fundamental part of how it works. It'll cache these things dynamically based on their usage in an LRU-ish + stats + speculative manner. And you can also explicitly set particular things to be pinned in memory.

Filesystem allocation units (block size)

One thing to consider: when you format your disks, you might want to go with an allocation unit size larger than the NTFS default for the data disks. (Not the OS disk!) Can improve analytic workload performance. The default size is more suited for transactional workloads that skip around more.

Won't matter nearly as much as the actual hardware does, but can make some difference for basically no cost.

Running SQL Server

Dunno how experienced you are with SQL Server. Maybe you already know this, but...

Watch out when you're running SQL Server. Databases want RAM. So MSSQL, and SQL databases in general, often assume they basically have the computer dedicated to themself, and will go ahead and hoover up most of the RAM to use for their page cache, and hold on to it. Might want to configure it to limit how much it uses, even switching that around based on what you're doing on a given day.

You'll maybe want to enable data compression (row and maybe page) on your larger tables, and be mindful of the column datatypes and normalization design. That stuff can easily be a 2x to 10x speed difference for analytic workloads. And definitely check out the CLUSTERED COLUMNSTORE table organization, esp. on the most recent versions of MSSQL. Can be a huge win for time series style data.

Just for storage re. databases you could look into new old stock u.2/u.3 drives on eBay and use a controller in a Gen 5 PCI express 8x or 16x slot. That would give you a theoretical 31.5 to 63 GB/sec bandwidth, but what you get is of course dependent on the drives and any RAID config that you choose. It might be a fast and cost-effective way to add very fast storage though.

I agree with @apjanke his comment about SQL Server expecting to be the sole big process on a server. Just thinking out of the box, would it an idea to repurpose your current machine, possibly with upgraded storage and memory, as a dedicated SQL Server host? With fast networking, 10 Gbe is easy nowadays and 25/40 Gbe isn't that expensive anymore with NOS Mellanox nics, you can spread the load over two machines. In this case, the database would no longer have to share iops, memory and CPU capacity with all the other processes you're running in your workstation.

Spec now finalised and machine ordered! I'll share more (including benchmarks) once it arrives, here's the final build:

That final spec is one stonking box. I think you'll be happy with that.

Ten years ago, these specs would be pretty darn good for a departmental database server involving a rack mount and some IT guys to support it. (And a lot of it would have been slower.) The fact that you can now get this in a desktop is kind of insane.