• 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)