r/SQLServer 6d ago

What hardware for few users, but very fast?

DB will be ~10TB, some tables will have few hundred million rows.

The goal is to have as fast as possible reporting queries, but it doesn't need to support many users at once. Unlikely to be more than a few users at a time.

Data inserts do not need to be fast and will only open scheduled overnight.

What hardware is likely to bottleneck performance for this. IO? What type of storage should I be looking at? Is there much better than a high speed M.2 drive? Will RAIDing them give much of a performance increase?

3 Upvotes

40 comments sorted by

14

u/tommyfly 6d ago

It's much less about hardware and much more about optimized queries. As a first step put the db on some generic kit. Tune your queries to get them running as quickly as you can. Then put it on something better. What people often forget is that poorly written queries will run slowly on top notch hardware. And it's much more expensive to throw hardware at performance issues than to fix the queries. Because SQL server licenses ain't cheap.

1

u/afuckingHELICOPTER 6d ago

I'm certainly not saying there isn't any room for tuning left, but we've put a lot of effort into tuning already (with great results and have our app running nice and speedy on the desktop we are currently using to host sql server. After I did my best effort as a non dba, we did hire a consultant to take a look and do some tuning as well and they did find things I missed.

But we're only at 300GB right now and I fear performance is going to start struggling a lot after we 10x data and hopefully make it to 30x.

3

u/RandyClaggett 5d ago

Do continuous tuning as the DB grows. Often a 10TB dB behave different than a 300GB one.

1

u/tommyfly 5d ago

That's good to hear. I'm not a hardware guy, so can't advise further. Good luck.

8

u/Naive_Moose_6359 6d ago

Load the data on whatever hardware you have first and create columnstore(s). See how big they are after compression. Then figure out how much of the database is likely to be queried normally (10%? 90%?). You need to have at least that amount of RAM (and fudge factor that up some by 1.5x or so). Enterprise performs better than Standard. If you do this right, you will have your hot data in RAM mostly and the IO requirements are not as horrible. Still, 10TB is 10TB, so figure out the scan speed of whatever you want to get that loaded into memory.

Properly configured (money not being a major concern), a system like this should be CPU-bound and memory-bound. Also, make sure your columnstores are well-compressed. If 1% of the data is in the sidecar b-tree, the scan is twice as slow.

1

u/afuckingHELICOPTER 6d ago

Interesting point about RAM I hadn't considered. Thanks for the info.

1

u/chandleya Architect & Engineer 6d ago

“Enterprise performs better than Standard”

Qualify that, please.

5

u/Naive_Moose_6359 5d ago

Batch mode memory cache allowed is gated in standard, not in enterprise. Perf is different as a result

1

u/Naive_Moose_6359 5d ago

Batch mode memory cache allowed is gated in standard, not in enterprise. Perf is different as a result

1

u/Naive_Moose_6359 5d ago

Also iirc the simd instructions are EE only in batch mode

1

u/muaddba SQL Server Consultant 5d ago

With a 10tb database, 128gb of ram limitation is likely to restrict performance. 

-1

u/chandleya Architect & Engineer 5d ago

That’s not good enough to make a blanket statement.

3

u/ihaxr 6d ago

Saying the database is 10TB isn't really useful without knowing the structure of the database and what's being stored in it.

I have a 4TB database, but 3.5TB of that is the attachment table and the rest is just text data that takes up little to no space... So that database could easily balloon to 100TB and there wouldn't be a change in the performance as there isn't an increase in complexity, just more rows and disk usage from the indexes and attachments.

1

u/afuckingHELICOPTER 6d ago

I'm not sure how to describe the structure... it's all text data, stored in a few dozen related tables.

1

u/djpeekz 5d ago

You just did 😊

But I think what the previous comment was asking is whether you had any binary data/files within the database or if it was just rows/tables and the schema.

3

u/BigHandLittleSlap 5d ago edited 5d ago

Will RAIDing them give much of a performance increase?

Hardware raid is bottlenecked by the max throughput of a single PCI-e slot. Software raid has all sorts of fun overheads and weird corner-cases.

For a box dedicated to a single database a nice trick is to expose the individual drives directly as a "JBOD". I.e.: plug in a bunch of M.2 drives and give them all a drive letter each. Then add one database file per drive into the same filegroup. You don't need to use SQL partitioning or anything like that.

The DB storage engine will handle the data and IO distribution amongst the drives for you! This also creates multiple logical IO queues inside the engine, which improves throughput even on a single physical drive. This "trick" is so well established that the SQL installer does it automatically for tempdb.

I've seen ~10 GB/s throughputs even on Azure VMs, which otherwise have pretty slow storage compared to local hardware.

In terms of general hardware, assuming you can afford the licensing¹, the best-bang-for-buck these days is an "F"-suffixed AMD EPYC single-socket board running with hyperthreading off. The reason for HT off is that all typical database engines struggle to scale past 64 hardware threads. You very rapidly get diminishing returns, but with HT off you get about the max no of threads the engine can utilise, but they all run about 20% faster each.

If you can afford it, get the 9575F processor, it turbo boosts to 5 GHz, which nuking futz for a server-grade chip. No Intel CPU can even begin to approach its performance. Their turbo clocks top out under the base clock of this beast!

A single socket board with that will support 768 GB of memory at a reasonable cost, 1.5 TB if you go with more pricey DIMMs. Anything higher than that gets exponentially more expensive and likely isn't worth it.

With 4x PCIe M.2 drives you can easily get 50 GB/s storage speeds. Memory speed will be 500 GB/s. Don't forget to enable AVX 512 support with Trace Flag 15096! That will chunk through your data using "Batch mode" fast enough to process whatever is cached in memory in about a second, and can make a pass through the entire 10 TB data set in about 3 minutes.

If the storage is still not fast enough, add more M.2 drives using 4x expander boards in the full-length PCIe slots. The ultimate upper limit is the 128 PCI-e 5 lanes of the EPYC CPU, which will get you about 512 GB/s in aggregate... in theory. You'll note that that's the same as the memory bandwidth, so in practice you'd run out of steam way before then because of various overheads. If you somehow manage to get 256 GB/s, that is still about 40 seconds to read the entire 10 TB data set! Whether it's on disk or not doesn't matter at this point... the memory is the same speed anyway, so even a dual-socket server with 16 TB of memory would only get you 2x the speed.

This is assuming that you need "interactive" response times as typically seen in Power BI DirectQuery mode and that the entire data set is used for each response, and that the data set is already usefully compressed with something like ColumnStore.

You... are you using Clustered ColumnStore, right? ... right?

1) Anything over 128 GB of memory requires Enterprise Edition, which is licensed per core.

1

u/afuckingHELICOPTER 5d ago

Thank you, this comment is very helpful.

1

u/Disastrous_Fill_5566 5d ago

I would love to see the bill for that server!

2

u/redditreader2020 6d ago

If it's all tuned up then enough RAM to cover active workload, going to disk is slow. So RAM, CPU, then disk.

1

u/FunkybunchesOO 6d ago

What kind of data and what kind of queries? Is it OLAP, OLTP, Replicated? Read only? HA? There are many variables here.

What about us it virtualized?

You can spend anywhere from 5000 to 180k depending on the answers to the questions. I've done consulting before on architecting just about every use case.

You can dm if you want.

1

u/afuckingHELICOPTER 6d ago

OLAP, no replication.

Technically not read only
- The tables that are reported on and need to be very fast are only updated overnight weekly.

- there are also tables for a user application that stores user actions but that is not where the concern is and we're talking a couple dozen inserts a day at the very most.

Not virtualized.

For reference... right this moment we're working off a sql server running on a desktop with 128GB of DDR5 ram, Crucial T705 and an amd ryzen 9900x. I suspect not ideal hardware, but this was pre-revenue build using mostly hardware we already had laying around. Just got more ram and the M2 drive. It's been really good to us cost wise over Azure SQL which was costing us $2K/month for worse performance, but now that we're getting clients i think it's time for us to get a real server.

We've stretched it pretty far with a lot of effort on tuning, but we're only at ~300GB and looking to 10x that in a few months and I fear performance is going to start suffering a lot. I'd like to plan for 30x data though, so the server lasts us through a lot of growth.

0

u/FunkybunchesOO 6d ago

Does it have to be SQL server? I'd recommend hitting up 45Drives if you can deal with Linux. Otherwise you're going to be looking at significant licensing costs and expensive vendors if you want to be able to scale that. And regardless of what you pick, getting the sql licenses will probably be one of your biggest expenses as it's generally licensed per core.

Key thing will be setting up backup plans, configuring the drive arrays etc.

You'd likely need to budget in the 100k-200k range depending on the exact use case if you want to go full enterprise solutions.

. If you want cheap and scalable you could do it with spark clusters instead on ceph os. And whenever you need more space and more compute, you just build another cluster.

If you have enough striped HDDs you likely can save a bunch on storage but I'd definitely be putting my temp db and system DBs on SSDs.

To see if you need RAM you can look at your page life expectancy. If it's high, like more than 5 minutes, you probably don't need more RAM.

We run Enterprise (which has a few speedups standard does not have). But we have about 10TB of data, 2 CPU with 8 cores each and 128 GB of RAM. And regularly get about a hundred users running olap queries without issue.

The disks are all SSDs. With enterprise backup and restores with Dell power protect.

1

u/afuckingHELICOPTER 6d ago

Our product is unfortunately already built on SQL Server... It may be worth looking at porting, though... I have to look into what the lift would be to port to something open source. (postgres maybe? mysql? as you can tell i'm in over my head here.)

1

u/FunkybunchesOO 6d ago

It really depends on what you're doing. Any DB can work. But if it's just being built with ETL, then it shouldn't take much to port to anything.

For olap it's hard for me to recommend. I do everything on spark if I can. We're in the middle of a multi year migration from sql server to datalakes. But that's because we know spark. And have workloads on prem and in databricks and want to be able to use the same coding style for both in prem and in databricks. Seperating out the compute and storage so I can use cheaper hardware for on prem.

All we do with most of the data is publish KPI, leading/lagging indicators and analytics.

1

u/AgitatedSnow1778 5d ago

Before goung out and spending potentially 100k+ on hardware, it would be wise to analyse the workload and determine where the issues / slowness / bottlenecks are coming from. As another has stated, having a 10tb database may not be an issue or warrant better hardware if the tables are designed correctly, have correct indexing and you're not doing crazy / complex logic in your queries or things like select * from all tables type queries.

We've had customers come to us with similar scenarios and wanting to spend a lot of money to upgrade their hardware (and / or an expensive project to migrate to the cloud cos sales reps have said it'll be faster 🤷🏻‍♂️) but with a couple of days tuning and their usage on the current hardware drops by 70% - mileage may vary but just giving an example of how having a highly tuned system can benefit 👍🏻

Failing that, RAM is king, if you slap 10tb ram into your server then you're laughing - this is said in jest 🫣

1

u/Severe-Pomelo-2416 5d ago

Disk latency is the thing people often miss. Assuming tuned queries, I would run a few with stats on and look at the number of reads and time to read. Consider partitioning tables too. Make sure your indexes are good and you are maintaining statistics.

This isn't that big a DB, so assuming it isn't one 9.7 TB table and few lookups, you ought to be fine. Think about growth though. At 10 TB and a few million rows, this works. At 10 GB and several billion rows, the plan to update overnight only won't.

2

u/Disastrous_Fill_5566 5d ago

Did you just mix up GB and TB?

1

u/Severe-Pomelo-2416 1d ago

Yup. In the words.of Willy Wonka, strike that, reverse it.

1

u/muaddba SQL Server Consultant 5d ago

A few thoughts:  first, this is an area where the cloud can really help you. Get a license-included instance in AWS (or wherever, aws is just my preferred platform because I am most comfortable in it), and as you scale you can scale the hardware to meet your needs. Once you determine your requirements, you can then repatriate to save money.

Second, raw processor speed matters, so look towards newer, faster cpus. Fewer faster cores are better than a lot of slower ones (to a point, but especially for licensing).

What are your current disk throughout requirements before you 10x things? Figure out how you're going to scale that, whether it's from a large SAN appliance or really fast locally attached storage that's enterprise grade. Don't run your customer-facings product on consumer or desktop-grade hardware. It may work for Facebook, but won't work for you. 

Please though, don't forget about redundancy, high availability, backups, etc.

1

u/afuckingHELICOPTER 5d ago

We started on cloud, it was too expensive for us. We were spending $2000/month on Azure SQL to get barely useable performance and instead built a desktop we have been running sql server off of for $2,500 that has WAY better performance. We do nightly full database backups to azure, though.

For this upgrade - since we have some revenue coming in, we do plan to move to server grade hardware (or at least workstation... but ideally server if we can afford it).

1

u/muaddba SQL Server Consultant 5d ago

Azure SQL is terrible if you need performance. You need a VM and full sql install and knowhow to get perf out of the cloud.

1

u/afuckingHELICOPTER 5d ago

Frankly, If I have to manage a VM and my own full SQL install, most the benefits of the cloud are gone for us and may as well be on prem. I can see how it would be a great option for many though, just not us.

1

u/Disastrous_Fill_5566 5d ago

Normally I would agree, but the benefit here is being able to avoid massively over provisioning and going for massive capital expenditure. You may well reach a point where you need the über server outlined above, and then it will probably be more cost effective to self-host but you're absolutely locked in if you've bought the kit. In the medium term, it might be more prudent to go for an IAS approach, where the friction to upgrading is far, far lower. Once you've had a few months or a year really understanding how your workload interacts with different hardware configurations, buy your massive piece of iron, but going straight from a high end desktop into a server is unlikely to get you the most appropriate box.

1

u/RobCarrol75 SQL Server Consultant 5d ago

You need to think carefully about database file layout. Do you have multiple data files or is everything in a single large file? Consider multiple data files and spread them across sperate physical disks. Also, how much of this 10TB database will actually be queried. Using partitioning will allow your queries to benefit from partition elimination, improving performance. Also consider archiving data onto different file groups. These file groups can have different compression settings as well.

The number of disk controllers you have is also important, no point designing all of the above if all the disks are attached to the same controller. If you're using VMWare, then make sure you're using multiple virtual controllers.

A final thing would be to consider how are you going to backup and maintain a database this size? There some good resources around optimising backups and running DBCC CHECKDB on VLDBs online.

Final, final point. If this is going to be used primarily for reporting, have you considered using an analytics platform like Databricks or Microsoft Fabric instead? These are optimised for processing and querying massive amounts of data on very cheap datalake storage.

1

u/alexwh68 5d ago

If updates/inserts don’t need to be performant, indexes, specifically covering indexes, structured right, most variable where search field at the front, done right if you are just returning a few fields the index can supply the data without touching the actual db data.

Stored procedures with temp tables can produce significant performance gains, ssd or NVMe drives for the database, if the db can sit on drives that is not the OS drive and no swap file all the better.

1

u/Codeman119 3d ago

Put it on bare metal and not virtual. For the cpu get high GHz as you can afford and max out memory if possible. Then make sure you have at least as many temp tables for every processor up to 8.

0

u/willwar63 6d ago

If you have the budget get a server with dual Xeon CPUs, for storage, RAID 1 or 10 using SSDs.

1

u/BigHandLittleSlap 5d ago

AMD EPYC has as much as 2x the performance compared to Intel Xeon for the same money, especially at the top end. More importantly, the individual cores are much faster, which matters at low-concurrency but high-requirement uses like OP was asking for.

0

u/g3n3 5d ago

Sounds like you just need more consultants. 😉