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!

Whether you need a lot of cores depends on the license of your SQL Server install (SQL Server is licensed per core)

Just developer edition running locally so no license requirement.

First of all, identify your bottlenecks. You have the right idea (Threadripper etc), but that's semi-meaningless if we don't know the exact workload, bottlenecks, graphs over time, ability to parallelize tasks, etc.

Don't look at generic plain CPU benchmarks, they're next to useless.

I'd recommend getting ECC RAM, but if it's not a 24/7 server and you know how to run a memtest, it's not essential. One important thing to keep in mind if the last computer you built used DDR4 is that DDR5 is a nightmare and the rules are extremely different for 2-stick and 4-stick setups, e.g. if you buy 2 sets of 2 sticks that are rated for 6000MHz and are listed as motherboard approved RAM, they will still not boot in the 4-stick setup in anything more than 3600-4400MHz, so you need to specifically look at motherboard's list of those 4-stick setups.

Also, desktop level motherboards generally say RAM limit is 64-128GB but they'll support 256GB just fine. With Threadripper of course the limits are much higher.

What you will most definitely want though is checking that any given motherboard has enough PCI-E lanes for your NVMes - I'm not a fan of RAID, but if you don't mind the hassle and need contiguous logical space for MSSQL, then it would be an option. However, the disk I/O (several GB/s) with modern higher-end NVMes in general is unlikely to be a bottleneck with SQL server of any kind.

Honestly, the biggest performance improvement you could make is switching database engines, refactoring table structure, and trying different approaches on how to write queries.

If you spend a lot of time in the ingest phase, and you don't have that sufficiently optimized enough, split the files in N chunks (approx number of cores) and process them with something written Rust. (Or any other language, but multi-threaded tasks tend to be easier/safer/faster in Rust unless you're already very good with writing reliable C/C++/Zig/etc). The bottleneck here will most likely be inserts into MSSQL, so finding an optimal batch size to INSERT and fine tuning settings for it might boost you significantly. Sadly I don't have specific recs, I've been working with Postgres.

If you can, please upload some graphs from something like Grafana or whatever MSSQL performance monitoring tools - CPU load, IO load, network load, disk latency, CPU temperature, etc. One set for each workload (ingest, search, computationally heavy queries, IO heavy queries, etc, whatever is holding you back).

Source: I have built/sold computers and also been a DBA + architect + web developer with focus on infra, SRE and optimisation for 2 decades.

Sorry one more qualifying question. The current setup has the MSSQL data files on magnetic/rotational HDDs?

Sorry one more qualifying question. The current setup has the MSSQL data files on magnetic/rotational HDDs?

"It depends". A lot is on a Samsung 840 Pro NVMe but that's only a 256GB drive so pretty tight on capacity obviously, so a lot ends up on 7200 spinning rust. Clearly, that's where a lot of the gains can be made, good point in your earlier message re the number of PCI-E lanes too.

Addressing some questions that came up on the socials:

1. I haven't set a budget, but I'll know it when I hit it 🙂 Primary focus is getting a return on the outlay.
2. My current machine is running 128GB of RAM and my gut feel is "about 4x that" for the new one
3. Current machine is also an Intel Xeon Silver 3210 @ 2.20GHz
4. For reference, @stebet's setup is here (site is currently dead though): https://pcpartpicker.com/list/w6BmXR

Yeah seems like there's a lot of outages today. Yay everything is cloud!

Anyway: again, really depends on the workload, and specifics of how data is laid out by MSSQL, i.e. if you read by an index, and whether the server reads from random blocks on a HDD, or if it sorts it first by location and reads almost-sequentially - that alone can have mean 50~1000x slowdown just by the nature of HDDs that you would not have even on a same-read-speed SSDs - nevermind that NVMes nowadays have 200x higher throughput/read speeds. So even with the exact same hardware today, just moving to NVMes will speed up some of your workloads literally >100x. I'm sure you know that but it's worth repeating, as someone who had nose deep into io bottlenecks.

If you plan more than 256GB RAM, then most definitely go with Threadripper. However, the price difference is not negligible, and you will NOT use most of the cores most of the time, again by nature of the sql engine / io wait / tasks that cannot be reasonably parallelized. You will get lower single-thread performance, so again, it's good to analyze how many of your current bottlenecks are on single-threaded tasks and see if the slowdown is acceptable. Software will improve and more tasks will be parallelized, but some of them fundamentally can't. Again, sorry for stating the obvious.

If you'll be fine with 256GB RAM, high-end Ryzen can be fine. You'll get slightly faster single-thread performance, and again, not many tasks will benefit from 16 vs 32 physical cores. Do some napkin math of what your data looks like, eg.

• SQL table is 4TB on disk
• disk read speed is 8GB/s
• RAM access speed is 60-100GB/s (desktop) or 200+GB/s (Threadripper)
• 1 thread = 100% disk speed, 10% RAM speed, 8.5min to read
• 10 threads = 100% disk speed, 100% RAM speed, ~50s to read
• 32 threads = 100% disk speed, 100% RAM speed, 22 cores fighting for resources and therefore being underutilized, ~50s

Similarly, assuming SQL Server can use most of the RAM as cache (again, as the other person said: double check your license, it might have artificial memory limits), let's say 220GB can be used by either kernel or DBMS cache - that will have 10x higher IO than the ones read from disk - if you know you're using an index to read only 10% of the table, e.g. 400GB, you might get to most of the CPU cores to full(er) extent. At this point you'd benefit relatively little from 512GB RAM over 256GB, possibly 0-20% overall query time, depending on how well will the DB prefetch data versus how likely is it all to be cached (running 1 query once = everything will need to be loaded from disk anyway).

Your current CPU is anemic by today's standards, so anything even in the mid tier consumer range will be a massive improvement.

Anyway if you want, we could do a quick zoom call (free, no strings attached, not selling anything, just as a gratitude for HIBP), I might be better able to provide my educated guess of what could bottleneck you now and in the future if I know more about approx data/table size, query complexity, etc.

The PCPartPicker finally loaded, so notes:

• yep Ryzen 9950X is a good high-end consumer desktop CPU. Not sure if I'd go for the 3D cache variant, I haven't seen/done benchmarks [for SQL performance] myself, I have 7950X non-3D at home. Our work servers were almost exclusively Epyc CPUs.
• Fractal Design cases are good. Another reliable brand lately is Lian-Li. For details of current ones I'd go look at whatever recent reviews did GamersNexus do.
• Motherboard - I'd look around more, sadly the higher end motherboards are all $500+, but you might get 5 M.2 slots, and one or two USB4, so it might be good to spend more to future-proof it. Again, before buying, check specific RAM compatibility. MSI, Asus, Asrock, Gigabyte are these days mostly on par. They're all enshittified and install garbage from BIOS that's enabled by default.
• RAM: For my motherboard, the only officially large supported RAMs are 2x48GB or 4x24GB at 6000MHz. The largest kits I see in a local shop are 4x48GB; there are no 4x64GB that I see, and by buying two 2x64GB, any guarantees of speed go out of window. Either way, for desktop it seems 256GB is the realistic limit for the next few years. Buying a threadripper means likely +$200 ~ +$1500 for CPU (in the range for your use case) and another +$200-1000 for motherboard (also: many of them still have only 4 DDR5 slots).
• Samsung make good NVMes but they're not the only ones. Again, hypothetical 14GB/s reads might not materialize in practice - it will mostly be helpful for data ingestion, assuming simple text format like CSV that has a very low overhead for parsing - but SQL data will incur slightly larger overhead. I couldn't quickly duckduckgo any specific mssql benchmark for potential maximum read speed, but my gut feeling is that at best it would be around 10GB/s, although it could be 5 it could be 15, really depends on what Microsoft optimized for. Meaning 9100 Pro might not provide tangible benefit, and it might be better to spend money on larger drives than to chase highest raw performance. No shame in putting 2-3 8TB drives in there. With 128+GB RAM, there's little need for worrying about Windows boot drive having a separate super extra fast NVMe, as most everything will be cached, and no applications should be swapping data, but instead managing their own cache intelligently and let OS handle the rest.

(Also, of course indexes are a separate topic, as a ballpark I count additional 5-10% of table data size for each index.)

I'm going to buck the trend here and suggest looking at used datacenter grade 3D XPoint drives for storage, instead of going for oodles of RAM. Get several P4800X drives, put them in RAID0 in Storage Spaces, and 256GB of RAM ought to do it. Use those drives as the working set drives, and back up cold data on other consumer-grade SSDs or spinning rust in a redundant array. Beyond a certain number of cores, as alluded to above, you're not going to get much more perf. My suggestion: Core Ultra 7 265K (or Ultra 9 285K if you want a tiny amount more single-thread performance), 256GB DDR5 (prioritize latency over ultimate bandwidth), and a PCIe to U.2 adapter for the 3D XPoint drive(s).

Ultimately, I'd look at minimizing latency vs maximizing bandwidth for DB queries.

• Samsung 9100 Pro is a great choice for raw throughput, up there among the fastest prosumer NVMe drives available. If you're really game you can go through the pain to setup BitLocker hardware encryption too. You can use a 4TB for main and up to 8TB for data. You'll want a motherboard with at least 2 PCIe Gen5 x4 M2 slots to get the most out of it.
• For memory, 256GB is the max you can achieve on consumer platforms, and even then you'll struggle to get high transfer rates and stability, plus you'll need to use 4 DIMMs even though the platform is only running on 2 channels. If you want real memory scalability, you need to go Threadripper (or Xeon, but let's be real)
• Threadripper is where it's at for true scalable workstations. For just coding & container use, maxxed out regular consumer platforms are more than fine, but once you're running large databases locally and want real CPU, memory and I/O scalability, you need to go workstation grade.
• You don't need anything at all special GPU-wise to run the monitors you're talking about. A Quadro P400 or T400 will be just fine. If you use any RTX-based real-time processing for you AV/streaming stuff (background removal, noise removal, etc.) then drop in an RTX 5060 or even an RTX 3060.

Hi Troy,

Threadripper is pretty much it for 'budget' serious workloads these days. Next step up is Epyc and deep rabbit holes.

Two main reasons -
1: Lots of performance, lots of RAM, lots of cores
2: Lots of PCI slots - with the right motherboard this means many PCI slots for things. Multiple GPU's, room for U.3 SSD risers, proper 10GBE networking, etc Not only slots for things but room for things.

2: is the reason I went over to the Threadripper platform for all my GIS workstations.

If you want lots of RAM then consumer AMD is not for you - stability with all 4 RAM slots loaded is hit & miss. Ryzen 9000 series CPU performance is fantastic but let down by lack of PCI lanes thus gimping motherboards. Flip-side is you can build a pretty fast system for the price of a Threadripper motherboard.

The Nvidia RTX4000 Ada SFF is a nice little card - small, good performance, low noise & low heat, as well as 20GB handy for local LLM work.
For more serious GPU performance 5090s are fierce but large. The water-cooled versions are great at optimising space but cost a bit more. Something like the Asus ROG Astral LC 5090 is a lot more compact slot-wise and using a decent BeQuiet case makes it easy to locate the radiator.

PCIe Gen 5 SSDs are pretty fast. Samsung and Crucial have a few options.
U.3 is the current enterprise durable, high speed, high IOPS, large volume format. Up to 30TB. Not available from 'gaming parts' stores so a bit harder to find without having to fill out a contact form and getting a business development representative calling you back in three days....

Cooling gets complicated - Threadrippers with all the fruit get a bit warm. All my work PCs are full of either Noctua or BeQuiet fans.

Threadripper motherboards can be very temperamental to get going. Be prepared to grab a few cold ones and take a few walks on the beach. Once running they're rock solid. I have Gigabyte, Asus, and Asrock Rack boards and none have done anything weird after initial setup.
Except for the one that I had an Intel Arc card in - that was nasty. Two cats in a cage....

Finding a vendor that will build what you want is going to be hard. I've seen some funky stuff built by 'specialists'. They usually mess up the cooling config and you end up with a noisy yet overheating (=throttling) mess.

Buy a very proper UPS.
Buy more solar panels.

I like what @kltye is cooking

Just wanted to say a big “thanks” to everyone that chimed in here. Loads of good info and some clear patterns emerging, I’m going to chat to a few local builders, point them to this thread and see what they come up with. Will share the final spec once I work it out.

Without doing any real analysis of your thread usage, I'd still go Intel

Here's a rig that would fly compared to what you're currently using: https://pcpartpicker.com/list/tGVqC8

I've taken into account heat and noise in my decision making

Video card is good bang for buck, and quiet
CPU cooler is awesome, I have one myself

Without doing any real analysis of your thread usage, I'd still go Intel

Here's a rig that would fly compared to what you're currently using: https://pcpartpicker.com/list/tGVqC8

I've taken into account heat and noise in my decision making

Video card is good bang for buck, and quiet CPU cooler is awesome, I have one myself

@elevator-oper8or I would STRONGLY recommend against that specific GPU. It is NOT "good bang for buck", far from it in fact. That's a 4060 Ti 8 GB for 550 USD. that's a frankly horrendous price for that GPU. The normal price for a 4060 Ti 8 GB is 399 USD, which is already 150 EUR less. But not only that, there are other cards from the current generation which are FAR better, like the 5060 Ti 16 GB or even the 5070. Both of these can be had for the same or a lower price. I know that for just a "I want to hook up 3 monitors to this card" the 4060 Ti 8 GB will suffice, but at least just take a regular MSRP model instead of an overpriced premium AIB model.

Yeah, I was thinking if I should say something about it. For something that won't run games, you can either go for the cheapest suitable card like an ancient Radeon RX580 for 130 euro (no reason for it specifically, I can just see it has 2 DPs and 1 HDMI) if you know you're not going to need CUDA or 4k gaming.

If you will, or realistically foresee using CUDA for accelerating something like video editing, then yeah, what nzall said about is good advice. Radeon also has decent choices in mid range, IF you know you won't need CUDA.

Either way, GPU is literally the easiest thing to replace/upgrade later if needed.

I would just like to reiterate that if MSSQL is the absolute most important thing to run fast, you need to find an MSSQL optimization guide / advice / expert / official hardware recommendations / vendor optimization guides (e.g. AMD publishes detailed BIOS/OC settings for best performance for individual software).

Either way, any higher-end desktop machine will be a massive improvement over the previous machine, the question is if you want it to be future-proof for the next 3-4 years (eg. high end consumer hardware) or 6-8 years (e.g. Threadripper + top notch RAM and NVMes). The latter option might not be necessarily cheaper than just buying a new computer every X years, the question is perhaps more about the convenience of not having to reinstall OS and tuning everything.

The RTX 5060 and RTX 4060 Ti are basically identicle performance wise:
https://gpu.userbenchmark.com/Compare/Nvidia-RTX-5060-vs-Nvidia-RTX-4060-Ti/4184vs4149

Here is why I think it's good bang for buck.

• Why would I waste $200 extra on a 5060 TI when this isn't a gaming PC?
• Why would I buy a noisy, heat generating card when this isn't a gaming PC?
• Why would I go on the cheap and save maybe $100-$200 for a lesser quality card with only 2 DisplayPorts? or that is potentially unreliable and noisy?
• Why would I put a cheap video card in such a beautiful rig?

You can pickup an MSI GeForce RTX 4060 Ti GAMING X 8G on sale for $549AUD inc GST in Australia at the moment.
The PC builders would probably just substitute the RTX 4060 Ti for a RTX 5060, so in saying that, perhaps one of the MSI GeForce RTX 5060 GAMING series cards would be a good substitute. Same price range, same effective performance, but a newer vintage.

TL;DR - I'd go for a 4060 TI on sale or a 5060 - quiet model

I hate noisy PCs, so I pay the premium for the quiet, higher quality version of video cards. I run an MSI GeForce RTX 3060 GAMING X and absolutely love it. It's so quiet (silent most times), performs well, has all the DisplayPort connections I need and has never skipped a beat. Asus, MSI, both good choices.

End of the day, if all anyone is complaining about is my video card choice, suggest better alternatives in AUD.

Again, my point wasn't "this specific RX580 is the best choice", it was just a data point where I knew the price and availability in a local shop.

AMD drivers have fan tuning and a zero RPM option. Some cards have dual bios with quiet/performance switch on the card. $500 is an overkill for a workstation PC that doesn't use anything CUDA. I'm not saying 4060 Ti / 5060 is a bad choice for you or anyone specific.

I think you've moved on to the next phase of your journey, but today I stumbled upon Dell's Precision 7875 Tower Workstation which I was able to configure with a Threadripper Pro 32c/64t, 256GB RAM and the aforementioned RTX 4000 Ada for $12k. I specifically mention this because it can be upgraded at a later date to way above 256GB RAM ($16k for 512GB) and you can get fancy Dell support for it (not sure how that works down there for you Australia, YMMV).

Thank you all for the input, loads of good feedback and healthy arguments 😊 Based on this, I have two proposed machines from local builders:

The drive specs on the second are a bit light (the first one with 3x 4TB is a much better fit), and there are obvious differences around RAM and CPU but other than that, how do we feel about this? What would you change given the discusison here?

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.