Baller server for SQL?

[FWP!]

I'm the Infrastructure Architect at the company I work at, and in my free time (lol) I have been looking into what it would cost for a new physical host for to move our SQL DBs to.

 

Our main business app relies VERY heavily on SQL and is used by around 500 employees on average every day.  The complication is that it must reference data going back to 2006, and some tables have 10's of millions of rows.  We have "cleaned up" the database as much as we can without purging needed data, and while the DB isn't huge at only around 250 GB, it gets hammered all day, every day, and we've started seeing issues with other application functions that go on in the background which also leverage other SQL DB's.  One of the processes pulls millions of rows of data, does compares, and then updates other tables, every night, for multiple DBs.  We have been working with the application vendor to see if we can figure out where the bottleneck is, and we're beginning to think we may need to move to a server with more horsepower (even though the current one only sits at around 40% CPU utilization, on average).  And yes, all best practices are in place for NUMA spanning, OS optimization, and BIOS settings on the B200 M4s.

 

Side note - we are on core-based licensing, right now the server is running as a 14 CPU VMware Windows 2016 VM and with 384GB of RAM, and SQL Server 2016 Enterprise Edition.  The VM is given full resource allocation and CPU ready times remain at less than 5%.  The Pure storage array does not seem to be hit very hard, it barely breaks a sweat most of the time.

 

With that said, I was wondering what you guys would consider a kick ass SQL host in the $10k-15k dollar range.  Ideally, two CPUs running at 3.5 GHz or above, whatever RAM, but I am open to opinions.  I built one out at Puget systems with two Xeon 6226R's, 384GB of RAM, and some NVME SSD's.  Then I would either possibly add a PCIe NVME card with a couple of drives running in RAID 0, or I could attach a FCoE LUN off of the Pure array for DB and log storage.

 

Any input is appreciated!

[leadeater]

Just as an additional note I would advise getting in to SQL profiling and look at the queries that are being run and the breakdown of runtime to see where the largest portions of time is spent and why. Throwing raw hardware power doesn't always get the gains you want or expect so just be careful of a let down, even if not now in a year or so when there is more data and more rows and it slows down again. Not that is will definitely happen but it's a good area to get skilled in regardless.

 

SQL query latency also responds very well to clock speed, most CPU configurations are able to hit the same total queries per second but if you drill down to that query latency it can be as much as half with a CPU that can hit high sustained and boost clocks. I had a good review showing this but a while ago but lost the link. That said the current 'best' MSSQL CPU would be the Xeon 6256 but that is a little pricey, The 6226R you are looking at is fine, the only areas I don't like about it is the much smaller L3 cache and the lower all core and peak boost clocks (it's a 150W TDP part). 6242R and 6246R would be better but both are WAY more expensive, 6242R would also benefit from tuning max parallelism but everything does tbh, if you need to get down to the sort of tuning.

 

Your storage not being hit very hard could also be a sign that your database file/filegroups aren't optimal and is limiting your performance. If you are lucky your database already has the required columns in your tables and you could do something like place each year in their own table partition and file then you'll have much greater usable storage I/O. Unless you are able to split your database in to multiple files you're actually a bit I/O limited, there are different ways to do this but anyway single mdf on a large database doesn't scale well but on the other hand it may not be possible to split data out so that is where NVMe etc come in.

 

Getting the absolute maximum performance out of MSSQL VMs is still a bit of a pain so our larger clusters are actually still baremetal OS using clustering. Our databases sit on all flash Netapp arrays connected using multipath iSCSI. We haven't actually had to yet do much database file partitioning on our physical clusters but on our SQL VMs we have, this is due to how ESXi handle disk I/O and command queues so I would advise looking in to VMware ESXi SQL optimization best practices. Basically you end up with multiple SCSI controllers and virtual disks being split across those and you do actually get way better disk performance.