September 17, 2014 at 7:45 pm
I know SQL Server 2008 needs RAM. When it comes to cost, I think the bus speed is unimportant. I think I just need 32 GB (as opposed to 28 GB with a fast bus speed). Does bus speed affect performance compared to the sheer amount of RAM?
September 18, 2014 at 1:26 am
32 GB is not a huge amount of RAM: get more if you can. Obviously, unless your databases are so small that they fit in RAM anyway.
As far as bus speed is concerned, the faster the better. I can't tell the real observable performance gain though.
-- Gianluca Sartori
September 18, 2014 at 1:52 pm
CPU is typically not the bottleneck nowadays, I/O is. More RAM = bigger buffers = less I/O. Thus, RAM will typically be much more vital to performance that CPU speed or bus speed. As the old saying goes, "All computers wait at the same speed".
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 18, 2014 at 3:52 pm
ScottPletcher (9/18/2014)
CPU is typically not the bottleneck nowadays, I/O is. More RAM = bigger buffers = less I/O. Thus, RAM will typically be much more vital to performance that CPU speed or bus speed. As the old saying goes, "All computers wait at the same speed".
Ironically, I've just completed a T-SQL fix for a major CPU problem due to some really terrible code that was running 30,000 times during the workday. It was a major bottleneck and it was causing 90% CPU usage for 2 to 5 seconds on an otherwise quiet CPU. When normal daily work entered the picture, all 4 CPUs flattlined at 100% for many seconds just scant seconds apart.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2014 at 4:15 pm
Jeff Moden (9/18/2014)
ScottPletcher (9/18/2014)
CPU is typically not the bottleneck nowadays, I/O is. More RAM = bigger buffers = less I/O. Thus, RAM will typically be much more vital to performance tha[n] CPU speed or bus speed. As the old saying goes, "All computers wait at the same speed".Ironically, I've just completed a T-SQL fix for a major CPU problem due to some really terrible code that was running 30,000 times during the workday. It was a major bottleneck and it was causing 90% CPU usage for 2 to 5 seconds on an otherwise quiet CPU. When normal daily work entered the picture, all 4 CPUs flattlined at 100% for many seconds just scant seconds apart.
In that case, though, I doubt a somewhat faster bus or processor would have helped much anyway.
In general, calcs that are genuinely extremely CPU-intensive, even when properly code, would be better done in CLR or outside of SQL Server completely.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 18, 2014 at 9:08 pm
ScottPletcher (9/18/2014)
Jeff Moden (9/18/2014)
ScottPletcher (9/18/2014)
CPU is typically not the bottleneck nowadays, I/O is. More RAM = bigger buffers = less I/O. Thus, RAM will typically be much more vital to performance tha[n] CPU speed or bus speed. As the old saying goes, "All computers wait at the same speed".Ironically, I've just completed a T-SQL fix for a major CPU problem due to some really terrible code that was running 30,000 times during the workday. It was a major bottleneck and it was causing 90% CPU usage for 2 to 5 seconds on an otherwise quiet CPU. When normal daily work entered the picture, all 4 CPUs flattlined at 100% for many seconds just scant seconds apart.
In that case, though, I doubt a somewhat faster bus or processor would have helped much anyway.
In general, calcs that are genuinely extremely CPU-intensive, even when properly code, would be better done in CLR or outside of SQL Server completely.
I agree. A somewhat faster bus or processor wouldn't have helped much at all here. Neither would have more RAM because the data was already cached and stayed cached all day. CLR wouldn't have helped here, either. It was some junk SQL code with a totally (2 level hierarchical lookup) non-SARGable WHERE clause. When I fixed that, the total daily run time (over a period of 12 hours) of over 30,000 runs dropped from 6 hours (1/8th of the total CPU time available in 12 hours) to 10 seconds (2,160X faster :-)). The Reads also dropped from more than 34 Trillion bytes per day (all logical reads but still a Memory I/O clog) down to just a couple of Billion (was about 2,777X fewer reads). It was some real low hanging fruit with huge ROI. Sometimes, I just get lucky.
As a bit of a sidebar, the box has 4 relatively slow CPUs (2GHz) and only 32GB of RAM, 28 of which have been allocated to SQL Server.
Heh... forget hardware, performance is in the code. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 19, 2014 at 5:14 am
The bus speed is important as is the type of RAM too. If the RAM bus speed exceds the CPU bus speed there's no benefit as it will clock down to match. More importantly, ensure you purchase matching sticks for the configuration you intend to use, otherwise the server may not boot.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply