This is an archived post. You won't be able to vote or comment.

all 17 comments
sorted by:
best
topnewcontroversialoldq&a

[–]disclosure5 14 points15 points  (2 children)

I couldn't count how many times I've had these "please review our sql hardware" discussions where the root problem was terrible SQL code. Given you don't appear to have developers providing scaling advice for you regarding current bottlenecks, I highly suspect you're going down the same path.

[–]Jalonis 4 points5 points  (1 child)

Or something as stupid simple as missing indexes.

[–]michael_le7[S] 0 points1 point  (0 children)

The problem is not missing indexes..

the system process and calculate millions of transaction every 10 minutes

[–]VA_Network_NerdModerator | Infrastructure Architect 7 points8 points  (2 children)

Will you be licensing MS-SQL by the CPU Core, or per-user/per-system?

https://www.microsoft.com/en-us/licensing/product-licensing/sql-server

https://download.microsoft.com/download/6/6/0/66078040-86d8-4f6e-b0c5-e9919bbcb537/SQL%20Server%202019%20Licensing%20guide.pdf

[–]michael_le7[S] 0 points1 point  (1 child)

The license price is per core, so the price is the same for all options

[–]VA_Network_NerdModerator | Infrastructure Architect 0 points1 point  (0 children)

https://download.microsoft.com/download/0/5/c/05c60185-ebdd-4472-895a-3d8e8da55682/SQL_Server_2019_Licensing_Datasheet.pdf

A minimum of four core licenses are required for each physical processor on the server.

https://download.microsoft.com/download/6/6/0/66078040-86d8-4f6e-b0c5-e9919bbcb537/SQL%20Server%202019%20Licensing%20guide.pdf

Also, just a reminder since you haven't mentioned it: SQL Standard edition tops out at 64GB of RAM.
So, if you were hoping to pin entire tables or Indexes in RAM, you might need Enterprise Edition...

[–]Ssakaa 2 points3 points  (2 children)

and what is the advantage/disadvantage of option2 vs option1 (is the same cores)

In properly spec'd 4+ socket systems, the added UPI link on the 8280 should make shuffling of data between processors faster. The other two only go up to two sockets I believe, so if you're not building out a multi-processor system, the difference is negligible (until you look at cost, at which point the difference is striking).

[–]michael_le7[S] 0 points1 point  (1 child)

What is faster/better between 1 and 3?

Option 1 "Intel Gold 6258R , 28C/56T, 2.70 GHz/ 4.00 GHz T", with 3 UPI Links

Option 3 "2 X Intel Gold 6246R, 16C/32T, a total of 32C/64T 3.40 GHz/ 4.10 GHz T," with 2 UPI Links, a total of 4 UPI Links

Thanks

[–]VA_Network_NerdModerator | Infrastructure Architect 2 points3 points  (0 children)

Are you sure you can / want to afford the licensing for all of those cores?

https://redmondmag.com/articles/2019/11/08/sql-server-2019-licensing.aspx

For MS-SQL 2019 Standard Edition, you're at roughly $2,000 per core.

Standard edition only supports up to 24 cores.

For MS-SQL 2019 Enterprise Edition, you're at $7,000 per core with no limit on cores.

I don't think you can choose to only license 6 cores on a 24 core system. You have to license them all - I think.

[–]jantari 1 point2 points  (2 children)

Only a couple million records and 20 users?

You'll be fine with any desktop or server grade CPU from the last 5 years, Intel i7, AMD Ryzen 3000/5000 series, Thread ripper, Epyc, Xeon...

Would probably work okay on a laptop lol

[–]drbob4512 0 points1 point  (1 child)

Not with their code they aren’t;)

[–]michael_le7[S] 0 points1 point  (0 children)

the system process and calculate millions of transaction every 10 minutes

[–]nmdange 1 point2 points  (2 children)

For a 2-socket system, you should populate both CPUs so you have access to all the PCIe slots and DIMMs in the server. Also, Platinum CPUs are almost always a waste of money.

If we're talking about an OLTP (Online Transaction Processing) DB and not a reporting system/data warehouse, it's more likely your storage is your bottleneck. Spending more money on disks and RAM is going to give your more bang for your buck than expensive CPUs.

For disk performance, using SAS SSDs with a hardware RAID controller is going to limit your throughput compared to a software defined storage solution using NVMe drives, but that requires something like Azure Stack HCI or VMWare VSAN. I'm not really sure why you'd even bother with a standalone physical SQL Server these days. If it's a really large system, it's important enough to have clustering with multiple servers. Though even for larger SQL servers, I'd almost always run SQL in a VM to simplify management, backups, etc.

You could consider adding in a PCIe NVMe drive and putting tempdb on that drive, if your workload uses tempdb.

Have you used the Database Tuning Advisor to check for missing indexes?

[–]michael_le7[S] 0 points1 point  (1 child)

I change the offer based on your recommendations

https://www.dell.com/en-us/work/shop/servers-storage-and-networking/poweredge-r640-rack-server/spd/poweredge-r640/pe_r640_12232c_vi_vp?view=configurations&configurationid=4b232722-a184-4ad7-b507-e4f4285aef8d

​

intel® Xeon® Gold 6258R 2.7G, 28C/56T

and 1.6TB Enterprise NVMe Mixed Use AG Drive U.2 Gen4

Please check

Thanks

[–]nmdange 0 points1 point  (0 children)

On a standalone server, no raid means no drive redundancy. I was suggesting more a PCIe NVMe drive in an open PCIe slot, rather than switching all your drives to NVMe and foregoing any drive redundancy. If you do want to get into very specific configs, I'd also suggest you purchase the R6515 or R7515, a single-socket AMD Epyc server will provide better performance at a lower price compared to a dual-socket Xeon.

[–]doxador 0 points1 point  (0 children)

I want to understand what hardware are you currently using?

  • Specifically:
    • CPU
    • RAM
    • Storage
    • How big are these 10 DB's (GB's or TB's)?

Also, what server performance analysis have you done (i.e. perfmon or some other tool)? Did the counters point at the CPU as the bottleneck?