Windows SQL Server Upgrades - Second Processor or Faster Cores?

[JRA]

Hi all - just thought I'd get a few opinions in. Hopefully this is in the right place first of all!

 

I'm thinking of upgrading our SIMS server. It's going from 32 to 64GB of RAM regardless, but I'm wondering (as per title) what would be better in terms of performance for it; upgrade the current 6-core processor to a faster one, or stick another processor in it?

 

SQL licensing cost isn't a factor for me as another department has agreed to pick up that tab should we need it.

 

I'd be grateful for any opinions anyone's got! Thanks everyone.

[siuko]

I think initially I would be checking resource monitor to see what is being used

 

Our Sims server (for a school of approx 1200 students, Sims.mdf 8.4Gb) is only using 20.6Gb of the 64Gb we have installed (for the OS and SQL etc) - it also never creeps much higher than that amount

 

It also doesn't max out the CPU's in the system

 

If you are having sims speed issues it would be worth check resource usage first as a faster storage system might help more than extra ram and processors

[dry]

Our DB size is around 3.8GB (large primary) and we're using no more than 6GB of RAM. CPU is hardly taxed at all and we've had no complaints from the SIMS users.

 

Have you set upper memory limits in SQL Management Studio? My understanding was that it'd eat as much RAM as you make available to it, unless you set memory limits.

[SHimmer45]

we are in a school which uses SIMS alot and have other bits plugging into it and to be honest it isnt really CPU taxed (using 16GB RAM 6.5GB MDF)

this is where x64 OS's start to make sense for things like SQL.

 

what type of storage is it running on

[JRA]

Hi all! Wow, thanks for the pointers!

 

Right, server does SIMS and SQL usage is capped at 25GB of my 32GB, and minimum memory is 1024KB/query. Seem right?

 

Disks are 15k SAS drives, 653960.

 

Ambitions for it are to speed up SIMS a bit and, since we have Tasc's PARS also now, squeeze more performance from it. It's an HP DLP380 G8 with 32GB RAM, single Xeon E52620 @ 2GHz with 6 physical cores (space for a second processor.)

 

Any further thoughts?

 

Thanks much everyone.

[Norphy]

Replacing those 15k drives with SSDs might be a good place to look, albeit an expensive one.

[JRA]

Hehe yeah I know. If only.

[SHimmer45]

we are still using 15K mechanical drives, id suggest running some performance logging to see if you are hitting "100%" anywhere before sinking money into something which might not make alot of difference at the user end

[PotNoodleTech]

Assuming your CPU is not maxed out, SSD drives will be the only real way of getting any improvement.

 

There is a limit to how fast you can make SIMS. It's a large database that's evolved over decades. it is not an efficient beast. You can end up throwing good money after bad because you could install it on a4CPU 256GB RAM SSD server and find it's not actually much faster in daily use.

 

One thing to remember is workstation performance helps when using SIMS too - so if you've got old PCs with 2GB RAM and mechanical HDD's you'll get a much better experience with a more modern PC, 4gb+ RAM and a 128GB SSD.

[JRA]

Well thanks everybody!

 

Cor, well then, I'm up a creek pretty much unless I get SSDs in? Would it really make negligible difference on the extra cores and/or RAM? It doesn't appear that I'm maxing out anything except to the allocated max RAM for SQL - but then, that'll just gobble up to the limit of what you give it, that right? SOMETIMES the disk I/O clips the limit but that's about it.

 

Guh?

[siuko]

What exactly have you allocated for max ram?

 

The 64Gb server our sims is on never goes above 20-21Gb for the OS and SQL etc

 

I have our max memory set to a silly high number and it never uses anyore than the 20Gb or so

 

Maybe set yours higher and see what it does?

[dry]

I remember reading that the RAM limit recommendation is 2x (+ a bit) your DB size, so a 3.5GB DB size should have a limit of 8GB RAM and run well with that... a 5.5GB DB, 12Gb and so on. What size is your DB?

[JRA]

Ta both. Looks like the SIMS db is a muscular 15GB and we've got a 25GB limit of our 32GB RAM for SQL.

 

So, is it quacking like the 'more RAM' duck at this point for anyone else?

 

Thanks again to everyone.

[siuko]

Just checked if the 2x (plus a bit) was correct here

 

Sims.mdf is 8.41Gb

 

Memory commited to sqlserver in resource monitor is 18.7Gb

 

So that 2x (plus a bit) seems fairly accurate for us

Edited December 8, 2016 by siuko

[3s-gtech]

Remember that your SQL configuration may not be at its most efficient - we're a medium secondary with 15+ years of SIMS usage and with refinement of SQL parameters our main SIMS database is only 6GB or so. We run everything within a 12GB limit (used to be 28GB).

[jinnantonnixx]

Throwing extra hardware at the problem is a seductive concept, but you may not get the returns that you hoped for.

You might find that adding processors without a MAXDOP limit could make your server slower. Before you add processors, you should first establish that you need extra processors.

https://www.brentozar.com/archive/2013/12/q-can-high-maxdop-make-a-query-slower/

 

If you're seeing the (in)famous CXPACKET waits, your machine is spending time waiting for the parallel threads to be gathered together.

 

It's worthwhile looking at the fundamentals first. Have you separated your OS, your database files, logs and temporary tables/files onto separate LUNs/disks? This can yield good results as zero cost apart from time spend re-organising.

https://msdn.microsoft.com/en-us/library/bb402876.aspx

 

Sometimes it's a good idea to step back and do some analysis before deciding what to do next. At the very worst, you'll have good grounds which justifies your hunch, and you'll learn more about your system.

 

Brent Ozar is the guru's guru. His website is full of great stuff, and he publishes lots of scripts that can really get to the heart of the problem quickly. I've used them many times. Here's a good place to start. Be sure to explore the links from the 'Tools for baselines' section. These are solid gold.

https://www.brentozar.com/archive/2015/02/slow-not-metric/

Edited December 29, 2016 by jinnantonnixx

[jinnantonnixx]

More on the topic of parallelism. As it happens, I'm doing some work on this today.

 

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

https://support.microsoft.com/en-us/kb/2806535

 

Good stuff here:

https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/