dedicated m.2 for TempDB in MS SQL Linux

[nutsnax]

Checking here if anyone knows of a small/cheap m.2 drive for dedicated tempdb work that would be faster (for TempDB work) than a Samsung 980 pro or the like? I looked at Optane stuff like H10/M10 but can't really tell if it'd be suited for this (I think not?). This doesn't need to be terribly big (120gb or less)

Thanks!

 

[MrCalvin]

You might consider a drive with PLP (Power-Loss-Protection), Samsung PM9A3 /983 DCT, don't look at performance numbers, what matters is that the SQL server can write synchronize IO to the power-loss-protected memory on the drive instead of writing to the "slow" nans.
Actually, a SATA SSD with PLP is faster than a NVMe drive without PLP when speaking SQL write performance, that's how much PLP matters!
I don't think PLP matters much in read IO performance though, so I guess it depends on your use-case.

As fare I know Optane is strong in IO but often not strong in MB/s. But my own experience is that SQL doesn't really move many MB/s, but is IO intensive, so I actually think a "slow" Optane, which e.g. can only move 900MB/s, might be a lot faster in SQL than your Samsung 980 Pro which can move 3500 MB/s.

 

[nutsnax]

You might consider a drive with PLP (Power-Loss-Protection), Samsung PM9A3 /983 DCT, don't look at performance numbers, what matters is that the SQL server can write synchronize IO to the power-loss-protected memory on the drive instead of writing to the "slow" nans.
Actually, a SATA SSD with PLP is faster than a NVMe drive without PLP when speaking SQL write performance, that's how much PLP matters!
I don't think PLP matters much in read IO performance though, so I guess it depends on your use-case.

As fare I know Optane is strong in IO but often not strong in MB/s. But my own experience is that SQL doesn't really move many MB/s, but is IO intensive, so I actually think a "slow" Optane, which e.g. can only move 900MB/s, might be a lot faster in SQL than your Samsung 980 Pro which can move 3500 MB/s.

PM9A3 spec sheet
I found the 960GB version in Denmark for €162,00 (without VAT)

Yeah I've got pm9a3's in the production box. The ones I have for this are actually PM9A1's so same difference to 980 pros.

This would be a small-ish tempdb drive (MAYBE another for logs if cheap enough) but I'd prefer it be faster for little I/O than mega throughput.

Side note, I wonder if a fast (short) fiber link and a massive cheap dedicated RAM drive on the other side could be useful here?

 

[T_Minus]

Buy a low capacity optane (200GB) put it in there and be done, no need to over think it or add complexity like fiber + RAM drive.

 

[nutsnax]

Buy a low capacity optane (200GB) put it in there and be done, no need to over think it or add complexity like fiber + RAM drive.

Any suggestion on which one? There seem to be so many.

 

[T_Minus]

Any suggestion on which one? There seem to be so many.

Intel® Optane™ SSD DC P4801X Series (200GB, M.2 110MM PCIe x4, 3D XPoint™) Product Specifications

Intel® Optane™ SSD DC P4801X Series (200GB, M.2 110MM PCIe x4, 3D XPoint™) quick reference guide including specifications, features, pricing, compatibility, design documentation, ordering codes, spec codes and more.

ark.intel.com

 

[acquacow]

Buy several small drives and run one or two tempDB's per drive. You want one tempDB per core on your system for max speed.

 

[nutsnax]

Buy several small drives and run one or two tempDB's per drive. You want one tempDB per core on your system for max speed.

One tempdb per CORE? I have 32 cores/64 threads.... so that means 32 SSD's? Would this prefer high bandwidth or low latency? Perhaps a pile of cheap/disposable 16GB Optane SSD's or SATA SSD's even?

 

[CyklonDX]

At company I work at, the cheapest option we use for tempdb is micron 3400. (we bought 2TB models, and under-provisioned it to 512GB, giving us massive boost to endurance)

It has good performance, when ran on gen3 it doesn't overheat much under real stress for prolonged time.

and most of all has power loss protection.

 

[acquacow]

One tempdb per CORE? I have 32 cores/64 threads.... so that means 32 SSD's? Would this prefer high bandwidth or low latency? Perhaps a pile of cheap/disposable 16GB Optane SSD's or SATA SSD's even?

You can see how many you can run on one ssd before it becomes a bottleneck. All the tempdb stuff is single-threaded, but you can use multiple tempdb's to multi-thread and improve transaction times.

 

[i386]

I have 32 cores/64 threads

And how many licenses?

 

[nutsnax]

And how many licenses?

All of them.

 

[CyklonDX]

you have to pay for the amount of cores on the system in new versions of mssql.

 

[nutsnax]

you have to pay for the amount of cores on the system in new versions of mssql.

Yep, I'm limited to, I believe, 24 cores on SQL but this nowhere near just a SQL server.

 

[986box]

For standard edition, you are limited to 24 cores. Enterprise limited by OS.
Microsoft sells 2 cores license. For 32 cores, you pay for 16 2 cores license.
If I remember correctly, SQL tempdb performance max out at 14 tempdb files.
What you can do I alternate the tempdb files across 2 drives.

 

[nutsnax]

For standard edition, you are limited to 24 cores. Enterprise limited by OS.
Microsoft sells 2 cores license. For 32 cores, you pay for 16 2 cores license.
If I remember correctly, SQL tempdb performance max out at 14 tempdb files.
What you can do I alternate the tempdb files across 2 drives.

I'm already locked into the 24 core now so I'm just going to work with what I've got. I've got lots of other stuff going on anyway.

I'll look at the 14-drive thing. What might be cool is a PCI-E 3.0 NVME raid controller with some cache (Dell or whatever) with a bunch of Optane 58GB sticks attached to it.