Cores Vs Sockets

David_SingletonDavid_Singleton Member Posts: 5,479
edited 2008-11-14 in SQL Performance
Has anyone done any specific comparisons between Cores and Sockets and SQL specifically related to NAV.

For example comparing one server with 2 Dual Core CPUs to a similarly configured machine with a Single Quad core, or even four single core processors.

basically in a NAV environment, does 1x4 = 2x2 = 4x1.

4 sockets in theory would have better ability to access ram, but of course if you have slow ram, then the ram (or bus) will be the bottle neck anyway.

Is there a significant advantage sharing the cache between 4 cores on once socket?

Opinions, theory and real world experiences would be appreciated.
David Singleton

Comments

  • bbrownbbrown Member Posts: 3,268
    Step 1: Find a main stream vendor that still markets a single-core processor server. :)

    I'm not sure you could draw generic decisions from limited comparisions. There are just too many variables. Things like different amounts of level 2 and 3 cache plus bus and memory speed differences would skew results. The only valid conclusions you could draw would be that Server A (with processor type 1) outperformed Server A (with processor type B). Everything else would need to be indentical to be able to say that the difference was the processor.
    There are no bugs - only undocumented features.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    bbrown wrote:
    Step 1: Find a main stream vendor that still markets a single-core processor server. :)

    I'm not sure you could draw generic decisions from limited comparisions. There are just too many variables. Things like different amounts of level 2 and 3 cache plus bus and memory speed differences would skew results. The only valid conclusions you could draw would be that Server A (with processor type 1) outperformed Server A (with processor type B). Everything else would need to be indentical to be able to say that the difference was the processor.

    Well specifically I was asking about 2 Dual cores vs one Quad core. I just added the single core as an after thought, since the same logic would apply if someone had done this comparison some years ago between single and dual cores.

    I may have not been clear, but when I said "similarly configured machines" I meant just that, basically same everything, EXCEPT 2 duals vs 1 quad, and of course same bus speed etc.

    Would you consider that a single quad core is a sufficient replacement for 2 quad cores.
    David Singleton
  • David_SingletonDavid_Singleton Member Posts: 5,479
    By the way the reason I started this thread, is I have had three cases recently where I have specified 4 CPUs in servers, and the hardware people came back saying that one Quad core IS four CPUs.
    David Singleton
  • DenSterDenSter Member Posts: 8,304
    It is by definition not the same. 4 sockets with 4 single core processors is simply not the same as one socket with one quad processor.

    What I've been told is that more sockets equals quicker performance, but there are probably setup tricks and hardware people that will disagree with that. I was told speficically that if you want 4 cores, that 2 dual cores will perform better than one quad core. What I don't know is whether there are generations of quad core processors that have solved any "problems" there used to be. It probably has to do with motherboard configuration, RAM type, and whole bunch of other things.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Hi,

    Interesting question...

    The trouble is that it is very unusual to have the same motherboard and two different types of processors to make comparisons.. And comparing between different motherboards doesn't make much sense.

    My personal view is that all depends in fact on motherboard not really on processor itself. SQL is not computing-intensive process. Its main task in fact is to transfer huge amount of data, so more data paths - better overall throughput. If you have 4 cores in one socket all four must compete for access to the RAM. Spreading number of cores across physical sockets actually may not improve the situation. If motherboard has single data channel which is shared between sockets then it makes little difference IMHO whether you have one quad-core or two double-cores processors. Shared cache, cache memory in general, is irrelevant when it comes to amounts of data transferred to and from memory by SQL due to it's size.

    The difference might be more significant when number of sockets exceeds 2. OS tasks always run on logical processor 0, and network cards are services by highest logical processors. When it comes to running and switching processes cache memory is very effective, as usually switched parts of the code are small compared to the cache size. Interrupt routines and device driver code is also quite small. So having 4 sockets and single core processors even with 2 MB cache per processor should work better than one quad core processor even equipped with 12 MB of cache. Especially if you exclude the first and the last one or two (depends on number of network cards in the sustem) processor in SQL process affinity mask. In such a scenario OS will run its processes from cache which will not be flushed by large data transfers, network cards drivers will have their data in latest processor cache.

    Situation looks different if you have machine with NUMA architecture. Each socket then has its own path to memory, and overall average transfer per socket is bigger, so it makes sense to use more sockets and smaller number cores per socket. In such a scenario even 2 sockets might perform significantly better than one (in opposite to non-NUMA architectures), because much bigger memory is considered 'local', like exclusively owned and accesses by all processor cores sharing one socket.

    That's my thoughts only. It is not related to NAV in any particular way, rather to SQL.

    NAV is only a 'consumer' of SQL services. What worse NAV is relatively simple application in terms of SQL queries thrown against SQL Server, so even more it depends on huge data transfer and not on any advanced computation. It is quite hard with such a simple queries (simple SELECTs, no JOINs, simple WHERE clauses) even use parallel processing. And without parallel processing data sharing among processors or cores practically not exist (in scope of processing single query). Sharing data in the processor cache among different queries also does not exists (or is very small) - due to large amount of the transferred data.

    Conclusion - more sockets is better but only if each socket has its own path to the data.

    Regards,
    Slawek

    PS. I'm not a motherboard hardware designer so I might be wrong...
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • kinekine Member Posts: 12,562
    There is one difference: licensing. If you want to license something like SQL per CPU, it is "per socket" now and not "per core". It means 2x2 config will be more expansive than 1x4. And may be it is why IT's like to have one Quadcore...

    But nothing about performance or any other difference what I know...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • ara3nara3n Member Posts: 9,255
    I just want to add that there is also a difference between Intel multi core implementation and AMD.

    AMD has its memory controler on the CPU, where as intel has it outside. AMD have better performance and I believe Intel is working on similar technology for future CPU's.

    There are a lot of website that do hardware performance comparison between hardware CPUs and I would definitely look at them.
    By the end I would say SQL licensing will have higher impact on which one I would choose.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • strykstryk Member Posts: 645
    My two cents ...

    If I don't err, you have ONE "Processor Queue" per physical CPU, per "Socket" if you like. If too many processes/threads are sent to a physical CPU this queue might enconter problems. Once the Processor Queue is full (> 2 requests queued) the whole system will slow down. By using multiple phyiscal CPU you have multiple queues, so such on "overflow" should not happen (or at least be delayed).

    The logical CPU - "Cores" - are sharing the internal Cache of the physical CPU (L2 cache etc.) for inernal operations. Once the L-caches are full, the CPU has to use the physical RAM (besides the usual RAM allocation for data etc.) which would also slow down the system. So multiple cores which are dynamically sharing a sufficient L-cache should not encounter such a problem.

    Hence, (IMHO) I would prefer two Dual Cores: Having TWO processor queues, and each physical CPU is split into TWO Cores sharing ONE L-cache ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • krikikriki Member, Moderator Posts: 9,094
    I read (and was also told) that it is faster to have multiple processors in stead of multiple cores. But that the extra speed is not so big.

    But considering that the most complex calculations SQL has to do for NAV is an order by without an index to work on, I would go for 1 quadcore and save a lot of money on the license (and the second cpu) that I can convert into more disks (and some more memory). For NAV DB's, most of the time the problems is disks and memory, and a lot less processing power (except if you have a single-core, single-cpu server [do they still exist?]).

    Just my 2 cents.

    Just for laughing a bit: Some weeks ago, I told a IT-department of a customer they had some problems with the cpu's of their new server (2 quadcores!). He looked horrified until I told him, the processors were annoying themselves to death! Even in peaks, the total cpu use percentage hardly arrived at 10%.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • SkeeveSkeeve Member Posts: 33
    Also keep in mind that if you have multiple cores or sockets, a good way to make use of it is to have multiple DB files for your production DB as well as TEMPDB.

    As a best practice, you should have 0.25 to 1 files per core.

    The files should be the same size though, otherwise, SQL will favor the file with the most space.
Sign In or Register to comment.